When working with relational databases, we tend to create a lot of relationships between tables. But not all relationships are created equal – there are different types of relationships. The one-to-many relationship is probably the most common type of relationship in relational database design.

A one-to-many relationship is a type of association between two tables where a record in one table (the “one” side) can be related to multiple records in another table (the “many” side). For example, in an e-commerce database, a single customer can place many orders. In this scenario, the Customers table would have a one-to-many relationship with the Orders table.

Example

Consider a simple database schema for an e-commerce application:

• Customers: Stores information about customers.
• Orders: Stores information about orders placed by customers.

ER Diagram

Here’s an entity relationship (ER) diagram, which presents a visual representation of the schema:

Key points:

• The Customers table’s primary key is the CustomerId column. A primary key column cannot contain duplicate values. Therefore, each customer can only have one entry in that table. This is the “one” side of the relationship.
• The Orders table also has a CustomerId column, and this is a foreign key that references the CustomerId column in the Customers table. A foreign key can (and usually does) contain duplicates (unless it’s a one-to-one relationship). This is the “many” side of the relationship.

The Orders table also has its own primary key, but that doesn’t affect the one-to-many relationship. This primary key ensures that each order can be identified in its own right, without being confused with other orders. In other words, we can have multiple rows with the same CustomerId but with a different OrderId.

SQL Code to Create the Schema

Here’s how we can create the above schema in SQL:

-- Create the Customer table
CREATE TABLE Customers (
    CustomerId INT IDENTITY PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    Email VARCHAR(100)
);

-- Create the Order table
CREATE TABLE Orders (
    OrderId INT IDENTITY PRIMARY KEY,
    OrderDate DATE,
    Amount DECIMAL(10, 2),
    CustomerId INT,
    FOREIGN KEY (CustomerId) REFERENCES Customers(CustomerId)
);

We can see that the Orders table has a FOREIGN KEY constraint defined on the last line. The first CustomerId is the one in the Orders table. The second one is the one in the Customers table.

So it goes FOREIGN KEY (fk_column) REFERENCES pk_table(pk_column). Or put more simply, it says something like “this column here on this table references that column there on the other table”.

When to Use a One-to-Many Relationship?

We use a one-to-many relationship when we have a scenario where a single entity needs to relate to multiple other entities.

Here are some scenarios that warrant a one-to-many relationship:

Scenario	“One” Side	“Many” Side	Tables Involved	Description
Customers and Orders	Customer	Order	Customer, Order	One customer can place many orders.
Categories and Products	Category	Product	Category, Product	One category can contain many products.
Blog Posts and Comments	BlogPost	Comment	BlogPost, Comment	One blog post can have many comments.
Cities and Addresses	City	Address	City, Address	One city can have many addresses.

Sometimes the type of relationship will depend on the situation. For example, the following scenarios could warrant a one-to-many relationship, but they may also require a many-to-many relationship, depending on the specific use case.

Here’s what they would look like with a one-to-many relationship:

Scenario	“One” Side	“Many” Side	Tables Involved	Description
Authors and Books	Author	Book	Author, Book	One author can write many books.
Departments and Employees	Department	Employee	Department, Employee	One department can have many employees.
Departments and Projects	Department	Project	Department, Project	One department can oversee many projects.

As alluded to, some of the scenarios listed here could be better suited to a many-to-many relationship depending on the circumstance. For example, in many cases a book can be co-authored by several authors, and authors can contribute to multiple books.

Here’s a look at those scenarios and why they might be better modeled with a many-to-many relationship:

Scenario	Many-to-Many Suitability
Authors and Books	Yes: A book can have multiple authors, and an author can write multiple books.
Departments and Employees	Maybe: An employee might belong to multiple departments, and a department might manage employees temporarily.
Departments and Projects	Yes: A project can be overseen by multiple departments, and a department can manage multiple projects.

So this illustrates why we need to understand the full use case before we jump in and start designing our database relationships.

Summary

One-to-many relationships are a fundamental concept in SQL, allowing for efficient and organized data management where a single record in one table relates to multiple records in another. Understanding and implementing these relationships correctly ensures referential integrity and supports scalable database design.