One-To-Many SQL Relationship

One-To-Many Relationship Basics

A one-to-many (1:n) relationship is a very common SQL relationship. The basic idea is that every row you store in the 1-side table is related to an undefined number of rows in the n-side table. This can be any number between 0 and n rows.

A very good example is a SQL database that stores orders in one table (1-side) and order positions in another (n-side). Take a look at the following tables:

One-To-Many SQL relationship

One order can have zero, one or many order positions. Since the number is not limited in theory (from the concept point of view), you simply replace the undefined number with the variable “n”. That why this relationship is called a one-to-many or 1:n relationship.

 

Implementation

Implementing a one-to-many relationship is quite easy. The relationship is stored using a special column in the n-side table. This column stores the primary key of the 1-side row. If there is more than one primary key in the 1-side table, you have to add more columns.

In the example above, you would add the primary column of the order table (order_id) to the order_position table. To ensure the referential integrity of your data you can use foreign keys. Read more about foreign keys in detail here.

 

Advantages

The main advantage of one-to-many relationships is that you can normalize your tables and don’t have to store data redundantly. Of course, you could also use NULL columns and only store data in the first row of each order, but this is what I would consider as bad behavior. The ninja way is the one-to-many relationship in this case.