Introduction

Database design begins with understanding the information an application needs to store. Before creating tables or writing SQL, the designer should identify the important business objects, their properties, and the relationships connecting them.

The Entity-Relationship model, commonly called the ER model, provides a conceptual method for representing this information.

The ER model describes:

• Entities about which information is stored
• Attributes that describe those entities
• Keys that uniquely identify entity instances
• Relationships connecting entities
• Cardinality rules for those relationships
• Participation and optionality constraints
• Business rules that the data model must preserve

In your System Design curriculum, Entities and Relationships is Topic 5.1 and begins the Relational Data Modeling & SQL module. It is followed by normalization and denormalization, keys and constraints, ACID, transactions, isolation, indexes, query plans, and connection pools.

Prerequisites

What Is an Entity?

An entity is a distinguishable object, event, place, person, concept, or transaction about which the system needs to store information.

Examples include:

• Customer
• Product
• Order
• Invoice
• Payment
• Employee
• Department
• Course
• Reservation
• Shipment

Entity Type vs Entity Instance

Entity type:

Customer


Entity instances:

Customer CUS-42
Customer CUS-43
Customer CUS-44


Entity set:

All customer instances

What Is an Attribute?

An attribute is a property or characteristic that describes an entity or relationship.

Customer Attributes

Customer

- Customer ID
- Display name
- Email address
- Phone number
- Status
- Created time

Product Attributes

Product

- Product ID
- Product name
- Description
- Unit price
- Status
- Category

Every attribute should have a clear definition, data type, allowed format, nullability rule, and business meaning.

Types of Attributes

Composite Attributes

A composite attribute contains several components that have independent meaning.

Address

- Address line 1
- Address line 2
- City
- State or region
- Postal code
- Country code

Storing these components separately supports validation, filtering, sorting, searching, and country-specific formatting.

shipping_address VARCHAR(1000)

address_line_1 VARCHAR(200),
address_line_2 VARCHAR(200),
city VARCHAR(100),
region VARCHAR(100),
postal_code VARCHAR(30),
country_code CHAR(2)

Multi-valued Attributes

A multi-valued attribute can contain several values for one entity.

Customer CUS-42

Telephone numbers:

- +91-0000000001
- +91-0000000002
- +91-0000000003

In a relational database, repeated values should normally be modeled using a related table instead of placing several values in one column.

phone_numbers VARCHAR(1000)

Stored value:

+91-0000000001,+91-0000000002,+91-0000000003

CREATE TABLE customer_phone_numbers
(
    phone_number_id BIGINT PRIMARY KEY,
    customer_id BIGINT NOT NULL,
    phone_number VARCHAR(30) NOT NULL,
    phone_type VARCHAR(20) NOT NULL,
    is_primary BOOLEAN NOT NULL DEFAULT FALSE
);

Derived Attributes

A derived attribute is calculated from other data.

Order total
=
Sum of line amounts
+
Tax
+
Shipping
-
Discounts

A derived value can be calculated when queried or stored when performance, legal, historical, or audit requirements justify materializing it.

Keys and Entity Identity

A key identifies an entity instance and supports references between related entities.

Primary and Alternate Keys

CREATE TABLE customers
(
    customer_id BIGINT PRIMARY KEY,
    customer_number VARCHAR(30) NOT NULL,
    email_address VARCHAR(254) NOT NULL,
    display_name VARCHAR(200) NOT NULL,

    CONSTRAINT uq_customers_customer_number
        UNIQUE (customer_number),

    CONSTRAINT uq_customers_email
        UNIQUE (email_address)
);

The system-generated customer_id is the primary key, while the customer number and email address can be alternate candidate keys when the business rules guarantee uniqueness.

What Is a Relationship?

A relationship represents an association between entity instances.

Customer places Order.

Order contains Order Line.

Order Line references Product.

Order has Payment.

Order produces Shipment.

A relationship type defines the general association, while a relationship instance represents one actual association.

Degree of a Relationship

The degree describes the number of participating entity types.

Cardinality

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

The principal cardinality patterns are:

• One-to-one
• One-to-many
• Many-to-one
• Many-to-many

One-to-one Relationship

In a one-to-one relationship, one instance of entity A can be related to at most one instance of entity B, and one instance of entity B can be related to at most one instance of entity A.

User Account 1 -------- 1 User Profile

SQL Implementation

CREATE TABLE user_accounts
(
    user_id BIGINT PRIMARY KEY,
    username VARCHAR(100) NOT NULL UNIQUE
);

CREATE TABLE user_profiles
(
    user_id BIGINT PRIMARY KEY,
    display_name VARCHAR(200) NOT NULL,
    biography VARCHAR(1000) NULL,

    CONSTRAINT fk_user_profiles_user
        FOREIGN KEY (user_id)
        REFERENCES user_accounts (user_id)
);

Using the parent key as both the primary key and foreign key in user_profiles ensures that each account can have at most one profile.

One-to-many Relationship

In a one-to-many relationship, one parent entity can be associated with several child entities, while each child belongs to one parent.

Customer 1 -------- N Orders

SQL Implementation

CREATE TABLE customers
(
    customer_id BIGINT PRIMARY KEY,
    customer_number VARCHAR(30) NOT NULL UNIQUE,
    display_name VARCHAR(200) NOT NULL
);

CREATE TABLE orders
(
    order_id BIGINT PRIMARY KEY,
    order_number VARCHAR(30) NOT NULL UNIQUE,
    customer_id BIGINT NOT NULL,
    order_status VARCHAR(30) NOT NULL,
    created_at TIMESTAMP NOT NULL,

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

The foreign key appears on the many side. Several order rows can reference the same customer row.

Many-to-many Relationship

In a many-to-many relationship, several instances of entity A can relate to several instances of entity B.

Students N -------- N Courses

A relational database normally resolves this relationship through an associative or junction table.

Junction-table Implementation

CREATE TABLE students
(
    student_id BIGINT PRIMARY KEY,
    student_number VARCHAR(30) NOT NULL UNIQUE,
    student_name VARCHAR(200) NOT NULL
);

CREATE TABLE courses
(
    course_id BIGINT PRIMARY KEY,
    course_code VARCHAR(30) NOT NULL UNIQUE,
    course_title VARCHAR(200) NOT NULL
);

CREATE TABLE enrollments
(
    student_id BIGINT NOT NULL,
    course_id BIGINT NOT NULL,
    enrolled_at TIMESTAMP NOT NULL,
    enrollment_status VARCHAR(30) NOT NULL,
    final_grade VARCHAR(10) NULL,

    PRIMARY KEY
    (
        student_id,
        course_id
    ),

    CONSTRAINT fk_enrollments_student
        FOREIGN KEY (student_id)
        REFERENCES students (student_id),

    CONSTRAINT fk_enrollments_course
        FOREIGN KEY (course_id)
        REFERENCES courses (course_id)
);

The enrollments table does more than connect students and courses. It also stores attributes belonging to the relationship, such as enrollment time, status, and final grade.

Associative Entities

An associative entity represents a relationship that has become important enough to be modeled as an entity in its own right.

Project Membership Example

User N -------- N Project

Resolved through:

Project Membership


Project Membership attributes:

- Membership ID
- User ID
- Project ID
- Role
- Joined time
- Status

CREATE TABLE project_memberships
(
    membership_id BIGINT PRIMARY KEY,
    project_id BIGINT NOT NULL,
    user_id BIGINT NOT NULL,
    membership_role VARCHAR(50) NOT NULL,
    membership_status VARCHAR(30) NOT NULL,
    joined_at TIMESTAMP NOT NULL,

    CONSTRAINT uq_project_memberships
        UNIQUE
        (
            project_id,
            user_id
        ),

    CONSTRAINT fk_memberships_project
        FOREIGN KEY (project_id)
        REFERENCES projects (project_id),

    CONSTRAINT fk_memberships_user
        FOREIGN KEY (user_id)
        REFERENCES users (user_id)
);

Recursive Relationships

A recursive relationship connects instances of one entity type to other instances of the same type.

Employee Reporting Structure

Employee
   |
   | reports to
   v
Employee

CREATE TABLE employees
(
    employee_id BIGINT PRIMARY KEY,
    employee_number VARCHAR(30) NOT NULL UNIQUE,
    employee_name VARCHAR(200) NOT NULL,
    manager_employee_id BIGINT NULL,

    CONSTRAINT fk_employees_manager
        FOREIGN KEY (manager_employee_id)
        REFERENCES employees (employee_id)
);

A top-level employee can have a null manager reference, while other employees can reference another employee as manager.

Ternary Relationships

A ternary relationship connects three entity types in one association.

Supplier supplies Product to Warehouse

The meaning can depend on all three participants. Replacing it with several independent binary relationships can lose information.

CREATE TABLE supplier_product_warehouse
(
    supplier_id BIGINT NOT NULL,
    product_id BIGINT NOT NULL,
    warehouse_id BIGINT NOT NULL,
    supplier_price DECIMAL(18, 2) NOT NULL,
    lead_time_days INT NOT NULL,

    PRIMARY KEY
    (
        supplier_id,
        product_id,
        warehouse_id
    ),

    FOREIGN KEY (supplier_id)
        REFERENCES suppliers (supplier_id),

    FOREIGN KEY (product_id)
        REFERENCES products (product_id),

    FOREIGN KEY (warehouse_id)
        REFERENCES warehouses (warehouse_id)
);

Participation Constraints

Participation describes whether an entity is required to participate in a relationship.

Mandatory Participation

customer_id BIGINT NOT NULL

Optional Participation

manager_employee_id BIGINT NULL

Minimum and Maximum Cardinality

Relationship constraints can be expressed using minimum and maximum participation.

Order Example

Customer to Orders:

Customer:
0..* Orders

Order:
1..1 Customer


Order to Order Lines:

Order:
1..* Order Lines

Order Line:
1..1 Order

Some minimum-cardinality rules, such as requiring at least one order line, might need transaction-level application logic or a deferred database constraint rather than a simple foreign key.

Strong Entities

A strong entity can be uniquely identified using its own key and does not depend on another entity for identity.

Strong entities:

- Customer
- Product
- Employee
- Course
- Warehouse

CREATE TABLE products
(
    product_id BIGINT PRIMARY KEY,
    product_code VARCHAR(30) NOT NULL UNIQUE,
    product_name VARCHAR(200) NOT NULL
);

Weak Entities

A weak entity depends on another entity for identification or existence.

Order Line Example

Order ORD-9001

Line 1
Line 2
Line 3


Line number 1 is unique only
within order ORD-9001.

CREATE TABLE order_lines
(
    order_id BIGINT NOT NULL,
    line_number INT NOT NULL,
    product_id BIGINT NOT NULL,
    quantity INT NOT NULL,
    unit_price DECIMAL(18, 2) NOT NULL,

    PRIMARY KEY
    (
        order_id,
        line_number
    ),

    CONSTRAINT fk_order_lines_order
        FOREIGN KEY (order_id)
        REFERENCES orders (order_id),

    CONSTRAINT fk_order_lines_product
        FOREIGN KEY (product_id)
        REFERENCES products (product_id)
);

The complete identity of an order line contains the parent order key and the line's partial key.

Supertype and Subtype Entities

A supertype stores attributes shared by several related entity subtypes.

Payment
   |
   +-- Card Payment
   |
   +-- Bank Transfer
   |
   +-- Wallet Payment

Shared payment attributes can include:

• Payment ID
• Amount
• Currency
• Status
• Created time

Subtype-specific attributes can include a card-provider reference, bank transfer reference, or wallet provider.

Table-per-subtype Example

CREATE TABLE payments
(
    payment_id BIGINT PRIMARY KEY,
    payment_type VARCHAR(30) NOT NULL,
    amount DECIMAL(18, 2) NOT NULL,
    currency CHAR(3) NOT NULL,
    payment_status VARCHAR(30) NOT NULL
);

CREATE TABLE card_payments
(
    payment_id BIGINT PRIMARY KEY,
    provider_reference VARCHAR(100) NOT NULL,
    card_brand VARCHAR(30) NOT NULL,

    CONSTRAINT fk_card_payments_payment
        FOREIGN KEY (payment_id)
        REFERENCES payments (payment_id)
);

CREATE TABLE bank_transfers
(
    payment_id BIGINT PRIMARY KEY,
    transfer_reference VARCHAR(100) NOT NULL,

    CONSTRAINT fk_bank_transfers_payment
        FOREIGN KEY (payment_id)
        REFERENCES payments (payment_id)
);

Entity-Relationship Diagrams

An Entity-Relationship Diagram, commonly abbreviated as ERD, visually represents entities, attributes, keys, and relationships.

Common diagram elements include:

• Entity boxes
• Attribute lists
• Primary-key indicators
• Foreign-key indicators
• Relationship lines
• Cardinality markers
• Optionality markers

Textual ER Diagram

CUSTOMER
--------
PK customer_id
   display_name
   email_address

       1
       |
       | places
       |
       N

ORDER
-----
PK order_id
FK customer_id
   order_status
   created_at

       1
       |
       | contains
       |
       N

ORDER_LINE
----------
PK, FK order_id
PK     line_number
FK     product_id
       quantity
       unit_price

       N
       |
       | references
       |
       1

PRODUCT
-------
PK product_id
   product_name
   current_price

Conceptual, Logical, and Physical Models

Discover Entities from Requirements

Consider the following business requirements:

A customer can place several orders.

Every order contains one or more order lines.

Each order line references one product.

An order can have several payments.

A payment belongs to one order.

An order can create several shipments.

Candidate Entities

• Customer
• Order
• Order Line
• Product
• Payment
• Shipment

Candidate Relationships

• Customer places Order
• Order contains Order Line
• Order Line references Product
• Order has Payment
• Order creates Shipment

Naming Entities and Relationships

Use clear and consistent domain terminology.

Recommended Entity Names

Customer
Order
Order Line
Product
Payment
Shipment

Recommended Relationship Names

Customer places Order.

Order contains Order Line.

Order Line references Product.

Order receives Payment.

Order produces Shipment.

Avoid technical names that expose temporary implementation details when a stable domain term is available.

Translating Entities into Tables

Complete Order-model Example

CREATE TABLE customers
(
    customer_id BIGINT PRIMARY KEY,
    customer_number VARCHAR(30) NOT NULL UNIQUE,
    display_name VARCHAR(200) NOT NULL,
    email_address VARCHAR(254) NOT NULL,
    customer_status VARCHAR(30) NOT NULL,
    created_at TIMESTAMP NOT NULL
);

CREATE TABLE products
(
    product_id BIGINT PRIMARY KEY,
    product_code VARCHAR(30) NOT NULL UNIQUE,
    product_name VARCHAR(200) NOT NULL,
    current_price DECIMAL(18, 2) NOT NULL,
    product_status VARCHAR(30) NOT NULL
);

CREATE TABLE orders
(
    order_id BIGINT PRIMARY KEY,
    order_number VARCHAR(30) NOT NULL UNIQUE,
    customer_id BIGINT NOT NULL,
    order_status VARCHAR(30) NOT NULL,
    ordered_at TIMESTAMP NOT NULL,
    total_amount DECIMAL(18, 2) NOT NULL,
    currency CHAR(3) NOT NULL,

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

CREATE TABLE order_lines
(
    order_id BIGINT NOT NULL,
    line_number INT NOT NULL,
    product_id BIGINT NOT NULL,
    product_description VARCHAR(200) NOT NULL,
    quantity INT NOT NULL,
    unit_price DECIMAL(18, 2) NOT NULL,

    PRIMARY KEY
    (
        order_id,
        line_number
    ),

    CONSTRAINT fk_order_lines_order
        FOREIGN KEY (order_id)
        REFERENCES orders (order_id),

    CONSTRAINT fk_order_lines_product
        FOREIGN KEY (product_id)
        REFERENCES products (product_id)
);

CREATE TABLE payments
(
    payment_id BIGINT PRIMARY KEY,
    order_id BIGINT NOT NULL,
    payment_status VARCHAR(30) NOT NULL,
    payment_amount DECIMAL(18, 2) NOT NULL,
    currency CHAR(3) NOT NULL,
    created_at TIMESTAMP NOT NULL,

    CONSTRAINT fk_payments_order
        FOREIGN KEY (order_id)
        REFERENCES orders (order_id)
);

CREATE TABLE shipments
(
    shipment_id BIGINT PRIMARY KEY,
    order_id BIGINT NOT NULL,
    shipment_status VARCHAR(30) NOT NULL,
    tracking_number VARCHAR(100) NULL,
    dispatched_at TIMESTAMP NULL,
    delivered_at TIMESTAMP NULL,

    CONSTRAINT fk_shipments_order
        FOREIGN KEY (order_id)
        REFERENCES orders (order_id)
);

Historical Snapshot Attributes

A relationship can require transaction-time attributes instead of always reading current values from a related entity.

Product current price:

USD 90.00


Order placed earlier:

Unit price paid:
USD 75.00


Requirement:

The historical order must continue to show
the transaction-time price.

Therefore, the order line stores a product reference and a snapshot of the description and price used for that transaction.

Query Related Entities

Customers and Orders

SELECT
    c.customer_number,
    c.display_name,
    o.order_number,
    o.order_status,
    o.ordered_at
FROM customers AS c
INNER JOIN orders AS o
    ON o.customer_id = c.customer_id
ORDER BY
    c.customer_number,
    o.ordered_at DESC;

Order Details

SELECT
    o.order_number,
    ol.line_number,
    ol.product_description,
    ol.quantity,
    ol.unit_price,
    ol.quantity * ol.unit_price
        AS line_amount
FROM orders AS o
INNER JOIN order_lines AS ol
    ON ol.order_id = o.order_id
WHERE
    o.order_number = :order_number
ORDER BY
    ol.line_number;

Customers without Orders

SELECT
    c.customer_number,
    c.display_name
FROM customers AS c
LEFT JOIN orders AS o
    ON o.customer_id = c.customer_id
WHERE
    o.order_id IS NULL;

Referential Integrity

Referential integrity ensures that foreign-key values refer to valid parent rows or are null when the relationship is optional.

Invalid relationship:

Order customer_id = 999

No customer 999 exists.


Foreign-key result:

Database rejects the order insert
or update.

Foreign keys help prevent:

• Orders referring to nonexistent customers
• Order lines referring to nonexistent orders
• Payments referring to nonexistent orders
• Memberships referring to nonexistent users or projects
• Orphaned child records

Delete and Update Actions

A foreign-key relationship needs an explicit policy for parent deletion and key changes.

Relationship Constraints

Foreign keys alone do not express every business rule.

Additional relationship rules can include:

• An order must contain at least one line
• One address must be the customer's primary address
• A user cannot join the same project twice
• A shipment cannot belong to a cancelled order
• A manager cannot report directly or indirectly to the same employee
• A payment currency must match the order currency
• One active profile can exist for each user

These rules can require unique constraints, check constraints, transactions, deferred validation, triggers, or application services.

Relationship Lifecycle

Relationships can change over time and can require history.

Employment Assignment

Employee assigned to Department A:

Start: 2025-01-01
End:   2026-03-31


Employee assigned to Department B:

Start: 2026-04-01
End:   null

CREATE TABLE employee_department_assignments
(
    assignment_id BIGINT PRIMARY KEY,
    employee_id BIGINT NOT NULL,
    department_id BIGINT NOT NULL,
    valid_from DATE NOT NULL,
    valid_to DATE NULL,

    FOREIGN KEY (employee_id)
        REFERENCES employees (employee_id),

    FOREIGN KEY (department_id)
        REFERENCES departments (department_id),

    CONSTRAINT ck_assignment_dates
        CHECK
        (
            valid_to IS NULL
            OR valid_to >= valid_from
        )
);

Replacing the current department foreign key on the employee row would lose historical assignment information.

Temporal Relationships

A temporal relationship records when an association is valid.

Examples include:

• Employee department assignments
• Product category memberships
• Customer subscription plans
• Contract assignments
• Resource ownership
• Price agreements

The model should define whether overlapping validity periods are allowed and how the current relationship is determined.

Avoid Storing Sensitive Relationships Unnecessarily

A relationship can reveal sensitive business or personal information even when its individual entity attributes appear harmless.

Apply:

• Data minimization
• Purpose limitation
• Access control
• Retention policies
• Audit logging
• Encryption where required

Do not expose relationship tables directly through an API without authorization and a clear client-facing contract.

Entity-discovery Workflow

1. Gather business use cases and data requirements.
2. Identify important nouns as candidate entities.
3. Identify verbs as candidate relationships.
4. Define one stable identifier for each entity.
5. List attributes and their business meanings.
6. Define relationship cardinality.
7. Define mandatory and optional participation.
8. Resolve many-to-many relationships with associative entities.
9. Identify weak entities and ownership dependencies.
10. Identify historical and temporal relationships.
11. Validate the model using realistic business scenarios.
12. Translate the logical model into a physical relational schema.

Modeling Review Questions

• Can every entity instance be uniquely identified?
• Does every attribute describe the correct entity?
• Are multi-valued attributes modeled separately?
• Are cardinalities based on real business rules?
• Is optionality represented correctly?
• Are many-to-many relationships resolved?
• Do relationship attributes belong in an associative entity?
• Are historical snapshots preserved where necessary?
• Are deletion rules safe?
• Can foreign keys prevent invalid references?
• Are sensitive relationships protected?
• Can the model support the required queries?

Common Entity and Relationship Mistakes

Recommended Test Cases

Entities and Relationships Best Practices

Practice Exercise

Design an entity-relationship model for an online course platform.

Requirements

1. A learner can enroll in several courses.
2. A course can contain several chapters.
3. A chapter can contain several lessons.
4. A course can have several instructors.
5. An instructor can teach several courses.
6. An enrollment stores enrollment time, status, and completion percentage.
7. A learner can attempt several quizzes.
8. Each quiz attempt stores a score and submission time.
9. A lesson can have several documents and videos.
10. Each course has one category.
11. A category can contain several courses.
12. Historical quiz attempts must remain available.

Candidate Entity Inventory

Relationship Template

Learner N -------- N Course
Resolved through Enrollment

Course 1 -------- N Chapter

Chapter 1 -------- N Lesson

Instructor N -------- N Course
Resolved through Course Instructor

Learner 1 -------- N Quiz Attempt

Quiz 1 -------- N Quiz Attempt

Category 1 -------- N Course

Frequently Asked Questions