Click here to Skip to main content
15,867,568 members
Articles / Programming Languages / SQL
Tip/Trick

How to Tell Which Way a One-to-Many Relationship Should Go

Rate me:
Please Sign up or sign in to vote.
3.43/5 (8 votes)
22 Feb 2018CPOL3 min read 10.9K   3   2
This article explains how to tell if a relationship in a database should be many-to-one or one-to-many.

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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



Comments and Discussions

 
QuestionHmmm Pin
Jon McKee22-Feb-18 16:14
professionalJon McKee22-Feb-18 16:14 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.