Entity-Relationship Diagrams: Designing Databases Visually

Introduction

An Entity-Relationship (ER) diagram is a visual blueprint for a database. Before writing a single line of SQL, experienced database designers first create an ER diagram that maps out every entity (thing), attribute (property), and relationship (connection) in the system. ER diagrams are the universal language of Database Management Systems design and a critical skill for any data architect or backend developer.

Explanation

An ER diagram has three fundamental building blocks. An entity represents a real-world object or concept about which data is stored — for example, Student, Product, or Order. An attribute is a property of an entity — a Student entity has attributes like student_id, name, and email. A relationship describes how two entities are associated — a Student "enrolls in" a Course.

Entities are drawn as rectangles, attributes as ovals, and relationships as diamonds in the traditional Chen notation. In crow's foot notation (used by most modern tools), entities are rectangles with attribute columns, and relationship lines use crow's foot symbols to show cardinality.

Real World Example

An e-commerce platform has Customers, Orders, Products, and Categories as entities. A Customer places Orders (one customer can place many orders — one-to-many). An Order contains Products (one order can have many products, one product can be in many orders — many-to-many, resolved through an OrderItems junction table). A Product belongs to a Category (many-to-one). Mapping this on an ER diagram before coding prevents missed relationships and incorrect foreign key placements.

Technical Breakdown

Cardinality defines how many instances of one entity relate to another:

• One-to-One (1:1): One entity instance relates to exactly one of another. Example: each employee has exactly one employee profile.

• One-to-Many (1:N): One entity instance relates to many of another. Example: one customer places many orders.

• Many-to-Many (M:N): Many instances relate to many of another. Example: students enroll in many courses, each course has many students. Always resolved by a junction/associative table in SQL.

Attributes can be simple (atomic, single-valued), composite (made of sub-parts — e.g., full_name composed of first and last), multi-valued (e.g., phone_numbers), or derived (calculated — e.g., age derived from birth_date).

[ ER Diagram for E-commerce]

Prompt: Clean minimal ER diagram showing Customer, Order, Product, and Category entities as labeled rectangles. Lines between entities show cardinality: Customer—Order (one-to-many crow's foot), Order—Product (many-to-many through OrderItems junction table shown as a rectangle), Product—Category (many-to-one). White background, blue entity rectangles, simple sans-serif font, educational style.

Example

-- Translating ER diagram to SQL:
-- Customer entity
CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    name        VARCHAR(100) NOT NULL,
    email       VARCHAR(150) UNIQUE NOT NULL
);

-- Many-to-many resolved through junction table
CREATE TABLE order_items (
    order_id   INT REFERENCES orders(order_id),
    product_id INT REFERENCES products(product_id),
    quantity   INT NOT NULL,
    unit_price DECIMAL(10,2),
    PRIMARY KEY (order_id, product_id)
);

Common Mistakes

• Forgetting to resolve many-to-many relationships — a direct M:N line in an ER diagram cannot be directly implemented in SQL and must be decomposed into two one-to-many relationships through a junction table.

• Treating derived attributes as stored columns — age can be calculated from date_of_birth and does not need its own column (it becomes stale the next day).

• Confusing entities with attributes — if you need to store multiple properties about something, it should be an entity, not an attribute of another entity.

Interview Tips

ER diagram questions are extremely common in system design interviews. Practice designing systems like a hotel booking platform, a ride-sharing app, or a social media feed. Interviewers look for: correct identification of entities vs attributes, proper cardinality notation, and the ability to resolve many-to-many relationships into junction tables. Always start by listing entities, then attributes, then relationships — this systematic approach demonstrates mature design thinking.