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

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:

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:

PASSPORT:

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:

ORDERS:

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

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:

COURSES:

ENROLLMENTS:

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:

DEPENDENTS:

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...