• 20 hours
  • Medium

Free online content available in this course.

course.header.alt.is_video

course.header.alt.is_certifying

Got it!

Last updated on 4/19/21

Create an association table

Log in or subscribe for free to enjoy all this course has to offer!

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.

Foreign key pointing to the person table
Foreign key pointing to the person relation

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:

Foreign key pointing to the apple table
Foreign key pointing to the apple relation

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:

Association Table
Association Table

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].

Association table with a 3-column PK

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.

Example of certificate of achievement
Example of certificate of achievement