Modeling a ONE-TO-MANY Relationship Between Tables

Now it’s time to level up our skills and create one of the most common relationships in relational databases — the ONE-TO-MANY relationship.

Picture a small company. One employee can only work in one department, but a department can have dozens of employees. We’ve got two real-world objects: employees and departments. The relationship between them can be described as “one department can include many employees,” or more formally, “one to many” (ONE-TO-MANY).

Same deal, ONE-TO-MANY relationships are everywhere:

• one client can make many orders;
• one author can write a bunch of books;
• one teacher can teach several students.

In a relational database, a ONE-TO-MANY relationship is set up using a foreign key (FOREIGN KEY). One of the columns in the “many” table (MANY) points to the primary key from the “one” table (ONE).

How to Create a ONE-TO-MANY Relationship

Let’s break down the classic example: the relationship between customers and orders. One customer can make many orders, but each order is tied to just one customer. We’ll create two tables: customers and orders.

The customers Table

This is our “one” table. It’ll store info about customers.

CREATE TABLE customers (
    customer_id SERIAL PRIMARY KEY, -- Unique customer ID
    name TEXT NOT NULL              -- Customer name
);

The orders Table

This is the “many” table. It stores orders, and each order has a foreign key customer_id that points to customer_id in the customers table.

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,           -- Unique order ID
    order_date DATE NOT NULL,              -- Order date
    customer_id INT REFERENCES customers(customer_id) -- Foreign key
);

Practical Use

Inserting Data

Now let’s add some data to our tables to check if the relationship works.

Let’s add customers to the customers table:

INSERT INTO customers (name)
VALUES
    ('Ada Lovelace'),
    ('Grace Hopper'),
    ('Linus Torvalds');

Result:

Let’s add orders to the orders table:

INSERT INTO orders (order_date, customer_id)
VALUES
    ('2023-10-01', 1),  -- Ada’s order
    ('2023-10-02', 2),  -- Grace’s order
    ('2023-10-03', 1);  -- Another order from Ada

The orders table:

Heads up: when adding an order, you gotta specify an existing customer_id. If you try to use a non-existent ID, the database will throw an error. That’s data integrity protection for you.

Checking the Relationship

Now let’s see how our tables are connected. For example, let’s ask: what orders did Ada Lovelace make?

SELECT orders.order_id, orders.order_date, customers.name
FROM orders
JOIN customers ON orders.customer_id = customers.customer_id
WHERE customers.name = 'Ada Lovelace';

Result:

Here we used the JOIN command to combine two tables based on the foreign key. Super handy, super clean — and no duplicate data!

Why Bother?

The ONE-TO-MANY relationship is insanely common and useful in real life. Imagine an online store with thousands of customers and millions of orders. Instead of duplicating customer info in every order record, we keep unique customers in one table and orders in another. This cuts down on data size and keeps the database tidy.

Plus, being able to link data lets you make powerful analytics queries. For example, you can ask: “How many orders did each customer make?” or “Which customers placed orders last month?”

Tricky Bits and Gotchas

Here’s where newbies usually trip up:

Missing foreign key. If you forget to add a foreign key to the “many” table, the relationship will only exist in your head, not in the database. That means you risk ending up with a “broken” database where orders point to non-existent customers.

Trying to delete a record from the “one” table. For example, if you delete a customer from customers, their orders in orders will be left “hanging.” To avoid this, you can use ON DELETE CASCADE so that when you delete a customer, their orders get deleted automatically too.

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    order_date DATE NOT NULL,
    customer_id INT REFERENCES customers(customer_id) ON DELETE CASCADE
);

Now, if you delete a customer:

DELETE FROM customers WHERE customer_id = 1;

All their orders will be deleted too. The database will be as clean as a fresh cup of coffee.

Insert errors. If you try to insert an order with a non-existent customer_id, you’ll get an error like:

ERROR:  insert or update on table "orders" violates foreign key constraint