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 some of our friends. (We won't actually create a real database for this yet, we're creating a pseudocode example to explore some basic database concepts. We'll walk through creating 'real' databases in the next lesson):

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

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

Schema Diagrams

To begin getting familiar with databases, we've been using text-based tables as examples. However, it's not common practice to include "sample data" when conveying database relationships, like we have in the examples above. Instead, the formal way to convey information about a set of tables and their relationships is with something called a schema.

Text Schemas

In a text-based schema, tables are depicted like this:

friends
-------
name
phone
birthday

As you can see, it's not actually that different from our examples above. We're just stating the name of the table (friends, in our case), then listing the names of the columns it will contain. In a database, these column names are also called database fields.

Visual Schemas

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.