Relationships Between Tables
Episode #6 of the course Relational database design by Ben Brumm
Welcome to lesson six.
In the last lesson, we looked at first normal form, what it is, and how to transform our database into “first normal form.”
In this lesson, we’ll discuss relationships between tables.
What Is a Relationship?
A relationship is how two tables are related to each other.
So far, we have designed different tables to store information about different entities. Our tables are student, subject, teacher, and university.
Your tables may be order, customer, employee, department, payment, product, product category, or anything else. But a database is supposed to capture data about these entities and how they relate to each other. To do this, we need relationships.
There are several types of relationships.
One-to-Many
A one-to-many relationship means that a record in one table relates to many records in another table.
This is the most common kind of relationship in database design.
If we use our example of a student enrollment system, a one-to-many relationship exists between teachers and subjects.
A subject has one teacher. But a teacher has many subjects. We store this by putting the primary key (usually an ID number) of one table into another table.
One-to-One
Another type of relationship is the one-to-one relationship. It means that a record in one table is related to only one record in another table.
This isn’t as common as a one-to-many relationship, but it’s still relevant.
For example, if we were creating a car database, we could say that a car has one steering wheel, and a steering wheel has only one car.
Many-to-Many
Another type of relationship is where many records in one table can relate to many records in another table.
Using our example, a student can have many subjects, and a subject can have many students. This is what many-to-many means.
How can this be stored effectively? We can’t put the ID of one table in another table, as that restricts us to one record to many records. We need to create a “joining table.” This table will contain all combinations of each table.
In our example, it will contain the list of all student, and all of the subjects that they have enrolled in.
It will store the ID numbers. The names of students will still be in the student table. The names of subjects will still be in the subject table.
This is a common and effective way of joining two tables together in this situation.
Self Join
There’s also a relationship where a table is related to itself. A common example used here is an employee-manager relationship.
Consider these rules:
• An employee has a manager
• A manager is also an employee
• A manager can have a manager
This forms some kind of hierarchy. Instead of creating different tables for each level of manager, we create a single table called “employee” and allow it to link to itself, or perform a self join.
This allows for a single place to store employee and manager data and is flexible for as many levels of managers and employees as we need.
So, those are the different types of relationships. They are relevant because we’ll start using them in the next lesson.
In the next lesson, we’ll cover the next step of normalization: second normal form.
Until next time,
Ben
Recommended book
Share with friends