Lesson Thursday

So far this week, we have worked with database relationships that are what we call One-To-Many Database Relationships.

Here are some examples we worked with earlier this week:

courses
-------

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

students
--------

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

Or, from our current to do list project:

categories
-------

 id | name
 ---+------------------------
  1 | Yardwork
  2 | Cleaning
  3 | Pet Care

students
--------

 id | description | category_id
 ---+-------------------
  1 | mow the lawn   | 1
  2 | fix the fence   | 1
  3 | wash the dishes     | 2
  4 | brush Fuzzy the cat   | 2

And so on.

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 Jake 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
  5 | Chicago  | IL    | 3

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.

You may be thinking that we should place the 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:

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.

One-to-One Relationships

There's one final type of relationship: One-to-one. Here's an example:

people
-------

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


favorite_colors

 id | social_security_number | person_id
 ---+------------------------+----------
 1  | 123456789              | 1
 2  | 345678901              | 2
 3  | 678901345              | 3

One-to-one relationships aren't used that much, because the information could just as easily be combined together into one table:

people
-------

 id | name    | phone      | favorite_color | social_security_number
 ---+---------+------------+----------------+-----------------------
 1  | Chris   | 9165551212 | green          | 123456789             
 2  | Jessica | 3235551212 | blue           | 345678901             
 3  | Jake    | 4155551212 | pink           | 678901345             

You might use a one-to-one relationship for clarity - for example, you might have a Friend class and a FavoriteColor class, and want to store the data from each model in its own table.