KAHIBARO
Discord Login Register ☰
☰

9.8. One-to-Many Relationships

Table of Contents

Understanding One-to-Many Relationships

One-to-many relationships are at the core of relational database design. You will use them in almost every real application, from blogs and shops to authentication systems.

In this chapter we focus on what makes one-to-many relationships unique, how to recognize them, and how to implement them in SQL, especially in PostgreSQL.

What Is a One-to-Many Relationship?

A one-to-many relationship exists when:

You can think of it as:

We usually describe this as:

A (one) → B (many)

Conceptual Example

Imagine a simple blogging system:

Tables conceptually:

Relationship:

Every post belongs to a single user, but each user can have multiple posts.

Identifying One-to-Many in Real Projects

When you design a database, you first think about entities and their relationships.

You usually have a one-to-many relationship when:

Some common patterns:

DomainOne sideMany sideSentence
E-commerceusersordersOne user has many orders
E-commerceordersorder_itemsOne order has many order items
BloggingpostscommentsOne post has many comments
EducationcourseslessonsOne course has many lessons
CompanydepartmentsemployeesOne department has many employees
Social networkuserspostsOne user has many posts

If, instead, you can say “many X can belong to many Y,” that is a many-to-many relationship, which is handled differently and is covered in its own chapter.

How One-to-Many Is Implemented in SQL

Technically, a one-to-many relationship is implemented using a foreign key on the many side of the relationship.

Rule:
In a one-to-many relationship, the table on the many side contains a foreign key column that references the primary key of the table on the one side.

So for:

We create:

Basic structure:

sql 
CREATE TABLE users (
    id          SERIAL PRIMARY KEY,
    name        TEXT NOT NULL
);
CREATE TABLE posts (
    id          SERIAL PRIMARY KEY,
    user_id     INTEGER NOT NULL REFERENCES users(id),
    title       TEXT NOT NULL,
    content     TEXT NOT NULL
);

Here:

One-to-Many: Parent and Child Tables

In a one-to-many relationship we often say:

Typical structure:

RoleTableKey columnDescription
ParentusersidOne user row
Childpostsuser_id FKMany posts, each linked to one user

You can read it like this:

Creating One-to-Many Relationships in SQL

Let us walk through a few examples in SQL to make this concrete.

Example 1: Users and Orders

Business rule

Table design

sql 
CREATE TABLE users (
    id          SERIAL PRIMARY KEY,
    email       TEXT NOT NULL UNIQUE,
    full_name   TEXT NOT NULL
);
CREATE TABLE orders (
    id          SERIAL PRIMARY KEY,
    user_id     INTEGER NOT NULL REFERENCES users(id),
    total_cents INTEGER NOT NULL,
    created_at  TIMESTAMP NOT NULL DEFAULT NOW()
);

Explanation:

Inserting Related Data

sql 
-- Insert two users (parents)
INSERT INTO users (email, full_name)
VALUES
    ('alice@example.com', 'Alice'),
    ('bob@example.com', 'Bob');
-- Insert orders for Alice (id = 1)
INSERT INTO orders (user_id, total_cents)
VALUES
    (1, 1500),
    (1, 3200);
-- Insert one order for Bob (id = 2)
INSERT INTO orders (user_id, total_cents)
VALUES
    (2, 5000);

Here:

Query: Get All Orders for a User

sql 
SELECT *
FROM orders
WHERE user_id = 1;

This returns all orders for user with id = 1.

Query: Join Parent and Children

sql 
SELECT
    u.full_name,
    o.id       AS order_id,
    o.total_cents,
    o.created_at
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE u.id = 1;

This joins users (one side) with orders (many side) using the foreign key.

Cardinality and Optional Relationships

You can constrain one-to-many in different ways, for example:

This is controlled by whether the foreign key column can be NULL.

Mandatory Relationship (Most Common)

sql 
CREATE TABLE posts (
    id          SERIAL PRIMARY KEY,
    user_id     INTEGER NOT NULL REFERENCES users(id),
    title       TEXT NOT NULL
);

Here, every post must have a user_id. The database will reject a post without a user.

Optional Relationship

Sometimes a relationship is optional. For example, maybe an article can be written by the “system” without a specific author.

sql 
CREATE TABLE articles (
    id          SERIAL PRIMARY KEY,
    author_id   INTEGER REFERENCES users(id),  -- NULL allowed
    title       TEXT NOT NULL
);

Here, author_id can be NULL. This means:

One-to-Many and Cascading Behavior

When you have one-to-many relationships, you must decide what happens when:

This is handled by foreign key actions such as ON DELETE and ON UPDATE.

Common ON DELETE Strategies

StrategyBehavior when parent is deleted
ON DELETE RESTRICTPrevent deletion if child rows exist
ON DELETE NO ACTIONSimilar to RESTRICT in many databases
ON DELETE CASCADEAutomatically delete child rows
ON DELETE SET NULLSet the foreign key in children to NULL

Rule of thumb:
Use ON DELETE CASCADE only when it is safe to remove all related child rows automatically, for example, for temporary or dependent data. For important business data, prefer RESTRICT or handle deletion manually.

Example: Posts and Comments

sql 
CREATE TABLE posts (
    id          SERIAL PRIMARY KEY,
    title       TEXT NOT NULL
);
CREATE TABLE comments (
    id          SERIAL PRIMARY KEY,
    post_id     INTEGER NOT NULL REFERENCES posts(id) ON DELETE CASCADE,
    content     TEXT NOT NULL
);

Now:

sql 
DELETE FROM posts WHERE id = 10;

This will:

Example: Departments and Employees

sql 
CREATE TABLE departments (
    id          SERIAL PRIMARY KEY,
    name        TEXT NOT NULL
);
CREATE TABLE employees (
    id              SERIAL PRIMARY KEY,
    department_id   INTEGER NOT NULL REFERENCES departments(id),
    full_name       TEXT NOT NULL
);

Here, if you try to delete a department that still has employees, the database will block the operation (default behavior similar to RESTRICT).

You can then:

Practical Modeling Patterns

Let us look at some typical one-to-many designs you will meet as a backend developer.

Blog: Posts and Comments

Business rule

Tables

sql 
CREATE TABLE posts (
    id          SERIAL PRIMARY KEY,
    title       TEXT NOT NULL,
    body        TEXT NOT NULL
);
CREATE TABLE comments (
    id          SERIAL PRIMARY KEY,
    post_id     INTEGER NOT NULL REFERENCES posts(id) ON DELETE CASCADE,
    author_name TEXT NOT NULL,
    content     TEXT NOT NULL,
    created_at  TIMESTAMP NOT NULL DEFAULT NOW()
);

Queries

Get all comments for a post:

sql 
SELECT *
FROM comments
WHERE post_id = 5
ORDER BY created_at;

Get posts with their comment count:

sql 
SELECT
    p.id,
    p.title,
    COUNT(c.id) AS comment_count
FROM posts p
LEFT JOIN comments c ON c.post_id = p.id
GROUP BY p.id, p.title
ORDER BY comment_count DESC;

Here, we are using GROUP BY and COUNT on the many side, which is a common pattern with one-to-many relationships.

E-commerce: Orders and Order Items

Business rule

Tables

sql 
CREATE TABLE orders (
    id          SERIAL PRIMARY KEY,
    user_id     INTEGER NOT NULL REFERENCES users(id),
    created_at  TIMESTAMP NOT NULL DEFAULT NOW()
);
CREATE TABLE order_items (
    id          SERIAL PRIMARY KEY,
    order_id    INTEGER NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
    product_id  INTEGER NOT NULL REFERENCES products(id),
    quantity    INTEGER NOT NULL,
    unit_price_cents INTEGER NOT NULL
);

Query: Get an Order with Its Items

sql 
SELECT
    o.id           AS order_id,
    o.created_at,
    oi.product_id,
    oi.quantity,
    oi.unit_price_cents
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
WHERE o.id = 42;

Query: Calculate Order Total

sql 
SELECT
    o.id AS order_id,
    SUM(oi.quantity * oi.unit_price_cents) AS total_cents
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
WHERE o.id = 42
GROUP BY o.id;

Again, you see the pattern:

Avoiding Common Mistakes

Mistake 1: Storing Lists in One Column

Sometimes beginners try to store multiple values in one column, for example:

sql 
CREATE TABLE users (
    id          SERIAL PRIMARY KEY,
    email       TEXT NOT NULL,
    order_ids   TEXT      -- "1,2,3" or JSON
);

This is not correct for a relational database, because:

Use a proper one-to-many relationship instead:

sql 
CREATE TABLE orders (
    id      SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL REFERENCES users(id)
);

Mistake 2: Putting the Foreign Key on the Wrong Side

For a one-to-many relationship:

Wrong:

sql 
CREATE TABLE users (
    id          SERIAL PRIMARY KEY,
    last_order_id INTEGER REFERENCES orders(id)  -- this does not model one-to-many
);

Right:

sql 
CREATE TABLE orders (
    id          SERIAL PRIMARY KEY,
    user_id     INTEGER NOT NULL REFERENCES users(id)
);

You might still have last_order_id as a derived convenience column, but it is not what defines the relationship.

Mistake 3: Forgetting Indexes on Foreign Keys

Foreign key columns are used often in queries and joins. Without an index, those queries can become slow as data grows.

In most databases:

Example:

sql 
CREATE INDEX idx_orders_user_id ON orders(user_id);

Some databases create indexes automatically for foreign keys, but not all. PostgreSQL does not create an index for foreign keys automatically, so you should do it explicitly for performance.

One-to-Many in Application Code

Although implementation details depend on the programming language and ORM, the underlying concept is always:

Pseudo Python with an ORM-like syntax:

python 
class User(Base):
    id = Column(Integer, primary_key=True)
    email = Column(String, unique=True)
class Post(Base):
    id = Column(Integer, primary_key=True)
    user_id = Column(Integer, ForeignKey("users.id"))
    title = Column(String)

Conceptually:

Although ORMs make it more convenient, the database structure is still the same one-to-many pattern you learned here.

Summary

In this chapter you learned that:

You will use one-to-many relationships constantly when designing schemas and building backends, and they form the basis for more advanced relationship types you will see next.

Views: 8

Comments

Please login to add a comment.

Don't have an account? Register now!

© 2025 Kahibaro
Visitors: 2995 / 24h
Impressum / Imprint
Datenschutz / Data Policy