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
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
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.
courses only holds data that directly defines each class the school
teachers only contains information that defines each instructor,
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
it will have to go in a different table, such as
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
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
that is connected to a particular entry in
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
table relates to many entries in
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:
- Each table must include a primary key column. A primary key is a unique, numerical identifier given to an entry.
teacherstable 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
teachersmatches one primary key in
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
multiple rows in
184.108.40.206. 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
ensures that each entry gets assigned a different integer value.
For our school example, the code to create
courses could look like:
1 2 3 4 5 6 7
CREATE TABLE courses ( course_id INTEGER PRIMARY KEY AUTO_INCREMENT, course_title VARCHAR(40), course_minutes INTEGER, course_description TEXT, credits INTEGER );
220.127.116.11. 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:
1 2 3 4 5 6 7 8 9
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
matches the name of the primary key column in
courses. Following this
convention helps you keep the relationships between your tables clear.
In the last studio, you established a relationship
directors table and a
movies table using a foreign key.
18.104.22.168. Keys Wrap-Up¶
- Define a primary key column for each table in the database.
- Define a foreign key column for any table that fulfills the many role of a one-to-many relationship.
- The foreign key of the many relates to the primary key of the one.
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:
You will need this information to answer the last few concept check questions.
2.1.5. Check Your Understanding¶
Which if the following is the BEST table to store the period and room number for an Algebra I course?
Which type of relationship exists between a
dresser table and a
Which type of relationship would exist between the
box tables, what are the
relationships? (Don’t overthink this).
- One-to-many and one-to-many
- One-too-many questions
- Many-too-many-too-many questions
- Um, wait, but… NOOOOOOO! I overthought it!