Understand the notion of cardinalities
Let’s say that we have information about the person who is eating the apple. I will now introduce a person table containing people who might eat apples.
In the apple table, let’s add an eaten_by column, which is a foreign key pointing to the person table.
But what if the apple is eaten by two people? What do we do then?
We could add two columns in apple: eaten_by_1 and eaten_by_2. They would both be foreign keys pointing to person.
But what if the apple is eaten by more than two people? How many columns should we add? Should we add 1,000, just in case?
Unfortunately, this is faulty reasoning. A table with over 1,000 columns is awfully big, and would be impractical to manipulate. So, rather than adding a foreign key in apple pointing to person, why not add a foreign key in person pointing to apple?
It would look like this:
Here we see that one apple can be eaten by multiple people.
But reversing the direction of the foreign key only reverses the problem! Sure, in our new model, an apple can be eaten by more than one person, but now, each person can only eat one apple! A person has the right to eat as many apples as they want! No? :euh:
The solution to our problem is to create a third table, which is sometimes called an association table or a composition table.
Create an association table for many-to-many relationships
Association tables are used for many-to-many relationships between two objects. They consist of at least two foreign keys, each of which references one of the two objects.
Here is an example of an apple that can be eaten by more than one person, where each of these people can also eat multiple apples:
Go Further: Primary Keys in Association Tables
The primary key for an association table is made up of at least the two foreign keys. But it is sometimes necessary to add additional columns to the key.
For example, if we added the concept of date to our association table, we could say, "Harry ate apple 2 on April 21." But he could also have eaten the same apple on another day (if he didn’t eat it all on April 21, he could finish it on April 22). Therefore, the primary key would be[apple, date, person].
apple [PK] | date [PK] | person [PK] | statement |
1 | February 5th 2019 | 1 | "This apple is really yummy!" |
1 | March 8th 2019 | 2 | "This apple is huge! I will finish the rest tomorrow." |
1 | March 9th 2019 | 2 | "The rest of the apple is all brown today :(" |
2 | January 24th 2019 | 2 | "This is super healthy." |
Summary
Asking “How many apples can one person eat?” or “How many people can eat a single apple?" amounts to asking about the degree of relationship—or cardinality.
For a many-to-many relationship, we need to introduce an association table.
An association table contains at least two foreign keys pointing to the two objects it is associating.
