Relationship Cardinality in Database Design

Cardinality describes how many instances of one entity can be associated with how many instances of another. Every relationship in a schema is one of three basic shapes — one-to-one, one-to-many, or many-to-many — and the shape determines exactly how the relationship must be implemented physically: as a foreign key on one side, a foreign key on the "many" side, or an entirely separate join table.

Getting cardinality wrong is one of the most expensive mistakes to fix later, because it usually means the physical structure of a table needs to change, not just a constraint. Deciding cardinality correctly during design — by asking "can there be more than one?" in both directions — avoids that rework.

Why it matters

Cardinality is how many rows on one side can relate to how many on the other, and each case has exactly one correct table structure. Modelling a many-to-many relationship as one-to-many is among the most common design mistakes, and it shows up later as data that simply cannot be recorded.

One-to-One

A one-to-one relationship means each row in table A relates to at most one row in table B, and vice versa. It is implemented by putting a foreign key with a UNIQUE constraint on one of the two tables (often the one that is optional or less frequently accessed). One-to-one relationships are commonly used to split a large or sensitive table — for example, separating rarely-needed profile details or sensitive data from a frequently-queried core users table.

One-to-Many

A one-to-many relationship means one row in table A can relate to many rows in table B, but each row in table B relates to only one row in table A. This is the most common relationship shape (one customer has many orders, one department has many employees) and is implemented with a plain foreign key on the "many" side, as described in the relationships topic.

Many-to-Many and the Join Table

A many-to-many relationship means rows on both sides can relate to multiple rows on the other side — one order can contain many products, and one product can appear on many orders. Relational databases cannot express many-to-many directly with a single foreign key; it must be resolved with a join table (also called an associative or junction table) that holds a foreign key to each side, and typically uses the combination of both as its composite primary key.

• One-to-one: FK with UNIQUE constraint on one side (e.g. users.id ↔ user_profiles.user_id UNIQUE).
• One-to-many: plain FK on the "many" side (e.g. orders.customer_id).
• Many-to-many: a join table with FKs to both sides (e.g. order_items with order_id and product_id).

Example

-- One-to-one: each user has exactly one extended profile
CREATE TABLE users (
    id     BIGINT PRIMARY KEY,
    email  VARCHAR(255) NOT NULL UNIQUE
);

CREATE TABLE user_profiles (
    user_id    BIGINT PRIMARY KEY REFERENCES users(id),
    bio        TEXT,
    avatar_url VARCHAR(500)
);

-- One-to-many: one author writes many posts
CREATE TABLE posts (
    id         BIGINT PRIMARY KEY,
    author_id  BIGINT NOT NULL REFERENCES users(id),
    title      VARCHAR(200) NOT NULL
);

-- Many-to-many: students enroll in many courses, courses have many students
CREATE TABLE students (
    id    BIGINT PRIMARY KEY,
    name  VARCHAR(150) NOT NULL
);

CREATE TABLE courses (
    id    BIGINT PRIMARY KEY,
    title VARCHAR(150) NOT NULL
);

CREATE TABLE enrollments (
    student_id  BIGINT NOT NULL REFERENCES students(id),
    course_id   BIGINT NOT NULL REFERENCES courses(id),
    enrolled_on DATE NOT NULL DEFAULT CURRENT_DATE,
    PRIMARY KEY (student_id, course_id)
);

user_profiles.user_id being both the primary key and a foreign key
enforces exactly one profile per user (one-to-one). posts.author_id
allows an author to have many posts, but each post has exactly one
author (one-to-many). enrollments resolves the many-to-many between
students and courses; the composite primary key (student_id, course_id)
prevents the same student from enrolling in the same course twice.

Exercise

A user has at most one profile. Enforce that one-to-one relationship in the profiles table so that the second insert below, a second profile for user 1, is rejected. Then count the profiles.

Expected output: profiles then 1

CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT NOT NULL);

-- TODO: profiles (id, user_id, bio) where each user can have only one profile

INSERT INTO users (id, name) VALUES (1, 'Asha');
INSERT INTO profiles (id, user_id, bio) VALUES (1, 1, 'Developer');
INSERT INTO profiles (id, user_id, bio) VALUES (2, 1, 'Second profile');

SELECT COUNT(*) AS profiles FROM profiles;

CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT NOT NULL);

CREATE TABLE profiles (
  id INTEGER PRIMARY KEY,
  user_id INTEGER NOT NULL UNIQUE REFERENCES users(id),
  bio TEXT
);

INSERT INTO users (id, name) VALUES (1, 'Asha');
INSERT INTO profiles (id, user_id, bio) VALUES (1, 1, 'Developer');
INSERT INTO profiles (id, user_id, bio) VALUES (2, 1, 'Second profile');

SELECT COUNT(*) AS profiles FROM profiles;

Quick quiz

Interview questions