Entities, Relationships, and Cardinality

Cardinality decides which table holds the foreign key

Before you can write a single JOIN, someone decided which table holds which key. That decision is data modeling, and getting it wrong is expensive: a foreign key on the wrong side forces application code to fake relationships, and a missing UNIQUE lets a "one profile per user" rule silently allow five. The whole schema either falls out cleanly or fights you forever, and it starts with cardinality.

The three shapes

Schema

• customer_id
• name

• order_id
• customer_id
• total

• user_id

• profile_id
• user_id

• ordersn-1customers— placed by (FK on the many side)
• profiles1-1users— UNIQUE FK = one profile per user

An entity is a thing you store (a customer, an order, a product). A relationship links entities. Cardinality says how many rows on one side can link to how many on the other:

• 1:N (one-to-many): one customer has many orders; each order belongs to one customer. By far the most common shape.
• 1:1 (one-to-one): one user has one profile. Rare, usually a deliberate table split.
• M:N (many-to-many): one order contains many products, and one product appears in many orders.

The rule: a foreign key is a single-valued pointer

A foreign key column holds exactly one value per row. So it can only encode a "to-one" direction, which means the FK lives on the "many" side. In a 1:N, each order points at exactly one customer, so customer_id sits on orders. It cannot go the other way: a customer row has no single order_id to store, because a customer has many orders.

CREATE TABLE customers (customer_id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE orders (
    order_id    INTEGER PRIMARY KEY,
    customer_id INTEGER NOT NULL REFERENCES customers(customer_id)  -- FK on the many side
);

INSERT INTO customers VALUES (1, 'Ada');
INSERT INTO orders   VALUES (100, 1), (101, 1);   -- Ada placed two orders

SELECT c.name, o.order_id
FROM customers c JOIN orders o ON o.customer_id = c.customer_id;
-- name | order_id
-- Ada  | 100
-- Ada  | 101

1:1 is the same FK plus a UNIQUE constraint. Split the optional columns into their own table and put a UNIQUE FK on it, e.g. profiles.user_id INTEGER UNIQUE REFERENCES users(user_id). Without UNIQUE, two profile rows could share one user_id, which is just a 1:N. The UNIQUE is what forbids the duplicate and pins the relationship to one.

M:N cannot be expressed with a single FK, because neither side is "to-one." It needs a third junction table holding two FKs (one pointing at each side), which is the next lesson. That is why a playlists-to-songs feature leaves songs standalone for now.

1:N   FK on the many side (no UNIQUE)
1:1   FK on one side, plus UNIQUE on that FK
M:N   junction table with two FKs (next lesson)

Pitfalls

• FK on the wrong side of a 1:N. The tell is wanting to store "a list of order ids" in one customers column. A column holds one value, so that list does not fit. Move the FK to the many side.
• Forgetting UNIQUE on a 1:1. A plain FK on the split table is a 1:N in disguise; nothing stops two children from sharing one parent. Add UNIQUE to the FK column to enforce the "one."

Warehouse note: ER modeling and FK placement are engine-independent. Many warehouses declare but do not enforce foreign keys, yet the placement decision (which table holds the key) is identical and still drives how you join.

Interview nuance: the FK's direction encodes cardinality (which side is "many"), but its nullability encodes participation, a separate axis. orders.customer_id NOT NULL means every order must have a customer (mandatory participation); making it nullable allows an order with no customer at all (optional). On a 1:1 split, UNIQUE already caps each parent at one child no matter what; adding NOT NULL to that FK is what forces every child to have a parent, giving exactly one parent per child instead of zero-or-one. Interviewers probe this to see whether you separate "how many" (cardinality) from "is it required" (participation).

Execution mode: you write a multi-statement script. It runs against a fresh in-memory SQLite database, then hidden assertion queries check FK placement, the UNIQUE that encodes 1:1, and that M:N was left for a junction table.