Lesson Weekend

You can think of a database like a bunch of linked spreadsheets. Each spreadsheet is called a table. Let's make a table with phone numbers for my friends:

friends
-------

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

There you have it: a database table. That wasn't too hard. Here's another one:

things_in_portland
------------------

 id | category     | in_portland
 ---+--------------+------------
 1  | sunshine     | false
 2  | moonshine    | true
 3  | shiny things | true

One-to-Many Relationships

The great thing about databases is the relationships they can make between tables. Let's say I want to add home and cell phones for my friends. I can just create another table and link them:

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

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

Here's another one-to-many relationship, where a course can have many students:

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

Many-to-many 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
  6 | Chicago  | IL    | 3

Now, information about Chicago (like the state) has to be listed multiple times. This is no good - if we have to update Chicago, we have to update it in multiple places, which is inefficient and, if something went wrong, could lead to data inconsistency.

You might be thinking that we should put the city_id in the friends table, but we'll end up with the same problem: instead of having duplicate cities, we'd have duplicate friends.

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, in which one person can visit many cities, and one city can have many people visit it.

The right way to handle a many-to-many relationships like this is 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

friends_cities holds IDs from both tables that it joins, so that you can have any combination of IDs from both tables without duplicating data.

If you can't think of a better name for a join table, you can just use the names of the two tables it joins separated by an underscore. But it's nice to think of something more descriptive. I might call this join table visits.

Another example would be if courses have many students, and students can enroll in many courses:

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

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 |colors            | person_id
 ---+------------------+----------
 1  | green            | 1
 2  | blue             | 2
 3  | pink             | 3

One-to-one relationships aren't used much, because the information can just be combined 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.

Schema diagrams

To get you familiar with how a database works, I've been showing you tables with example data. But when you want to describe all the tables and relations in a database, also called the database schema, it's not convenient to include sample data. Instead, tables are depicted like this:

friends
-------
name
phone
birthday

It's basically just turning the column names ninety degrees and listing them out under the table name. The column names are also called database fields.

When designing database schemas, it's often helpful to draw out the relationships among tables, like this:

A simple database schema diagram.

It's especially helpful if you're trying to figure out a more complex schema:

A complex database schema diagram.

You can make schema diagrams like this with this online SQL Designer. The SQL Designer has a lot of options, but ignore most of them - just add tables and add fields to tables, and don't worry about data types or other options. To connect tables for your one-to-many and many-to-many relationships, click on the ID of the table, click connection foreign key (on the right), and then click the corresponding field that references that ID.

There are three types of relationships:

  • One-to-many (e.g., a course has many students, and a student belongs to one course - like Epicodus)
  • Many-to-many (e.g., a course has many students, and a student has many courses - like college)
  • One-to-one (a tutor who only serves a single client)

For a one-to-many relationship, store the ID of the "one" in a column of the "many".

courses
-------

 id | name
 ---+------------------------
  1 | Epicodus

students
--------

 id | name | course_id
 ---+-------------------
  1 | libby   | 1
  2 | tiny    | 1

For a many-to-many relationship, store the IDs in a join table. Name the join table something descriptive; if nothing comes to mind, use the names of the tables it joins separated by an underscore (e.g., cities_friends).

friends
-------

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


cities
------

 id | city     | state
 ---+----------+------
 1  | Chicago  | IL
 2  | New York | NY


visits
--------------

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

A database schema diagram:

A complex database schema diagram.

Make schema diagrams with SQL Designer.