Cardinality in Database Design — 1:1, 1:N, M:N
Fast recall
Quick revision
Core answer
Whether the candidate can identify relationship types and translate them into correct SQL foreign key design
Not understanding that M:N cardinality cannot be implemented with a single foreign key and requires a junction table
Provide concrete real-world examples for each cardinality type and explain the SQL implementation details including where the foreign key goes
Interview-ready guide
Interview answer
Cardinality describes how many entity instances may relate: one-to-one, one-to-many, or many-to-many. A one-to-one relational mapping commonly uses a foreign key with UNIQUE. A one-to-many mapping commonly stores the parent key on each row of the many side. A many-to-many Student-Course relationship uses an Enrollment junction with two foreign keys; their pair can be a composite primary key or a UNIQUE constraint beside a surrogate key. Minimum participation—zero or one—is a separate rule from maximum cardinality.
Cardinality in Database Design — 1:1, 1:N, M:N
Direct Answer
Cardinality defines how many instances of one entity relate to another. Types: 1:1 (one person has one passport), 1:N (one customer has many orders), M:N (many students can enroll in many courses). Cardinality determines how tables are related via foreign keys.
Code Example
-- 1:1 — User has one Profile
CREATE TABLE "user" (
user_id BIGSERIAL PRIMARY KEY,
username VARCHAR(50) UNIQUE NOT NULL
);
CREATE TABLE profile (
profile_id BIGSERIAL PRIMARY KEY,
bio TEXT,
user_id BIGINT UNIQUE NOT NULL, -- FK on dependent side, UNIQUE enforces 1:1
CONSTRAINT fk_user FOREIGN KEY (user_id) REFERENCES "user"(user_id)
);
-- 1:N — Department has many Employees
CREATE TABLE department (
dept_id BIGSERIAL PRIMARY KEY,
dept_name VARCHAR(100) NOT NULL
);
CREATE TABLE employee (
emp_id BIGSERIAL PRIMARY KEY,
emp_name VARCHAR(100),
dept_id BIGINT NOT NULL, -- FK on the N side (many employees)
CONSTRAINT fk_dept FOREIGN KEY (dept_id) REFERENCES department(dept_id)
);
-- M:N — Students enroll in Courses
CREATE TABLE student (student_id BIGSERIAL PRIMARY KEY, name VARCHAR(100));
CREATE TABLE course (course_id BIGSERIAL PRIMARY KEY, title VARCHAR(100));
CREATE TABLE enrollment ( -- Junction table for M:N
student_id BIGINT,
course_id BIGINT,
enrolled_date DATE DEFAULT CURRENT_DATE,
PRIMARY KEY (student_id, course_id),
CONSTRAINT fk_student FOREIGN KEY (student_id) REFERENCES student(student_id),
CONSTRAINT fk_course FOREIGN KEY (course_id) REFERENCES course(course_id)
);
Common Mistakes & Pitfalls
- Placing FK on the wrong side in 1:N: Freshers put a customer_id column in the customer table pointing to orders, which inverts the relationship and only allows one order per customer. The FK must go on the many side (order table).
- Trying to implement M:N with a single FK: Attempting to store multiple course IDs in a single column as a comma-separated string (student table with courses='101,203,305') breaks normalization, makes querying impossible, and violates foreign key constraints.
- Using 1:1 when 1:N is correct: Modeling Address as 1:1 with Customer when customers can have multiple addresses (shipping, billing)—forcing a redesign later when the business requirement surfaces.
Check your understanding
Answer these without looking back at the article.
- How do you implement a 1:1 relationship in SQL?
- Why does a M:N relationship require a junction table?
- What is the difference between a 1:N and M:N relationship in JPA?
Looking for a structured roadmap?
Every module on this page is also part of the full Java backend interview track.