SQL Interview Prep · Lesson

ER Modeling and Relationship Cardinality

Translating requirements into entities, relationships, and junction tables.

ER Modeling and Relationship Cardinality is a free SQL Interview Prep lesson on CoddyKit — lesson 2 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Interview Prep learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

Why ER Modeling Shows Up in Interviews

After normalization, interviewers test whether you can turn requirements into a schema. The prompt is usually open-ended: "Design a database for a ride-sharing app" or "Model a library system."

This is an entity-relationship (ER) modeling exercise. They are watching how you identify entities, attributes, and the relationships between them, including cardinality.

The skill is converting English nouns and verbs into tables and foreign keys.

Entities, Attributes, Relationships

Three building blocks form every ER model:

Entity: a thing you store data about (Customer, Order, Product). Usually becomes a table.
Attribute: a property of an entity (name, price, created_at). Usually becomes a column.
Relationship: how entities connect (a Customer places an Order). Implemented with foreign keys or junction tables.

Tip from the prompt: nouns become entities/attributes, verbs become relationships.

Cardinality: The Core Concept

Cardinality describes how many instances of one entity relate to another. The three families:

One-to-one (1:1): one row here matches at most one row there.
One-to-many (1:N): one row here matches many rows there (the most common).
Many-to-many (M:N): rows on both sides match many on the other.

Getting cardinality right determines where foreign keys go and whether you need a junction table.

Implementing One-to-Many

One-to-many is implemented by putting the foreign key on the "many" side. A customer has many orders, so each order row carries the customer_id.

In an interview, always state the direction explicitly: "One customer to many orders, so the FK lives on orders."

CREATE TABLE customers (
  customer_id INT PRIMARY KEY,
  name        VARCHAR(100)
);

CREATE TABLE orders (
  order_id    INT PRIMARY KEY,
  customer_id INT NOT NULL,
  order_date  DATE,
  FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

Implementing Many-to-Many

A relational database cannot store M:N directly. The answer interviewers want is a junction table (also called a bridge, link, or associative table).

Students enroll in many courses; courses have many students. Create an enrollments table whose key combines both foreign keys. This resolves M:N into two 1:N relationships.

CREATE TABLE students (
  student_id INT PRIMARY KEY,
  name       VARCHAR(100)
);

CREATE TABLE courses (
  course_id  INT PRIMARY KEY,
  title      VARCHAR(100)
);

CREATE TABLE enrollments (
  student_id INT,
  course_id  INT,
  enrolled_at DATE,
  PRIMARY KEY (student_id, course_id),
  FOREIGN KEY (student_id) REFERENCES students(student_id),
  FOREIGN KEY (course_id)  REFERENCES courses(course_id)
);

The Junction Table Can Hold Data

A common follow-up: "Where do you store the grade a student got in a course?"

The grade belongs to the relationship, not to the student or the course alone. So it goes on the junction table. This is the insight interviewers probe: attributes of an M:N relationship live on the bridge.

Examples: enrollment date, grade, quantity in an order line, a role in a project membership.

ALTER TABLE enrollments
  ADD COLUMN grade CHAR(2);
-- grade describes THIS student in THIS course,
-- so it belongs on the junction table

Implementing One-to-One

1:1 is rarer. You implement it by giving the dependent table a foreign key that is also a unique key (often the primary key itself).

Example: a user and a user_profile with extended, optional details. Making user_id the primary key of the profile table enforces at most one profile per user.

CREATE TABLE users (
  user_id INT PRIMARY KEY,
  email   VARCHAR(255)
);

CREATE TABLE user_profiles (
  user_id INT PRIMARY KEY,           -- 1:1 enforced here
  bio     TEXT,
  avatar_url VARCHAR(255),
  FOREIGN KEY (user_id) REFERENCES users(user_id)
);

Optionality and Participation

Cardinality has a second dimension interviewers like: optionality (also called participation).

Mandatory: every order must have a customer, so customer_id is NOT NULL.
Optional: a user may or may not have a profile, so the relationship can be absent.

You express mandatory participation with NOT NULL on the foreign key. Mentioning NULL-ability shows you think about real constraints, not just shapes.

Self-Referencing Relationships

Some relationships point an entity at itself. An employee has a manager who is also an employee; a category has a parent category.

You model this with a foreign key referencing the same table. Interviewers expect this for org charts and tree structures, and it pairs naturally with self-joins and recursive CTEs.

CREATE TABLE employees (
  employee_id INT PRIMARY KEY,
  name        VARCHAR(100),
  manager_id  INT NULL,
  FOREIGN KEY (manager_id) REFERENCES employees(employee_id)
);
-- manager_id NULL = top of the hierarchy (e.g. CEO)

A Mini Modeling Walkthrough

Practice the verb-to-relationship method. Prompt: "Customers place orders; each order contains many products; products belong to suppliers."

Customer 1:N Order (FK customer_id on orders).
Order M:N Product -> junction order_items (with quantity).
Supplier 1:N Product (FK supplier_id on products).

State each cardinality and where the key goes. That narration is the interview win.

Clarifying Questions to Ask

Interviewers reward candidates who ask before designing. Good clarifying questions:

"Can a product belong to more than one supplier?" (decides 1:N vs M:N).
"Must every order have at least one item?" (participation).
"Do we need history, or just the current state?" (drives extra tables).

The answers change cardinality and table count, so never assume. Asking is a signal of seniority.

Quick Check

You are modeling students and courses where each student can take many courses and each course has many students.

Recap: ER Modeling and Cardinality

You can now drive an open-ended schema design question:

Turn nouns into entities/attributes, verbs into relationships.
1:N: foreign key on the many side.
M:N: junction table holding both FKs, plus any relationship attributes.
1:1: a shared/unique key on the dependent table.
Use NOT NULL to express mandatory participation, and self-referencing FKs for hierarchies.
Ask clarifying questions before committing to cardinality.

Frequently asked questions

Is the “ER Modeling and Relationship Cardinality” lesson free?

Yes — the full text of “ER Modeling and Relationship Cardinality” is free to read here on the web, and the SQL Interview Prep course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Interview Prep course, upgrade to CoddyKit PRO.

What will I learn in “ER Modeling and Relationship Cardinality”?

Translating requirements into entities, relationships, and junction tables. You practise SQL Interview Prep with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start SQL Interview Prep?

No prior experience is required. SQL Interview Prep on CoddyKit is structured for beginners through advanced learners; this is — lesson 2 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “ER Modeling and Relationship Cardinality” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this SQL Interview Prep lesson?

Yes. Every SQL Interview Prep lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

Normalization Through 3NF
ER Modeling and Relationship Cardinality
Star Schema and Data Warehouse Design
Full Mock Interview Problem Set

← Back to SQL Interview Prep