2.1. Table Relationships

As mentioned briefly in the last chapter, we want to keep the design of our database tables clean. Each table should deal with a very narrow set of data, and any specific piece of information should be stored in ONLY ONE PLACE. This makes it much simpler to perform updates, since changes can be applied once rather than over multiple tables.

For example, a school might keep track of their course offerings by creating a courses table. This would include information such as an ID number, title, duration, a short description, and number of credits. All of these items define a course—Chemistry, for example.

Every course has an assigned teacher as well as a student roster, and these may also seem like good things to add to the courses table. However, a list of student names does NOT define “Chemistry”. Since student placement in a course is temporary, the roster data needs to be kept in a separate location. Similarly, multiple teachers could run the same course. Since there could be two Chemistry classes that take place during 1st period, the names of specific instructors should be kept out of the courses table.

A separate teachers table would include information like ID, name, email, and hire date—everything the school needs to define an instructor. Data such as courses taught, planning period, or room number would NOT go into this table, since these items can change every year and are not specific to one person.

Similarly, a students table could contain ID, name, email, courses completed, parent phone numbers, and grades—anything that defines a particular child. Something like the name of their first period teacher would be stored elsewhere.

So, courses only holds data that directly defines each class the school offers. teachers only contains information that defines each instructor, and students stores student data and nothing else. This sounds obvious, but it is very easy to throw lots of data into a table and provide descriptive column names. While this may seem like a good idea at the time, it leads to repetition of data, and it makes maintaining that information more difficult.

Any time you add a new column to a table, ask yourself whether the data it represents defines that table. If you answer No, then store the data in a different place (e.g. lunch_period does not fit within students, so it will have to go in a different table, such as schedule).

2.1.1. Relating Data

Keeping data in separate, smaller chunks is more efficient than keeping a huge amount of data in one place. However, this often means we need to access information stored in different tables. To gather all the data we want, we will have to combine parts of these tables to get the whole picture.

For our school example, knowing which students are enrolled in which classes is useful. Similarly, we might want to display a list of students shared by a group of teachers.

Relational databases allow us to link tables together. Even though teacher names are not stored in courses, a connection can be made between that table and teachers.

Inside courses we can add an instructor column. However, this column will NOT hold any names. Instead, it will store references that point to the teachers table. These references identify the information in teachers that is connected to a particular entry in courses.

2.1.2. One-To-Many Relationships

A common structure for database tables is the one-to-many relationship. In this form, each entry in one table relates to many rows in a different table. For example, a single row in courses represents one class offered by a school. That single course can be taught by multiple teachers, and lots of different students will be enrolled in it. Thus, one entry in the courses table relates to many entries in teachers and students.

Note

For another example of the one-to-many concept, feel free to read this article.

2.1.3. Table Keys

To connect the courses table to teachers in a one-to-many relationship, the following conditions must be met:

1. Each table must include a primary key column. A primary key is a unique, numerical identifier given to an entry.
2. The teachers table must include a foreign key column. Foreign keys are integers that tie directly to the entries in a different table. In our school example, the foreign key for a row in teachers matches one primary key in courses.

Note that different teacher entries could have the same value for the foreign key. This sets up the one-to-many link between a single row in courses and multiple rows in teachers.

2.1.3.1. Adding a Primary Key

In MySQL, the syntax for creating a primary key column is:

column_name INTEGER PRIMARY KEY AUTO_INCREMENT

column_name usually involves the term “id”, and AUTO_INCREMENT ensures that each entry gets assigned a different integer value.

For our school example, the code to create courses could look like:

CREATE TABLE courses (
   course_id INTEGER PRIMARY KEY AUTO_INCREMENT,
   course_title VARCHAR(40),
   course_minutes INTEGER,
   course_description TEXT,
   credits INTEGER
);

2.1.3.2. Adding a Foreign Key

The general syntax for creating a foreign key column is:

column_name INTEGER,
FOREIGN KEY (column_name) REFERENCES other_table(primary_key_column)

Line 2 establishes the relationship between two tables. One way to interpret the line is, The value for ‘column_name’ in this table relates to the entry in ‘other_table’ that has a matching primary key.

For our school example, the code to create teachers could look like:

CREATE TABLE teachers (
   teacher_id INTEGER PRIMARY KEY AUTO_INCREMENT,
   first_name VARCHAR(40),
   last_name VARCHAR(40),
   email VARCHAR(80),
   hire_date DATE,
   course_id INTEGER,
   FOREIGN KEY (course_id) REFERENCES courses(course_id)
);

Note that in line 7, the name given to the foreign key column in teachers matches the name of the primary key column in courses. Following this convention helps you keep the relationships between your tables clear.

Note

In the last studio, you established a relationship between a directors table and a movies table using a foreign key.

2.1.3.3. Keys Wrap-Up

1. Define a primary key column for each table in the database.
2. Define a foreign key column for any table that fulfills the many role of a one-to-many relationship.
3. The foreign key of the many relates to the primary key of the one.

Tip

Even if you do not think a table needs one, define a primary key column anyway. Your database needs to grow and adapt to change. Adding a primary key column to a table at the beginning helps with that.

2.1.4. Other Relationships

Besides the common one-to-many structure, there are two other ways to relate tables to each other. Read the following articles (or do a quick Google search) to explore these options:

1. One-to-one
2. Many-to-many

You will need this information to answer the last few concept check questions.

2.1.5. Check Your Understanding

Question

Which if the following is the BEST table to store the period and room number for an Algebra I course?

1. teachers
2. students
3. courses
4. schedule

Question

Which type of relationship exists between a dresser table and a drawer table?

1. One-to-one
2. One-to-many
3. Many-to-many

Question

Which type of relationship would exist between the teachers and students tables?

1. One-to-one
2. One-to-many
3. Many-to-many

Question

For the closet, shelf, and box tables, what are the relationships? (Don’t overthink this).

1. One-to-many and one-to-many
2. One-too-many questions
3. Many-too-many-too-many questions
4. Um, wait, but… NOOOOOOO! I overthought it!