🗺️Mapping ER Model to Relational Model day 2

From ER Diagrams to Real Database Tables: A Practical Guide to ER-to-Relational Mapping

Imagine you're building an e-commerce application.

Your business requirements say:

• A customer can place multiple orders.
• Every order belongs to one customer.
• An order can contain multiple products.
• A product can appear in multiple orders.
• Every order must have at least one order item.

You understand SQL. You can write SELECT, INSERT, UPDATE, and DELETE queries.

But here's the real question:

How do you decide which tables to create, where foreign keys belong, and when a separate relationship table is necessary?

This is where ER-to-Relational Mapping comes in.

In this article, we'll take a practical journey:

1. Understand how business requirements become ER diagrams.
2. Learn the building blocks of ER modeling.
3. Understand cardinality and participation constraints.
4. Convert all major ER relationship types into relational tables.
5. Design primary keys, foreign keys, and database constraints.
6. Apply these concepts to production-style e-commerce, HR, SaaS, education, and billing systems.
7. Understand the trade-offs experienced backend engineers consider.
8. Prepare for DBMS interview questions and test your understanding.

By the end, you should be able to look at an ER diagram and independently design a relational schema instead of memorizing foreign-key placement rules.

---

1. Context: Why Do We Need ER-to-Relational Mapping?

ER-to-Relational Mapping is the process of converting an Entity-Relationship (ER) model into relational database tables, columns, primary keys, foreign keys, and constraints.

But why do we need this conversion?

Consider a business requirement:

A customer can place multiple orders, but every order must belong to exactly one customer.

This describes how the business operates. It does not tell us exactly how to implement the database.

We need to translate it into something PostgreSQL or MySQL can understand.

The process looks like this:

Business Requirements
        |
        v
Conceptual Database Design
        |
        v
ER Diagram
        |
        v
ER-to-Relational Mapping
        |
        v
Relational Schema
        |
        v
SQL CREATE TABLE Statements
        |
        v
Actual Database

Why can't we implement an ER diagram directly?

An ER diagram is a conceptual representation of data.

It describes:

• What entities exist.
• What information they contain.
• How entities relate to each other.
• How many records can participate in relationships.
• Whether participation is optional or compulsory.

A relational database, however, stores data using relations (tables), tuples (rows), attributes (columns), and constraints.

An ER diagram is therefore a design blueprint, not an executable database schema.

Think of it this way:

An architect creates a building blueprint. The construction team converts that blueprint into actual walls, doors, and rooms.

Similarly, a database designer creates an ER diagram, and the developer converts it into actual relational tables.

---

2. Core Concept: Understand the Building Blocks First

Before converting an ER diagram, let's understand what we're converting.

2.1 Entity

Simple explanation: An entity is something about which your application needs to store information.

For example, in an e-commerce application:

• Customer
• Product
• Order
• Payment

Each represents something important to the business.

Technical definition: An entity is a distinguishable real-world object or concept that can be independently represented in a data model.

2.2 Entity Instance

An entity describes a category. An entity instance represents one particular member of that category.

For example:

Entity: Customer

Instances:
Customer 101: Ali
Customer 102: Ahmed
Customer 103: Sara

In relational terms, each instance eventually becomes a row in a table.

2.3 Attribute

An attribute describes a property of an entity.

For example:

Customer
---------
customer_id
name
email
phone

Here, name, email, and phone are attributes.

In the relational model, attributes become columns.

2.4 Relationship

A relationship describes an association between entities.

Example:

Customer -------- Places -------- Order

The business meaning is that customers place orders.

A relationship can also contain its own attributes.

For example:

Student -------- Enrolls -------- Course
                    |
                    |
                enrolled_at
                grade
                status

Here, enrolled_at, grade, and status describe the enrollment, not the student or course independently.

This distinction becomes important when deciding whether a relationship needs its own table.

2.5 Cardinality

Cardinality describes how many instances of one entity can be associated with instances of another entity.

The common relationship types are:

• One-to-one (1:1)
• One-to-many (1:N)
• Many-to-one (N:1)
• Many-to-many (M:N)

For example:

One Customer ---- Many Orders

One customer can place multiple orders, while each order belongs to one customer.

2.6 Participation Constraint

Participation describes whether an entity's involvement in a relationship is mandatory or optional.

There are two types:

Total participation: Every instance must participate in the relationship.

Partial participation: An instance may or may not participate.

Example:

Customer -------- Places -------- Order

Suppose customers can register without placing an order.

Customer participation is partial.

Suppose every order must belong to a customer.

Order participation is total.

An important distinction:

• Cardinality tells us HOW MANY.
• Participation tells us WHETHER participation is compulsory.

2.7 Primary Key

A primary key uniquely identifies each row in a table.

Example:

CREATE TABLE customers (
    id BIGINT PRIMARY KEY,
    name VARCHAR(100)
);

Two customers cannot have the same primary-key value, and the primary key cannot contain NULL.

2.8 Foreign Key

A foreign key establishes a reference between tables.

CREATE TABLE orders (
    id BIGINT PRIMARY KEY,
    customer_id BIGINT NOT NULL,

    CONSTRAINT fk_orders_customer
        FOREIGN KEY (customer_id)
        REFERENCES customers(id)
);

Here, customer_id references customers.id.

The database prevents an order from referencing a customer that does not exist.

2.9 Relational Schema

A relational schema describes the structure of relational tables, including their attributes and constraints.

Example:

CUSTOMERS(
    id PK,
    name,
    email
)

ORDERS(
    id PK,
    customer_id FK,
    total_amount
)

This is the logical representation of the database.

Conceptual Design vs Logical Design

Conceptual design	Logical design
Focuses on business requirements	Focuses on relational structures
Uses entities and relationships	Uses tables and keys
Shows cardinality	Implements cardinality through constraints
Independent of SQL syntax	Describes relational schema
Example: Customer places Order	Example: orders.customer_id FK

Remember: ER modeling helps us understand the business. Relational modeling helps us represent that understanding using tables.

---

3. The Most Important Decisions Before Mapping

Before writing SQL, answer these questions.

3.1 Who owns the foreign key?

A foreign key normally goes into the table that needs to reference another table.

In a one-to-many relationship, that is the many side.

3.2 Is the relationship unique?

If one employee can have only one parking space, the corresponding FK must be unique.

If many employees can belong to one department, the FK must not be unique.

3.3 Can the relationship be absent?

If participation is optional, the FK may need to accept NULL.

If participation is compulsory and represented directly by that FK, use NOT NULL.

3.4 Does the relationship need its own attributes?

If an association contains meaningful information, a separate relationship table or associative entity may be appropriate.

3.5 Which constraints preserve the original business rules?

Common SQL constraints include:

Constraint	Purpose
PRIMARY KEY	Unique row identification
FOREIGN KEY	Valid references
UNIQUE	Prevent duplicate values
NOT NULL	Require a value
CHECK	Enforce a condition

Let's apply these decisions to real ER mapping cases.

---

4. ER-to-Relational Mapping: Six Important Cases

CASE 1: Binary 1:1 Relationship with Total Participation on One Side

Business scenario: Person and Passport

Imagine a government passport management system.

Business rules:

1. A person can have at most one passport.
2. Every passport must belong to exactly one person.
3. A person may exist without a passport.
4. A passport cannot exist without its owner.

ER representation

PERSON                 PASSPORT
-------                --------
person_id              passport_id
name                   issue_date
                       expiry_date

       1 -------- 1
          OWNS

Cardinality: 1:1.

Participation:

• Person: Partial.
• Passport: Total.

Step 1: Create the initial tables

PERSON(
    person_id,
    name
)

PASSPORT(
    passport_id,
    issue_date,
    expiry_date
)

Step 2: Decide FK placement

Since every passport must belong to a person, we can place the FK in PASSPORT.

PERSON(
    person_id PK,
    name
)

PASSPORT(
    passport_id PK,
    person_id FK,
    issue_date,
    expiry_date
)

Why?

Every passport needs an owner, so its person_id cannot be NULL.

Step 3: Enforce the 1:1 relationship

A foreign key alone does not enforce one-to-one cardinality.

Without UNIQUE, multiple passports could reference the same person.

Therefore, we need:

• FOREIGN KEY: The person must exist.
• UNIQUE: A person can be referenced by at most one passport.
• NOT NULL: Every passport must have an owner.

Final PostgreSQL schema

CREATE TABLE persons (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

CREATE TABLE passports (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    person_id BIGINT NOT NULL UNIQUE,
    issue_date DATE NOT NULL,
    expiry_date DATE NOT NULL,

    CONSTRAINT fk_passport_person
        FOREIGN KEY (person_id)
        REFERENCES persons(id),

    CONSTRAINT chk_passport_dates
        CHECK (expiry_date > issue_date)
);

Sample records

PERSON:

id	name
1	Ali
2	Ahmed
3	Sara

PASSPORT:

id	person_id	issue_date
101	1	2025-01-10
102	3	2025-04-15

Notice that Ahmed has no passport. This is valid because Person participation is partial.

Every passport, however, has a valid owner.

Everyday analogy

Think of a vehicle registration certificate.

A vehicle may exist before registration, but an issued registration certificate must identify its vehicle.

Production-grade example

The same pattern can represent:

• Employee and company-issued laptop.
• User and profile record when exactly one profile is mandatory.
• Company and its legally unique registration record.

Always verify the actual business rules before selecting this design.

Why not merge both tables?

We could store passport information directly in PERSON:

PERSON(
    id,
    name,
    passport_number,
    issue_date,
    expiry_date
)

This is sometimes perfectly reasonable.

However, if passport information has an independent lifecycle, security boundary, or optional existence, separate tables may be easier to maintain.

Also, merging tables does not automatically preserve all business semantics.

Interview question: Why is the FK normally placed on the total participation side?

Answer: It allows every row on that side to reference the other entity through a NOT NULL FK, while UNIQUE enforces the one-to-one restriction. This is a convenient mapping when the relationship's business rules support it.

Golden insight: In a 1:1 relationship, use a UNIQUE FK. Put it on the side whose existence requires the association when that is the most practical representation.

---

CASE 2: Binary 1:1 Relationship with Partial Participation on Both Sides

Business scenario: Employee and Parking Space Assignment

Imagine an organization with employees and parking spaces.

Business rules:

1. An employee may or may not have an assigned parking space.
2. A parking space may remain unassigned.
3. An employee can have at most one assigned parking space.
4. A parking space can be assigned to at most one employee.

ER representation

EMPLOYEE                PARKING_SPACE
--------                -------------
employee_id             space_id
name                    location

        1 -------- 1
          ASSIGNED

Participation:

• Employee: Partial.
• Parking Space: Partial.

Option A: Two-table mapping

Place a nullable UNIQUE FK in either table.

For this example, we'll place it in EMPLOYEE.

EMPLOYEE(
    employee_id PK,
    name,
    parking_space_id FK UNIQUE NULL
)

PARKING_SPACE(
    space_id PK,
    location
)

Why nullable?

An employee can exist without an assigned parking space.

Why UNIQUE?

Two employees cannot reference the same parking space.

SQL implementation

CREATE TABLE parking_spaces (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    location VARCHAR(100) NOT NULL
);

CREATE TABLE employees (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name VARCHAR(100) NOT NULL,

    parking_space_id BIGINT UNIQUE,

    CONSTRAINT fk_employee_parking
        FOREIGN KEY (parking_space_id)
        REFERENCES parking_spaces(id)
);

In PostgreSQL, an ordinary UNIQUE constraint permits multiple NULL values. This allows many employees to remain unassigned.

Option B: Three-table mapping

Instead of putting the FK inside either entity table, create a separate relationship table.

EMPLOYEE(
    id PK,
    name
)

PARKING_SPACE(
    id PK,
    location
)

PARKING_ASSIGNMENT(
    employee_id PK/FK,
    space_id UNIQUE/FK,
    assigned_at
)

SQL:

CREATE TABLE parking_assignments (
    employee_id BIGINT PRIMARY KEY,
    space_id BIGINT NOT NULL UNIQUE,
    assigned_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

    FOREIGN KEY (employee_id)
        REFERENCES employees(id),

    FOREIGN KEY (space_id)
        REFERENCES parking_spaces(id)
);

This design naturally represents an assignment only when it exists.

It also provides a place for relationship attributes such as:

• Assigned date.
• Approved by.
• Assignment status.

Important distinction

If we need assignment history, the above three-table design is insufficient because employee_id as the primary key allows only one assignment row per employee.

A historical design could instead use:

CREATE TABLE parking_assignments (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    employee_id BIGINT NOT NULL REFERENCES employees(id),
    space_id BIGINT NOT NULL REFERENCES parking_spaces(id),
    assigned_at TIMESTAMP NOT NULL,
    released_at TIMESTAMP,

    CHECK (
        released_at IS NULL OR released_at > assigned_at
    )
);

CREATE UNIQUE INDEX uq_active_employee_parking
    ON parking_assignments(employee_id)
    WHERE released_at IS NULL;

CREATE UNIQUE INDEX uq_active_space_assignment
    ON parking_assignments(space_id)
    WHERE released_at IS NULL;

Now historical assignments are retained, while partial unique indexes enforce one active assignment per employee and parking space.

Why not always use a separate table?

A separate table adds joins and another relation to maintain.

For a simple 1:1 association without independent attributes or history, a nullable UNIQUE FK is usually simpler.

Interview question

Can a 1:1 relationship be represented without a separate relationship table?

Answer: Yes. A UNIQUE foreign key can represent it. A separate relationship table is useful when the association has independent attributes, lifecycle, history, or other modeling requirements.

Golden insight: Partial participation on both sides does not automatically require three tables.

---

CASE 3: Binary 1:N and N:1 Relationship

Business scenario: Customer and Orders

Consider an e-commerce application.

Business rules:

1. A customer can place multiple orders.
2. Every order belongs to exactly one customer.
3. A customer may have zero orders.
4. An order cannot exist without a customer.

ER representation

CUSTOMER                  ORDER
--------                  -----
customer_id               order_id
name                      total_amount

       1 -------------- N
             PLACES

Cardinality: 1:N.

Participation:

• Customer: Partial.
• Order: Total.

Step 1: Initial tables

CUSTOMER(
    customer_id,
    name
)

ORDER(
    order_id,
    total_amount
)

Step 2: Place the FK on the N-side

CUSTOMER(
    customer_id PK,
    name
)

ORDER(
    order_id PK,
    customer_id FK,
    total_amount
)

Why the N-side?

One customer can be referenced by many orders.

Each order needs only one customer reference.

SQL implementation

CREATE TABLE customers (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(255) NOT NULL UNIQUE
);

CREATE TABLE orders (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_id BIGINT NOT NULL,
    total_amount NUMERIC(12, 2) NOT NULL,

    CONSTRAINT fk_order_customer
        FOREIGN KEY (customer_id)
        REFERENCES customers(id),

    CHECK (total_amount >= 0)
);

CREATE INDEX idx_orders_customer_id
    ON orders(customer_id);

Sample records

CUSTOMERS:

id	name
1	Ali
2	Ahmed

ORDERS:

id	customer_id	total_amount
101	1	2500
102	1	4000
103	2	1800

Notice that customer 1 has two orders.

This is exactly what the FK on the N-side allows.

Why not put multiple order IDs inside CUSTOMER?

An incorrect design might look like:

CUSTOMER(
    id,
    name,
    order_ids = "101,102,103"
)

This introduces problems:

• Difficult filtering.
• Difficult joins.
• Difficult referential integrity.
• Difficult updates.
• Multiple values inside one column.

A relational design should represent each order as its own row.

Production examples

Relationship	FK placement
Department → Employees	employees.department_id
Customer → Orders	orders.customer_id
Category → Products	products.category_id
Course → Students	students.course_id, only if each student has one course
Company → SaaS Users	users.company_id

Important education example: If students can enroll in multiple courses and courses can have multiple students, it is not 1:N. It is M:N and requires the next mapping pattern.

What if the relationship is optional?

Suppose employees may not be assigned to a department.

Then:

department_id BIGINT NULL

If every employee must belong to a department:

department_id BIGINT NOT NULL

The cardinality remains 1:N. Only participation changes.

Why not create a separate relationship table?

For a straightforward 1:N relationship, the FK on the N-side is enough.

A separate table is appropriate when the association itself needs independent representation, such as historical department assignments.

Interview question: Why does a 1:N relationship normally not need a junction table?

Answer: Because each row on the N-side needs only one reference to the 1-side. A foreign key can represent that association directly.

Golden insight: For a conventional 1:N relationship, the FK belongs on the N-side.

---

CASE 4: Binary M:N Relationship

Business scenario: Students and Courses

Business rules:

1. A student can enroll in multiple courses.
2. A course can contain multiple students.
3. An enrollment records when the student joined.
4. Enrollment has its own status and grade.

ER representation

STUDENT                  COURSE
-------                  ------
student_id               course_id
name                     title

        M -------- N
           ENROLLS

This is a many-to-many relationship.

Why can't we use one FK?

If we put course_id inside STUDENT, each student would reference only one course.

If we put student_id inside COURSE, each course would reference only one student.

Neither design represents both sides correctly.

We need an associative table.

Step 1: Create three relations

STUDENT(
    id PK,
    name
)

COURSE(
    id PK,
    title
)

ENROLLMENT(
    student_id PK/FK,
    course_id PK/FK,
    enrolled_at,
    grade
)

Step 2: Understand the composite primary key

The combination of student_id and course_id identifies one enrollment.

Why?

A student can enroll in many courses, and a course can contain many students.

But a student should not have duplicate active enrollment records for the same course under this simplified model.

Therefore:

PRIMARY KEY (student_id, course_id)

Neither FK is individually unique.

The combination is unique.

Complete PostgreSQL schema

CREATE TABLE students (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

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

CREATE TABLE enrollments (
    student_id BIGINT NOT NULL,
    course_id BIGINT NOT NULL,
    enrolled_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    grade NUMERIC(5, 2),
    status VARCHAR(20) NOT NULL DEFAULT 'active',

    PRIMARY KEY (student_id, course_id),

    FOREIGN KEY (student_id)
        REFERENCES students(id),

    FOREIGN KEY (course_id)
        REFERENCES courses(id),

    CHECK (grade IS NULL OR grade BETWEEN 0 AND 100),

    CHECK (status IN ('active', 'completed', 'cancelled'))
);

CREATE INDEX idx_enrollments_course_id
    ON enrollments(course_id);

Sample records

STUDENTS:

id	name
1	Ali
2	Sara

COURSES:

id	title
10	Database Systems
20	Operating Systems

ENROLLMENTS:

student_id	course_id	status
1	10	active
1	20	completed
2	10	active

Student 1 is enrolled in two courses.

Course 10 contains two students.

When should we use a surrogate primary key?

Instead of a composite key, we could use:

CREATE TABLE enrollments (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    student_id BIGINT NOT NULL REFERENCES students(id),
    course_id BIGINT NOT NULL REFERENCES courses(id),

    enrolled_at TIMESTAMP NOT NULL,

    UNIQUE (student_id, course_id)
);

This is useful when:

• Other tables need to reference an enrollment.
• The relationship has an independent lifecycle.
• ORM associations work more conveniently with a single-column ID.
• External systems need a stable enrollment identifier.

The UNIQUE constraint is still essential. Without it, duplicate enrollments would be possible.

Important: If a student can retake the same course multiple times, the business key must change. For example, include academic_year or attempt_number in the uniqueness rule.

Production examples

• Users ↔ Roles through user_roles.
• Roles ↔ Permissions through role_permissions.
• Products ↔ Categories through product_categories.
• Students ↔ Courses through enrollments.

Why this mapping works

The associative table converts one M:N relationship into two 1:N relationships:

STUDENT 1 -------- N ENROLLMENT N -------- 1 COURSE

This gives us a proper place to store relationship attributes and enforce uniqueness.

Interview question: Why are neither foreign keys individually unique in a conventional M:N junction table?

Answer: Because each student can appear in multiple enrollment rows, and each course can appear in multiple enrollment rows. Only their combination identifies an enrollment.

Golden insight: An M:N relationship is normally represented by an associative relation containing references to both participating entities.

---

CASE 5: Weak Entity and Identifying Relationship

Business scenario: Employee and Dependents

Imagine an HR system.

Business rules:

1. An employee can have multiple dependents.
2. A dependent belongs to exactly one employee.
3. A dependent cannot exist independently of an employee.
4. A dependent's name or sequence alone may not uniquely identify them globally.

Strong entity vs weak entity

A strong entity has its own independent primary key.

A weak entity depends on another entity for identification.

Example:

EMPLOYEE
--------
employee_id PK
name

A dependent might be identified using:

employee_id + dependent_number

Here:

• Employee is the owner entity.
• Dependent is the weak entity.
• dependent_number is the partial key.
• The relationship between them is identifying.

ER representation

EMPLOYEE
   ||
   || 1
   ||
 IDENTIFIES
   ||
   || N
   ||
 DEPENDENT

The weak entity has total participation because it cannot exist without its owner.

Relational mapping

EMPLOYEE(
    employee_id PK,
    name
)

DEPENDENT(
    employee_id PK/FK,
    dependent_number PK,
    name,
    relationship
)

The owner's primary key becomes part of the weak entity's primary key.

SQL implementation

CREATE TABLE employees (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

CREATE TABLE dependents (
    employee_id BIGINT NOT NULL,
    dependent_number INTEGER NOT NULL,
    name VARCHAR(100) NOT NULL,
    relationship VARCHAR(50) NOT NULL,

    PRIMARY KEY (employee_id, dependent_number),

    FOREIGN KEY (employee_id)
        REFERENCES employees(id)
        ON DELETE CASCADE
);

Sample records

EMPLOYEES:

id	name
101	Ali
102	Ahmed

DEPENDENTS:

employee_id	dependent_number	name
101	1	Sara
101	2	Hamza
102	1	Ayesha

Notice that dependent number 1 exists for both employees.

That is valid because the partial key is unique only within the owner's identity.

Production-grade example: Invoice and Invoice Items

INVOICE(
    invoice_id PK,
    invoice_date
)

INVOICE_ITEM(
    invoice_id PK/FK,
    line_number PK,
    product_name,
    quantity,
    unit_price
)

A line item cannot exist without its invoice.

The combination of invoice ID and line number identifies the line.

Why not use product_id as the invoice item's identity?

The same product may appear on multiple lines or have different pricing, discounts, or configurations.

A product identifies a catalog item, not a particular invoice line.

Important production consideration

A weak entity does not have to use a composite primary key in every physical implementation.

For example, a developer might introduce an id column for ORM convenience while retaining:

UNIQUE (invoice_id, line_number)

The logical dependency must still be preserved.

Interview question: Why does a weak entity use the owner's primary key?

Answer: Because it cannot be uniquely identified independently. Its identity depends on the owner's identity combined with its partial key.

Golden insight: Weak entity identity = Owner's PK + Partial Key.

---

CASE 6: Multivalued Attributes

Business scenario: Employee and Phone Numbers

Suppose an employee can have multiple phone numbers.

A naive table might look like this:

EMPLOYEE
--------
id
name
phone_numbers

Example:

id	name	phone_numbers
1	Ali	0300-111, 0311-222

This is problematic in a normalized relational design.

Why?

• Searching for one specific phone becomes difficult.
• Individual phone updates become awkward.
• Uniqueness is harder to enforce.
• Joining and indexing individual phone numbers becomes difficult.

ER representation

EMPLOYEE
   |
   | 1
   |
 HAS_PHONE
   |
   | N
   |
 PHONE_NUMBER

Mapping

Create a separate relation:

EMPLOYEE(
    id PK,
    name
)

EMPLOYEE_PHONE(
    employee_id PK/FK,
    phone_number PK,
    phone_type
)

SQL implementation

CREATE TABLE employees (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

CREATE TABLE employee_phones (
    employee_id BIGINT NOT NULL,
    phone_number VARCHAR(20) NOT NULL,
    phone_type VARCHAR(20),

    PRIMARY KEY (employee_id, phone_number),

    FOREIGN KEY (employee_id)
        REFERENCES employees(id)
        ON DELETE CASCADE
);

Sample records

EMPLOYEES:

id	name
1	Ali

EMPLOYEE_PHONES:

employee_id	phone_number	phone_type
1	03001111111	Mobile
1	03112222222	Work
1	0423333333	Home

Now each phone number is represented independently.

What about JSON?

PostgreSQL supports JSONB:

CREATE TABLE employees (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    contact_data JSONB
);

JSONB can be useful for flexible, irregular, or document-oriented data.

However, it is not automatically equivalent to a normalized relational structure.

If phone numbers require individual ownership constraints, frequent joins, independent updates, or references from other tables, a separate relation is usually more appropriate.

Interview question: Why do we create another table for a multivalued attribute?

Answer: To represent each value as an individual relational row, preserve atomicity, and support independent querying, indexing, and constraints.

Golden insight: One value per relational cell; multiple independent values deserve independent rows.

---

5. Additional Concepts You Must Understand

The six mapping cases cover the major transformations. The following concepts help you handle more complex ER diagrams correctly.

5.1 Composite Attributes

A composite attribute can be divided into smaller meaningful attributes.

Example:

Customer
   |
 Address
   |
   +---- Street
   +---- City
   +---- Postal Code

In a relational schema, map the components into separate columns:

CREATE TABLE customers (
    id BIGINT PRIMARY KEY,
    street VARCHAR(150),
    city VARCHAR(100),
    postal_code VARCHAR(20)
);

Do not create an Address table merely because Address is composite. Create one when it has independent identity, reuse, lifecycle, or other business requirements.

5.2 Derived Attributes

A derived attribute can be calculated from other stored attributes.

Example:

date_of_birth
age

Age can be calculated using date of birth.

SELECT
    date_of_birth,
    EXTRACT(YEAR FROM AGE(CURRENT_DATE, date_of_birth)) AS age
FROM employees;

Usually, store the source data rather than a value that changes over time.

However, calculated values may be stored when performance, historical reporting, or snapshot requirements justify it.

5.3 Candidate Keys and Alternate Keys

A candidate key is a minimal set of attributes capable of uniquely identifying a row.

Example:

USERS(
    id,
    email,
    username
)

If all three are individually unique:

• id is a candidate key.
• email is a candidate key.
• username is a candidate key.

One candidate key is selected as the primary key.

The remaining candidate keys are alternate keys.

CREATE TABLE users (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    email VARCHAR(255) NOT NULL UNIQUE,
    username VARCHAR(50) NOT NULL UNIQUE
);

5.4 Relationship Attributes

Some information belongs to the association rather than either entity.

Example:

EMPLOYEE -------- WORKS_IN -------- DEPARTMENT
                       |
                  assigned_at
                  role

If employees can move between departments and the system must retain history, use a separate association table.

CREATE TABLE employee_department_history (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    employee_id BIGINT NOT NULL REFERENCES employees(id),
    department_id BIGINT NOT NULL REFERENCES departments(id),
    assigned_at TIMESTAMP NOT NULL,
    ended_at TIMESTAMP
);

This is a more appropriate design than overwriting the employee's department whenever a transfer occurs.

5.5 Associative Entities

An associative entity represents a relationship as an entity.

Enrollment is a good example:

STUDENT ---- ENROLLMENT ---- COURSE

Enrollment has its own data and lifecycle.

It can therefore be treated as a first-class domain concept rather than merely an implementation detail.

5.6 Referential Integrity

Referential integrity means references between tables must remain valid.

For example, an order cannot reference a customer that does not exist.

A foreign key enforces this rule.

FOREIGN KEY (customer_id)
REFERENCES customers(id)

5.7 ON DELETE Behavior

When a referenced row is deleted, what happens to dependent rows?

PostgreSQL supports several common choices.

Option	Meaning	Typical use
CASCADE	Delete dependent rows	Dependent child records
RESTRICT	Prevent deletion while references exist	Protected business records
SET NULL	Set child FK to NULL	Optional association

Example:

FOREIGN KEY (customer_id)
REFERENCES customers(id)
ON DELETE RESTRICT

For an order system, RESTRICT may be preferable because deleting a customer should not accidentally destroy financial history.

For disposable dependent data, CASCADE may be appropriate.

For an optional manager relationship, SET NULL may make sense if the manager is deleted.

Choose behavior based on business rules, not convenience.

5.8 UNIQUE vs PRIMARY KEY

Both enforce uniqueness, but they serve different roles.

PRIMARY KEY	UNIQUE
Main row identifier	Additional uniqueness rule
Only one PK constraint per table	Multiple UNIQUE constraints allowed
Cannot contain NULL	NULL behavior depends on DBMS
Creates a unique index in PostgreSQL	Also enforced through a unique index in PostgreSQL

5.9 NOT NULL vs Total Participation

These are related but not identical concepts.

Total participation is an ER modeling rule.

NOT NULL is a relational constraint.

When a mandatory relationship is represented using a direct FK, NOT NULL usually implements the required participation on that side.

But if the relationship is represented by a separate table, NOT NULL on its FK columns only ensures that each relationship row has references. It does not guarantee that every parent entity has a relationship row.

This distinction matters in advanced schema design.

5.10 Normalization

Normalization organizes relational data to reduce undesirable redundancy and modification anomalies.

For example:

ORDERS(
    order_id,
    customer_name,
    customer_email
)

If the same customer's details are repeated in hundreds of orders, updating the email becomes error-prone.

A normalized design separates the customer:

CUSTOMERS(
    id,
    name,
    email
)

ORDERS(
    id,
    customer_id
)

ER mapping and normalization are related, but they are not identical. Mapping translates the ER constructs into relations; normalization helps refine the resulting relational design.

---

6. Production-Grade Database Design Examples

Let's connect everything to systems you may encounter as a backend developer.

6.1 E-commerce: Customers, Orders, Products and Order Items

Business requirements

• One customer can place multiple orders.
• Each order belongs to one customer.
• An order contains multiple items.
• A product can appear in multiple orders.
• An order item stores quantity and purchase-time price.

Relationship analysis

Relationship	Cardinality	Mapping
Customer → Orders	1:N	FK in orders
Order → Order Items	1:N	FK in order_items
Product → Order Items	1:N	FK in order_items
Order ↔ Products	M:N	order_items

Relational schema

CUSTOMERS(id PK, name, email)

ORDERS(id PK, customer_id FK, status)

PRODUCTS(id PK, name, current_price)

ORDER_ITEMS(
    id PK,
    order_id FK,
    product_id FK,
    quantity,
    unit_price
)

SQL

CREATE TABLE customers (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(255) NOT NULL UNIQUE
);

CREATE TABLE products (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name VARCHAR(150) NOT NULL,
    current_price NUMERIC(12, 2) NOT NULL
        CHECK (current_price >= 0)
);

CREATE TABLE orders (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_id BIGINT NOT NULL REFERENCES customers(id),
    status VARCHAR(30) NOT NULL DEFAULT 'pending',
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE order_items (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    order_id BIGINT NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
    product_id BIGINT NOT NULL REFERENCES products(id) ON DELETE RESTRICT,
    quantity INTEGER NOT NULL CHECK (quantity > 0),
    unit_price NUMERIC(12, 2) NOT NULL CHECK (unit_price >= 0),

    UNIQUE (order_id, product_id)
);

Important engineering decision

Why store unit_price in order_items when products already have current_price?

Because historical order data must preserve the price at the time of purchase.

If the product price changes tomorrow, an old invoice must not change.

This is an example of a business requirement influencing database design beyond simple ER mapping.

The UNIQUE(order_id, product_id) constraint assumes a product appears only once per order. If multiple distinct lines for the same product are allowed, remove that constraint and use a line identifier instead.

6.2 SaaS: Users, Roles and Permissions

Business requirements

• One user can have multiple roles.
• A role can belong to multiple users.
• One role can have multiple permissions.
• A permission can belong to multiple roles.

Schema

USERS(id PK)

ROLES(id PK)

PERMISSIONS(id PK)

USER_ROLES(user_id PK/FK, role_id PK/FK)

ROLE_PERMISSIONS(role_id PK/FK, permission_id PK/FK)

The two M:N relationships require separate associative relations.

CREATE TABLE user_roles (
    user_id BIGINT NOT NULL REFERENCES users(id),
    role_id BIGINT NOT NULL REFERENCES roles(id),

    PRIMARY KEY (user_id, role_id)
);

CREATE TABLE role_permissions (
    role_id BIGINT NOT NULL REFERENCES roles(id),
    permission_id BIGINT NOT NULL REFERENCES permissions(id),

    PRIMARY KEY (role_id, permission_id)
);

Multi-tenant consideration: If roles are company-specific, introduce a company/tenant reference and enforce that users and roles belong to the same tenant. Ordinary foreign keys alone do not guarantee that two referenced rows belong to the same tenant.

6.3 HR: Employees, Departments and Dependents

DEPARTMENTS(id PK, name)

EMPLOYEES(id PK, department_id FK, name)

DEPENDENTS(
    employee_id PK/FK,
    dependent_number PK,
    name
)

Mapping:

• Department → Employees: 1:N.
• Employee → Dependents: 1:N identifying relationship.
• Dependents: weak entity.

If employees can move between departments and HR requires history, use an employee_department_history association table instead of overwriting the current department.

6.4 Education: Students, Courses and Enrollments

STUDENTS(id PK)

COURSES(id PK)

ENROLLMENTS(
    id PK,
    student_id FK,
    course_id FK,
    enrolled_at,
    status,
    UNIQUE(student_id, course_id)
)

The associative entity gives enrollment its own attributes and identity.

6.5 Billing: Invoices and Invoice Items

INVOICES(
    id PK,
    invoice_number UNIQUE,
    issued_at
)

INVOICE_ITEMS(
    invoice_id PK/FK,
    line_number PK,
    description,
    quantity,
    unit_price
)

An invoice item belongs to one invoice and is identified using the invoice's identity and its line number.

For accounting systems, deletion and historical retention rules require special care. Issued invoices are commonly retained rather than physically deleted.

6.6 Contact Management: Users and Phone Numbers

USERS(id PK, name)

USER_PHONES(
    user_id PK/FK,
    phone_number PK,
    phone_type
)

Each phone number becomes an independently queryable row.

---

7. Tools: What Should You Use?

Not every database-related tool serves the same purpose.

Tool	Category	When to use
PostgreSQL	RDBMS	Production relational database
MySQL	RDBMS	Relational applications and production workloads
pgAdmin	Database management GUI	Manage PostgreSQL databases
MySQL Workbench	Database management and modeling	Design and manage MySQL schemas
DBeaver	Database client	Work with multiple database engines
dbdiagram.io	ER/schema design	Create database diagrams from schema definitions
Draw.io	General diagramming	Draw conceptual ER diagrams and architecture
Rails migrations	Schema evolution	Version and apply Rails database changes
Laravel migrations	Schema evolution	Version database structures in Laravel
Django migrations	Schema evolution	Apply model changes through migration files

Important distinction

PostgreSQL and MySQL are actual database management systems.

pgAdmin, MySQL Workbench, and DBeaver are tools for interacting with databases.

dbdiagram.io and Draw.io help design and communicate schemas.

Migrations are a way to manage schema changes over time.

For a Rails developer, the following is a familiar example:

class CreateOrders < ActiveRecord::Migration[7.1]
  def change
    create_table :orders do |t|
      t.references :customer, null: false, foreign_key: true
      t.decimal :total_amount, precision: 12, scale: 2, null: false

      t.timestamps
    end
  end
end

This creates a relational structure using Rails migrations.

---

8. Common Techniques and When They Win

Technique	Best suited for
Direct FK mapping	Simple 1:N relationships
UNIQUE FK	1:1 relationships
Junction table	M:N relationships
Composite PK	Naturally unique combinations
Surrogate PK	Independently referenced association records
Nullable FK	Optional direct relationships
NOT NULL FK	Mandatory direct relationships
Normalized tables	Independent, consistent relational data
JSON/JSONB	Flexible or document-shaped data with suitable access patterns

The important point is that database design is not about blindly selecting a pattern.

It is about choosing the simplest structure that correctly represents the business rules.

---

9. Big-Word Alerts 🚨

These are terms you should recognize immediately in an interview.

Term	Easy meaning	Example
Cardinality	How many can relate?	One customer, many orders
Participation	Is involvement compulsory?	Every order needs a customer
Referential integrity	References must be valid	An order cannot reference a missing customer
Weak entity	Depends on another entity's identity	Invoice item
Identifying relationship	Owner helps identify dependent	Employee identifies dependent
Partial key	Identifies weak entity within its owner	Dependent number
Associative entity	Relationship treated as an entity	Enrollment
Composite PK	Multiple columns identify one row	student_id + course_id
Candidate key	Any minimal unique identifier	Email or user ID
Nullability	Whether a column accepts NULL	Optional department_id

Interview memory trick:

• Cardinality = How many?
• Participation = Must it exist?
• PK = Who am I?
• FK = Who do I reference?
• UNIQUE = Can someone else have the same value?
• NOT NULL = Must I provide a value?

---

10. Asides 💡

A few related engineering details deserve attention.

1. Foreign keys do not automatically create indexes on the referencing columns in PostgreSQL.

If you frequently retrieve orders by customer ID, create an appropriate index.

2. A database FK does not automatically guarantee application-level authorization.

A user having a valid role relationship does not mean every request should be authorized without checking tenant boundaries and permissions.

3. Historical data may need snapshots.

Order item prices, invoice descriptions, and billing addresses may need to preserve the values that existed at transaction time.

4. ORM associations are not a replacement for database constraints.

A Rails belongs_to declaration helps the application understand associations, but the actual database FK and NOT NULL constraints should be designed deliberately.

5. A relationship table can become a domain entity.

For example, enrollment might eventually have payments, attendance, certificates, and completion dates. It is no longer merely a bridge between students and courses.

---

11. Pros, Cons and Trade-offs

11.1 Direct FK vs Separate Relationship Table

Consideration	Direct FK	Separate relation
Simplicity	Usually simpler	More complex
Joins	Fewer	More
Relationship attributes	Limited	Natural fit
Historical records	Requires additional design	Natural fit
Independent lifecycle	Less suitable	More suitable

11.2 Composite PK vs Surrogate PK

Composite PK	Surrogate PK
Represents natural identity	Provides artificial identity
Prevents duplicate combinations	Needs separate UNIQUE constraint
Can make referencing tables more complex	Easier single-column references
May be ideal for simple junctions	Useful for first-class domain entities

Neither is universally better.

11.3 Nullable FK vs Separate Relationship Table

Use a nullable UNIQUE FK when a simple optional 1:1 association is enough.

Use a separate table when the association has independent attributes, lifecycle, history, or needs to be queried and managed independently.

11.4 Normalized Tables vs JSON Storage

Normalized relational tables provide:

• Strong relational integrity.
• Easier joins.
• Individual constraints.
• Consistent updates.

JSON provides:

• Flexible structures.
• Convenient document-shaped storage.
• Useful support for irregular data.

The choice depends on access patterns and integrity requirements, not on which format seems newer.

---

12. Gotchas ⚠️

These are common interview mistakes and real-world design mistakes.

1. Assuming every relationship needs a separate table. A direct FK is sufficient for a conventional 1:N relationship.

2. Putting the FK on the wrong side. For 1:N, it normally belongs on the N-side.

3. Forgetting UNIQUE in 1:1. A foreign key alone does not enforce one-to-one cardinality.

4. Assuming FK means NOT NULL. NULLability must be specified separately.

5. Assuming a primary key can contain NULL. It cannot.

6. Treating M:N as 1:N. A single FK cannot represent multiple references on both sides.

7. Forgetting composite keys. A weak entity or junction table may require a combination of columns for identity.

8. Confusing a weak entity with an ordinary child entity. A child table is not automatically a weak entity. Its identity must actually depend on its owner.

9. Storing multiple independent values in one column. This creates querying and integrity problems.

10. Confusing cardinality and participation. One describes quantity; the other describes mandatory involvement.

11. Assuming UNIQUE always means exactly one. UNIQUE means at most one. It does not guarantee that a matching row exists for every parent.

12. Assuming a relationship's total participation can always be enforced using NOT NULL. This is only straightforward when the relationship is represented directly by a mandatory FK.

---

13. Best Practices ✅

When designing a production database:

• Start with actual business requirements, not table names.
• Identify entities and their independent identities.
• Write cardinality and participation rules explicitly.
• Put conventional 1:N foreign keys on the N-side.
• Use UNIQUE FKs for 1:1 relationships.
• Use junction tables for M:N relationships.
• Preserve relationship attributes in the appropriate relation.
• Use NOT NULL when the business rule requires a value.
• Add CHECK constraints for meaningful domain restrictions.
• Define deletion behavior deliberately.
• Index referencing columns according to query patterns.
• Keep historical transaction data where required.
• Use migrations to track schema evolution.
• Review constraints with the application team before deployment.

A good database design should make invalid business states difficult or impossible to represent.

---

14. Fact Check 🔍

Let's correct some statements that are commonly oversimplified in DBMS tutorials.

Statement	Fact check
Every 1:1 relationship needs table merging.	False. A UNIQUE FK is a valid mapping.
Total participation always means NOT NULL on a particular FK.	False. It depends on how the relationship is represented.
A foreign key enforces uniqueness.	False. UNIQUE is needed for that.
Every M:N relationship must always use a separate table in every possible database representation.	A separate associative relation is the standard relational mapping. Specialized implementations may differ.
Composite PK always performs better.	False. Performance depends on indexes, query patterns, and workload.
Normalization always improves performance.	False. It improves consistency and reduces anomalies, but may require more joins.
A nullable FK means the relationship is one-to-one.	False. UNIQUE is also required.
A weak entity must always use a composite PK physically.	Not necessarily. A surrogate key can be introduced while preserving the identifying business constraint.
A child table is always a weak entity.	False. Dependency of identity is the important distinction.

The biggest lesson: distinguish textbook mapping rules from implementation alternatives.

---

15. Five Whys 🧠

Let's understand why important design decisions exist instead of memorizing them.

Why does M:N require a junction table?

Why 1: Because one entity can relate to multiple instances of another entity.

Why 2: A single FK column can reference only one row at a time.

Why 3: Storing multiple IDs inside a column makes individual values difficult to constrain and query.

Why 4: Relational databases work best when individual relationships are represented as rows with valid keys.

Why 5: This provides reliable joins, uniqueness, referential integrity, and maintainability.

Conclusion: The junction table represents each association as a separate relational record.

Why does a 1:N FK go on the N-side?

Why 1: Each N-side record belongs to one parent.

Why 2: The parent can be referenced repeatedly.

Why 3: The FK does not need to contain multiple parent IDs.

Why 4: The database can validate each child-to-parent reference.

Why 5: This creates a simple and scalable representation without unnecessary relationship tables.

Conclusion: Put the FK on the N-side for a conventional 1:N relationship.

Why is UNIQUE needed in 1:1?

Why 1: A foreign key validates that the referenced record exists.

Why 2: It does not prevent multiple rows from referencing the same record.

Why 3: That would allow a supposedly one-to-one relationship to behave like one-to-many.

Why 4: UNIQUE prevents duplicate non-NULL references.

Why 5: Together, FK and UNIQUE preserve the intended maximum cardinality.

Conclusion: FK validates existence; UNIQUE enforces at-most-one referencing row.

Why does a weak entity use a composite key?

Why 1: The dependent does not have an independent identity in the model.

Why 2: Its owner identifies the context in which it exists.

Why 3: A partial key distinguishes dependents within that owner.

Why 4: Combining the owner key and partial key creates a unique identity.

Why 5: The database can preserve both ownership and identification rules.

Conclusion: Owner PK + Partial Key = Weak Entity PK.

Why does a multivalued attribute need a separate relation?

Why 1: One entity can have multiple independent values.

Why 2: Putting all values into one ordinary column makes individual operations difficult.

Why 3: Separate rows provide independent values.

Why 4: Each value can be queried, indexed, and constrained independently.

Why 5: The structure remains easier to maintain as requirements grow.

Conclusion: Model independently managed values as individual relational records.

---

16. What Experienced Backend Engineers Consider

Knowing mapping rules is important. Applying them safely in a production system is the next level.

16.1 Real business constraints

A customer can have multiple addresses, but perhaps only one default shipping address.

A simple customer-address M:N table is not enough to enforce one default address per customer.

In PostgreSQL:

CREATE UNIQUE INDEX uq_customer_default_address
ON customer_addresses(customer_id)
WHERE is_default = TRUE;

Now the database enforces at most one default address per customer.

16.2 Future relationship changes

Suppose employees currently belong to one department.

Later, the business requires employees to work across multiple departments.

The original 1:N design may need to become M:N.

This is a schema evolution decision, not merely a SQL syntax change.

16.3 Query patterns and indexing

Suppose your application frequently runs:

SELECT *
FROM orders
WHERE customer_id = 101
ORDER BY created_at DESC;

A composite index may be more appropriate:

CREATE INDEX idx_orders_customer_created
ON orders(customer_id, created_at DESC);

Design indexes around actual query patterns.

16.4 Deletion behavior

Deleting a customer should not automatically delete financial records unless the business explicitly allows it.

For financial systems, soft deletion, archival, retention policies, and restricted deletion are common considerations.

16.5 Migration safety

A production migration should consider existing data.

Suppose a nullable FK must become mandatory.

A safe process may involve:

1. Add the column or constraint in a backward-compatible way.
2. Backfill existing rows.
3. Verify that no invalid rows remain.
4. Apply NOT NULL.
5. Deploy code that depends on the new rule.

For large PostgreSQL tables, use staged constraint validation and appropriate locking strategies.

16.6 Concurrency

Imagine two requests simultaneously try to assign the same parking space.

Application-level checks alone can suffer from race conditions.

A database UNIQUE constraint or unique index provides protection even when requests execute concurrently.

16.7 Auditability

For important business relationships, ask:

• Who created this association?
• When was it created?
• Who changed it?
• When was it removed?

Sometimes a relationship needs an audit table or historical records rather than a simple FK.

Senior engineering principle: The correct schema is not merely one that stores today's data. It must preserve the business rules under concurrent requests, changing requirements, and production failures.

---

17. Interview Corner 🎯

A. Ten Conceptual Questions

Q1. What is ER-to-relational mapping?

It is the process of converting entities, attributes, and relationships into relational tables, keys, and constraints.

Q2. What is the difference between cardinality and participation?

Cardinality describes how many records can be associated. Participation determines whether association is mandatory or optional.

Q3. Where is the FK placed in a 1:N relationship?

Normally on the N-side.

Q4. Why does a 1:1 relationship need UNIQUE?

Because an FK alone permits multiple referencing rows.

Q5. What is an associative entity?

An entity representing a relationship, often used when the relationship has attributes or an independent lifecycle.

Q6. What is a weak entity?

An entity whose identity depends on another owner entity.

Q7. What is a partial key?

An attribute that identifies a weak entity only within its owner's identity.

Q8. What is a composite primary key?

A primary key consisting of multiple columns.

Q9. Does an FK automatically enforce NOT NULL?

No. NULLability is a separate constraint.

Q10. Why is normalization important?

It reduces unnecessary redundancy and prevents insertion, update, and deletion anomalies.

B. Five Scenario-Based Questions

Q1. A department can have many employees. An employee can belong to only one department. Where does the FK go?

In employees.

Q2. An employee may have one parking space, and a parking space may belong to one employee. Both are optional. What mapping can you use?

A nullable UNIQUE FK or a separate assignment table.

Q3. A student can enroll in many courses, and courses contain many students. What is the mapping?

An enrollment junction table.

Q4. An invoice line cannot exist without an invoice. What is the relationship?

A weak-entity-style identifying relationship if its identity depends on the invoice and a partial key such as line number.

Q5. A user can have several phone numbers. What should you do?

Create a separate user_phones relation.

C. Five SQL / Schema Design Questions

Q1. How do you enforce one-to-one using SQL?

Use an FK with UNIQUE.

Q2. How do you prevent orders from referencing nonexistent customers?

Use a FOREIGN KEY constraint.

Q3. How do you prevent duplicate student-course enrollments?

Use a composite PK or UNIQUE(student_id, course_id).

Q4. How do you ensure every order has a customer?

Use customer_id NOT NULL together with a foreign key.

Q5. How do you delete dependent records automatically?

Use ON DELETE CASCADE when that behavior is valid for the business.

D. Follow-up Questions an Interviewer May Ask

After your initial answer, an interviewer may ask:

1. What happens if the FK is nullable?
2. What if the relationship has attributes?
3. What if the business requires history?
4. Can a UNIQUE column contain multiple NULL values?
5. What happens when a referenced row is deleted?
6. Why would you choose a surrogate PK?
7. How would you enforce only one active assignment?
8. What indexes would you create?
9. How would you migrate an existing table safely?
10. Can the database enforce total participation on both sides?

The last question is particularly interesting: a simple FK generally enforces participation from the referencing side, not that every referenced parent must have at least one child. Enforcing such a rule may require a different modeling strategy, deferred constraint triggers, or application-level transactional logic.

---

18. Final Mental Model: Revise Every Mapping Case

This is the decision framework to remember during an interview.

START: Given an ER Diagram
          |
          v
   Identify Entities
          |
          v
   Identify Relationships
          |
          v
   Check Cardinality
          |
          +--------------------------+
          |                          |
          v                          v
         1:1                       1:N / N:1
          |                          |
          v                          v
    UNIQUE FK                 FK on N-side
    (or merge)                (normally)
          |
          |
          +--------------------------+
          |
          v
         M:N
          |
          v
    Junction Table
          |
          v
  Composite PK / Surrogate PK
          |
          v
 Check Weak Entities
          |
          v
 Owner PK + Partial Key
          |
          v
 Check Multivalued Attributes
          |
          v
 Separate Relation
          |
          v
 Apply Constraints
          |
          v
 Final Relational Schema

Last-minute revision table

ER construct	Relational mapping
Strong entity	Table with primary key
Simple attribute	Column
Composite attribute	Multiple columns
Derived attribute	Usually calculated
1:1	UNIQUE FK
1:N	FK on N-side
M:N	Junction table
Weak entity	Owner PK + partial key
Multivalued attribute	Separate relation
Relationship attributes	FK-based or associative relation, depending on cardinality and requirements

---

19. Test Me 🧪

Now it's your turn.

Do not just memorize the answers. Try to explain WHY your design is correct.

Question 1 — MCQ (Easy)

A customer can place multiple orders. Every order belongs to one customer.

Where should the foreign key normally go?

A. customers table

B. orders table

C. Both tables

D. A separate relationship table

Question 2 — MCQ (Easy)

Which combination enforces a conventional 1:1 relationship using a direct FK?

A. FK only

B. UNIQUE only

C. FK + UNIQUE

D. NOT NULL only

Question 3 — Cardinality (Easy)

A user can have multiple roles, and each role can be assigned to multiple users.

Identify the cardinality.

Question 4 — Participation (Medium)

Every order must belong to a customer, but a customer can exist without orders.

Identify the participation of:

• Customer
• Order

Question 5 — FK Placement (Medium)

An employee may have no department, but if assigned, belongs to exactly one department.

Where should the FK go? Should it be nullable?

Question 6 — Schema Design (Medium)

Design a relational schema for:

• Authors
• Books

An author can write multiple books, and a book can have multiple authors.

Include primary keys and foreign keys.

Question 7 — Identify the Mistake (Medium)

A developer creates:

CREATE TABLE employee_parking (
    id BIGINT PRIMARY KEY,
    employee_id BIGINT REFERENCES employees(id),
    parking_space_id BIGINT REFERENCES parking_spaces(id)
);

The business requires:

• One employee can have at most one parking space.
• One parking space can belong to at most one employee.

What constraints are missing?

Question 8 — Weak Entity (Hard)

An invoice has multiple line items.

A line item is identified by its invoice ID and line number.

Explain:

• Why the line item is weak.
• What the partial key is.
• What the composite primary key should be.

Question 9 — Production Design (Hard)

Your SaaS application has:

• Users.
• Roles.
• Permissions.

Users can have many roles. Roles can have many permissions.

Design the tables and explain how duplicate assignments are prevented.

Question 10 — Senior-Level Thinking (Hard)

A student can enroll in a course multiple times across different academic years.

Why is this constraint insufficient?

PRIMARY KEY (student_id, course_id)

Design a better key or uniqueness rule.

---

Answer Key

Try all ten questions before opening this section.

Click to reveal answers

1. B — orders table. It is the N-side.

2. C — FK + UNIQUE. NOT NULL may additionally be required for mandatory participation.

3. M:N.

4. Customer: Partial. Order: Total.

5. FK in employees, nullable if department assignment is optional.

6.

CREATE TABLE authors (
    id BIGINT PRIMARY KEY
);

CREATE TABLE books (
    id BIGINT PRIMARY KEY
);

CREATE TABLE author_books (
    author_id BIGINT REFERENCES authors(id),
    book_id BIGINT REFERENCES books(id),
    PRIMARY KEY (author_id, book_id)
);

7. Add UNIQUE(employee_id) and UNIQUE(parking_space_id), and NOT NULL on both FKs if every assignment row must contain both references.

8. The line item depends on the invoice for identification. Partial key: line_number. Composite PK: (invoice_id, line_number).

9.

CREATE TABLE user_roles (
    user_id BIGINT REFERENCES users(id),
    role_id BIGINT REFERENCES roles(id),
    PRIMARY KEY (user_id, role_id)
);

CREATE TABLE role_permissions (
    role_id BIGINT REFERENCES roles(id),
    permission_id BIGINT REFERENCES permissions(id),
    PRIMARY KEY (role_id, permission_id)
);

The composite PKs prevent duplicate assignments.

10. The same student-course combination must be allowed in different academic years. Use:

PRIMARY KEY (student_id, course_id, academic_year)

Alternatively, use a surrogate enrollment ID with:

UNIQUE (student_id, course_id, academic_year)

The correct design depends on whether multiple attempts within the same year are permitted.

---

20. Final Summary: What Have We Learned?

We started with a simple business problem: customers place orders, orders contain products, and students enroll in courses.

We then learned how to translate these business requirements into actual relational database structures.

Here is the complete journey:

1. ER modeling helps us identify entities, attributes, relationships, cardinality, and participation.
2. Relational mapping converts those concepts into tables, columns, keys, and constraints.
3. 1:1 relationships can use UNIQUE foreign keys.
4. 1:N relationships normally place the foreign key on the N-side.
5. M:N relationships use associative or junction tables.
6. Weak entities depend on their owner's identity.
7. Multivalued attributes are represented using separate relations.
8. Database constraints preserve business rules and protect data integrity.
9. Production database design also considers query patterns, indexing, concurrency, history, and future requirements.

The most important thing to remember:

Database design is not about deciding where to put foreign keys by memorizing rules. It is about translating business relationships into structures that preserve their meaning and enforce their constraints.

When you see an ER diagram in an interview, don't immediately start creating tables.

First ask:

• What entities exist?
• How are they related?
• What is the cardinality?
• Is participation optional or mandatory?
• Can a direct FK represent the relationship?
• Does the relationship need its own table?
• Which constraints preserve the business rules?

Once you can answer these questions, converting an ER diagram into a relational schema becomes a logical engineering process rather than guesswork.

That is the difference between someone who knows SQL and someone who understands database design.