Lesson Monday

Last week, we learned about the different types of database relationships. So far, we've gotten plenty of practice creating and interacting with a one-to-many database relationship setup. Let's also make sure we feel comfortable working with a many-to-many database relationship.

As you know, we'll need this to link our Restaurants to Foodtypes. (Or To Do List items to more than one Category, or Movies to more than one Genre - you get the idea. One-to-many relationships are rarely that close to life. In many cases, especially with complex apps, objects of different types will carry an association with different object types. Learning to model complex data is a real job, and we'll begin with a straightforward example.)

Let's briefly review one-to-many database relationships.

One-to-Many Review

friends
-------

 id | name    | phone
 ---+---------+-----------
 1  | Chris   | 9165551212
 2  | Jessica | 3235551212
 3  | Jake    | 4155551212

Above is a simple database table.

One-to-Many Database Relationship

The great thing about databases is their ability to manage relationships between different pieces of information. That is, we can make relationships between our tables. Let's say we want to add home and cell phones for our friends we listed in the table above. We can just create another table containing the phone numbers, then link this table to our existing table of friends:

friends
-------

 id | name
 ---+--------
  1 | Chris
  2 | Jessica
  3 | Jake

phones
------

 id | number     | type | friend_id
 ---+------------+------+----------
  1 | 9165551212 | cell | 1
  2 | 3235551212 | cell | 2
  3 | 4155551212 | cell | 3
  4 | 9165552020 | work | 1
  5 | 3235552020 | work | 2
  6 | 4155552020 | work | 3

The friend_id tells the database that the number in the column references the id number in the friends table. Because one friend can have many phones, we say that there is a one-to-many relationship between friends and phones.

If this part still feels confusing, take the time to review database relationships before proceeding.

Many-to-Many Database Relationships

What if we want to track what cities my friends have visited? We could do this:

friends
-------

 id | name    | phone
 ---+---------+-----------
 1  | Chris   | 9165551212
 2  | Jessica | 3235551212
 3  | Jake    | 4155551212


cities
------

 id | city     | state | friend_id
 ---+----------+-------+----------
  1 | Chicago  | IL    | 1
  2 | New York | NY    | 1
  3 | Oakland  | CA    | 2
  4 | Portland | OR    | 3

So far so good. But what if Jessica and Chris go to Chicago? Then our cities table looks like this:

cities
------

 id | city     | state | friend_id
 ---+----------+-------+----------
  1 | Chicago  | IL    | 1 
  2 | New York | NY    | 1 
  3 | Oakland  | CA    | 2 
  4 | Portland | OR    | 3 
  5 | Chicago  | IL    | 2  

But now, information about Chicago has to be listed multiple times. This is no good. If we later wanted to add more information to Chicago's entry in our database, we would have to update it in multiple places. This is inefficient and, if something went wrong, could lead to data inconsistency. Including redundant data is bad practice in a database, because it takes up more space than necessary. And means more records to edit and maintain. It's not easily scalable.

You may think we should place city_id in the friends table. But doing that would result in a similar problem: Instead of having duplicate city entries, we'd have duplicate friend entries.

We no longer have a one-to-many relationship, in which one person can visit many cities; it should be a many-to-many relationship, because one friend is capable of visiting many cities, and one city is capable of having many friends visit it. Our database should represent this.

The correct way to represent a many-to-many relationship in our database is with a join table, like this:

friends
-------

 id | name    | phone
 ---+---------+-----------
 1  | Chris   | 9165551212
 2  | Jessica | 3235551212
 3  | Jake    | 4155551212


cities
------

 id | city     | state
 ---+----------+------
 1  | Chicago  | IL
 2  | New York | NY
 3  | Oakland  | CA
 4  | Portland | OR


friends_cities
--------------

 id | city_id | friend_id
 ---+---------+----------
 1  | 1       | 1
 2  | 1       | 2
 3  | 2       | 3
 4  | 2       | 1

As you can see, our new friends_cities table contains IDs from both tables that it joins. This allows us to have any combination of IDs from both tables without ever duplicating our entries in either the friends or cities tables!

When naming join tables, it's common practice to use the names of the two tables it is joining together, separated by an underscore. However, if there is a name that more accurately describes the relationship the join table is managing, you can use that too. For instance, we could easily call the friends_cities table visits instead, since it's contents describe which friends have visited which cities.

Many-to-many relationships can be tricky to wrap your mind around. Especially when you're just starting out. Let's consider another example before we move on.

Let's say we needed to keep track of various classes students are enrolled in.

If a student were only allowed to take one course at a time, we could track this information in a one-to-many relationship, because one course can be taken by many different students; but a student can only take one course at a time.

However, let's assume students take multiple courses at once, like many high school or college schedules. This means a course can be taken by many students, but a student may also take many different courses. This requires a many-to-many database relationship. It would look something like this:

courses
-------

 id | course
 ---+------------------------
  1 | Epicodus
  2 | How to cook vegan
  3 | Intro to rocket science

students
--------

 id | student
 ---+--------
  1 | libby  
  2 | tiny   
  3 | bub    
  4 | lizzy  
  5 | hank   

enrollments
-----------

 id | student_id | course_id
 ---------------------------
  1 | 1          | 1
  2 | 2          | 1
  3 | 3          | 2
  4 | 4          | 1
  5 | 5          | 3
  6 | 2          | 3
  7 | 4          | 3

We have a courses table containing entries for each course. Each entry contains a course name, and an ID number. We also have a students table containing entries for each student. Each entry contains a student name, and their ID number.

Then, in order to track the relationship between students and courses, we have an enrollments join table. This join table contains an entry for each relationship between a course and student. For instance, we can see the student_id value of 4 is listed multiple times in our enrollments join table. Once with course 1 and once with course 3. If we cross-reference the students table, we can see that the student with the ID of 4 is lizzy. Then, if we cross-reference thecoursestable we can see that course1is Epicodus, and course3` is "Intro to Rocket Science". So, Lizzy is taking both Epicodus and Intro to Rocket science courses.