TopolearnGetting cardinality right is the most common modelling decision and the hardest to undo once data exists.
Every relationship has a cardinality: how many rows on each side can be linked.
- One-to-many (1:N): a customer has many orders, each order has one customer. Put a foreign key on the many side:
orders.customer_id. This is by far the most common shape. - Many-to-many (M:N): a student takes many courses and a course has many students. Relational tables can't hold a list in a column, so you add a junction table (also called an associative or join table) with a foreign key to each side. Its primary key is usually the pair, which also prevents duplicates.
- One-to-one (1:1): a user has at most one profile. Use a foreign key that is also
UNIQUE(or make the child's primary key the foreign key). Often 1:1 tables are simply merged, unless one side is optional, large or has different access rules.
Junction tables frequently grow attributes of their own: an enrollment has a grade and an enrolled date. When that happens the relationship has become an entity, which is fine — give it a name from the domain.
Optionality matters too. NOT NULL on the foreign key says "every order must have a customer"; a nullable one says the link is optional. Decide deliberately, because the constraint is what stops orphaned or half-linked data.
Example
CREATE TABLE student (id int PRIMARY KEY, name text NOT NULL);
CREATE TABLE course (id int PRIMARY KEY, title text NOT NULL);
-- M:N via a junction table that carries its own attributes
CREATE TABLE enrollment (
student_id int NOT NULL REFERENCES student(id) ON DELETE CASCADE,
course_id int NOT NULL REFERENCES course(id),
enrolled_on date NOT NULL DEFAULT current_date,
grade text,
PRIMARY KEY (student_id, course_id) -- no duplicate enrollments
);
-- 1:1 optional: at most one profile per student
CREATE TABLE profile (
student_id int PRIMARY KEY REFERENCES student(id),
bio text
);Remember
- 1:N: foreign key on the many side
- M:N: junction table with a composite primary key
- 1:1: a foreign key that is also unique (or the primary key)
Watch out for
- Storing comma-separated ids in a column instead of a junction table.