Modeling Relationships Between Entities

Once entities are identified, the next question is how they connect to each other. A relationship captures a real-world association — a customer places orders, an employee manages a department, a product belongs to a category — and in a relational database, every relationship must ultimately be expressed through foreign keys or, for many-to-many associations, through an entire join table.

Relationships are not optional decoration on top of entities; they are where most of the actual design difficulty lives. Getting entities right is usually straightforward once you know the domain, but deciding how those entities reference each other — including what happens when a referenced row is deleted, and whether a relationship is optional or mandatory — is where schemas succeed or quietly accumulate bad data.

Why it matters

A foreign key says how two tables are connected, and its delete rule says what happens to the children when the parent goes. Getting that rule wrong either blocks legitimate deletions or silently destroys data that should have been kept. It is a decision to make deliberately for every relationship.

From Sentence to Foreign Key

A relationship in a requirement sentence usually has a direction and an owner. "Each order belongs to one customer" tells you the foreign key lives on the orders table (orders.customer_id references customers.id) — the "many" side of a one-to-many relationship always holds the foreign key pointing to the "one" side. Getting the direction backwards (putting a customer_id-like column on the wrong table) is a common early mistake that becomes obvious only once you try to model a customer with more than one order.

Mandatory vs. Optional Relationships

A relationship is mandatory if a row cannot exist without it — an order_items row makes no sense without an order, so order_id should be NOT NULL. A relationship is optional if the association can be absent — a customer may or may not have an assigned account manager, so account_manager_id can allow NULL. Deciding this explicitly, column by column, prevents both false NOT NULL constraints that block legitimate data entry and false nullable columns that let genuinely required links go missing.

Referential Actions: What Happens on Delete or Update

Every foreign key needs an explicit policy for what happens when the referenced row is deleted or its key changes: ON DELETE CASCADE (delete dependents automatically), ON DELETE RESTRICT (block the delete if dependents exist), or ON DELETE SET NULL (clear the reference but keep the dependent row). Choosing this deliberately — rather than accepting a database default you haven't checked — is part of relationship design, not an afterthought: deleting a customer should probably not silently cascade-delete years of order history.

• CASCADE — appropriate for true ownership, e.g. deleting an order should delete its order_items.
• RESTRICT / NO ACTION — appropriate when the dependent data must be preserved or archived first, e.g. blocking deletion of a product that appears in past orders.
• SET NULL — appropriate for optional associations, e.g. clearing assigned_employee_id if that employee record is removed.

Example

CREATE TABLE departments (
    id    BIGINT PRIMARY KEY,
    name  VARCHAR(100) NOT NULL UNIQUE
);

CREATE TABLE employees (
    id             BIGINT PRIMARY KEY,
    full_name      VARCHAR(150) NOT NULL,
    department_id  BIGINT NULL REFERENCES departments(id) ON DELETE SET NULL,
    manager_id     BIGINT NULL REFERENCES employees(id) ON DELETE SET NULL
);

employees.department_id is a mandatory-looking but actually optional
relationship: an employee can be unassigned to any department. If a
department is deleted, ON DELETE SET NULL clears department_id on its
employees rather than deleting the employee rows or blocking the delete.
manager_id is a self-referencing relationship (an employee can manage
other employees) with the same "orphan gracefully" policy.

Exercise

Order items cannot exist without their order. Create order_items so that deleting an order automatically deletes its items. The data is inserted and order 1 is then deleted. Count the items that remain as remaining.

Expected output: remaining then 0

CREATE TABLE orders (id INTEGER PRIMARY KEY, customer TEXT NOT NULL);

-- TODO: order_items (id, order_id, product) whose rows are deleted with their order

INSERT INTO orders (id, customer) VALUES (1, 'Asha');
INSERT INTO order_items (id, order_id, product) VALUES (1, 1, 'pen'), (2, 1, 'book');

DELETE FROM orders WHERE id = 1;

SELECT COUNT(*) AS remaining FROM order_items;

CREATE TABLE orders (id INTEGER PRIMARY KEY, customer TEXT NOT NULL);

CREATE TABLE order_items (
  id INTEGER PRIMARY KEY,
  order_id INTEGER NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
  product TEXT NOT NULL
);

INSERT INTO orders (id, customer) VALUES (1, 'Asha');
INSERT INTO order_items (id, order_id, product) VALUES (1, 1, 'pen'), (2, 1, 'book');

DELETE FROM orders WHERE id = 1;

SELECT COUNT(*) AS remaining FROM order_items;

Quick quiz

Interview questions