When we design a database, we need to link tables together. This is often done during the normalisation process of database design, but can also be done if you’re adding new tables to an existing database.
One of the most common ways tables can be related is called a “one to many” relationship.
This means that one record in one table is related to many records in another table.
Several examples of this are:
- Products can have categories (e.g. homeware, furniture, electrical)
- Employees are in departments (e.g. sales, accounting)
- Players can play for a team (e.g. seniors, juniors)
To be able to store this information, we need to link the tables together. This is done using a foreign key: we place the ID of one table as a column in another table, and refer to it as the foreign key, as it links to the primary key of the other table.
But how do we know which ID goes into which table?
I’ve got a question that I ask myself about the data to answer this. Let’s take a look.
Example
Let’s use the categories and products example from earlier.
A category is used to group products together for various reasons. It could be used to display on a website. It could be for reporting, so the business knows how much it is selling in each category.
The tables could look like this:
Category (category ID, category name)Product (product ID, product name, price)
The data could look like this:
Category
| Category ID | Category Name |
1 | Homeware |
2 | Furniture |
3 | Electrical |
Product
| Product ID | Product Name | Price |
1 | Chair | 80 |
2 | Table | 300 |
3 | Laptop | 800 |
4 | iPod | 250 |
5 | Small Pan | 15 |
At the moment, there is no link between the two tables. We can’t tell what category relates to what product.
We need to add an ID from one table into another table.
But how do we know which way?
Adding an ID to Create a Foreign Key
I ask myself a question about the data to work out which way a relationship goes.
Quote:
Does a table 1 have many tables 2s, or does a table 2 have many table 1s?
If the first is true, the table 1 ID goes into table 2 (into the many side).
If the second is true, the table 2 ID goes into table 1 (into the many side).
So, we can replace the table 1 and table 2 with the actual names of the tables:
Does a category have many products, or does a product have many categories?
- A
category has many products. - A
product does not have many categories.
So, in this example, the first part of then statement is true. This means the category ID goes into the product table.
Our tables will not look like this (with the foreign key in italics):
Category (category ID, category name)Product (product ID, category ID, product name, price)
The data would look like this:
Category
| Category ID | Category Name |
1 | Homeware |
2 | Furniture |
3 | Electrical |
Product
| Product ID | Category ID | Product Name | Price |
1 | | Chair | 80 |
2 | | Table | 300 |
3 | | Laptop | 800 |
4 | | iPod | 250 |
5 | | Small Pan | 15 |
We can see that the category ID column is now in the product table.
Populating the Data
If you have data in your table already, the next step would then be to populate this data.
You would need to write queries or apply your understanding of the business rules to know which values to enter. I’ll populate this table now.
Product
| Product ID | Category ID | Product Name | Price |
1 | 2 | Chair | 80 |
2 | 2 | Table | 300 |
3 | 3 | Laptop | 800 |
4 | 3 | iPod | 250 |
5 | 1 | Small Pan | 15 |
So, now we can see that the product table has a category ID, and we can use the category ID to refer to the category name.
This is how we can tell which way a one to many relationship should be recorded.
