5  Many-to-many with attributes

Often when we are dealing with many to many relationships we want to go further than simple associations. For example if we were modeling people and their hobbies, we might want to know when the person started having their hobby. If we were modeling sporting events we might want to store results (who won, came second, or their race times) as well as who competed.

We can use an example of Class and Student, we might want to store an additional attribute of the connection between a single student and a single class, such as semester in which the student enrolled in that class. In tables we could do this:

But this is not recommended. This takes a Simple Association table classes_students and adds a column to it semester. Now we can represent that Sarah took Poisons twice, once in Fall 2008 and once in Spring 2008. So far so good. However our primary key for this table is the combination of class_id and student_id. Can you see that we have repeated that combination? The last two rows would have the same primary key (class_id: 3 and student_id: 3). We can’t do that (the database server will reject the second row).

To make our simple association table work with extra attributes we would have to add the extra attribute to our composite primary key as well, making the combination of all three columns the unique thing about the row.

There are two problems with this. First, the key becomes unwieldy as it gets longer. Second, you may have an extra attribute that is not unique. For example, imagine a class code like “Independent Study” that could be taken twice in the same semester by the same student. Now even the three columns (class_id, student_id, and semester) in combination aren’t sufficient to make the row unique. In this example we could try to find something further that made the classes unique (perhaps they have a different instructor? Perhaps not.). However then we have to manage keys of length 4. Moreover it may be that we simply don’t have data to distinguish between the two relationships, no matter how many attributes we try to add.

As far as the database is concerned that is fine and you will definitely see databases that are set up like this (and you very well might see composite keys of length 3 or even 4). The real world of databases can be a messy place.

5.1 Promote the association table to be an Entity

So when we have additional attributes about a relationship, rather than extending our composite primary key, we handle this by turning the relationship into its own Entity, with its own entity name and the extra data as an attribute of that entity.

As we turn our modeling into tables, this means that we have a new id column. To continue the Student-Class example, we would add the id column to our growing middle table and make it, rather than the combination of class_id, student_id, and semester the primary key. We can call this Entity “Enrollment”.

Now we can say:

“A single student could make many enrollments”

“A single enrollment is about a single student”

“A single student could have many enrollments, each about a single class”

It is possible to include attributes in the conceptual ER diagram; here we include semester as a key attribute of the enrollment.

When we translate to the physical ER diagram, just as before the foreign keys are in the ‘middle’ table, at the crowsfoot end of the lines.

5.2 Naming the middle entity

Sometimes the middle entity doesn’t have a natural name, such as Enrollment. In that case using “Record” is useful. For example People have many Addresses, such as “billing” and “mailing”. We can record that with a middle entity “AddressRecord” with an attribute of “type”.

5.3 Modeling Exercise (in-class)

Form groups of four and you will be assigned one of the relationships below. Each should be modeled with three entities with the center entity being the relationship between the outside ones. Hint: they are all many-to-many with attributes :)

You should:

  1. Brainstorm some concrete instances. Feel free to start with sentences or jotted notes, but you must have some real world examples to begin your modeling.
  2. Begin to develop your conceptual ER diagram. Check use are using CamelCase and singular.
  3. Develop your physical ER diagram. Check snake_case, tables plural, columns singular.
  4. Sketch tables and insert your concrete example data from step 1.

Examples (all will have three boxes and two lines.)

  1. Citizenship (Naturalization and Birth)
    e.g., James has two citizenships, one for the United Kingdom which he obtained by birth, and one for Australia where he was naturalized in 1997.

  2. Recipes and Ingredients
    e.g., My recipe for omelette includes 3 eggs and 15ml of milk. milk is also an ingredient in a hot chocolate, which uses 3 teaspoons of sugar and one cup of milk.

  3. Degrees awarded by universities to students.
    e.g., Shuyen graduated from Peking University with a BA in finance in 2000. She graduated with her MSIS from University of Texas at Austin in 2012.

  4. Magazine Subscriptions
    e.g., Roundrock library subscribed to “The Economist” magazine from 1972 through 2014. Kyle library subscribed to “The Economist” in 1985 and still has a subscription; they also subscribe to “The Atlantic Monthly” which they began in 2003.

  5. Sporting Event, Competitors, Results.
    Use the results in the screenshot as an example. You can ignore country or include it as an attribute of the competitor. Hint: the Event is “800m Freestyle Women”. See more results here: Rio 2016 Swimming Results

Don’t forget to brainstorm some additional concrete instances, write them down, and include them as sample data in your table sketches!