Entity-Relationship Modeling

Queries are easy to fix. Schemas are not. A badly shaped table is copied into reports, services and exports, and every one of them has to be migrated when you finally change it.

So the few hours you spend designing before you create anything are the cheapest hours in the project. Entity-relationship (ER) modeling is the method for spending them well.

The method#

You can do it on paper in a repeatable order.

1. Collect the questions the system must answer. "Which orders did this customer place?" "What did we sell last month?" The schema exists to answer these.
2. List the nouns. Customer, order, product, payment. Each candidate noun is an entity.
3. List the facts about each noun. These become attributes (columns).
4. Find the relationships between entities and decide how many of each side can take part.
5. Choose a key for every entity.
6. Walk the questions from step 1 through the draft. If one cannot be answered, the model is missing something.

Entities and attributes#

An entity is something you keep facts about, and it becomes a table. Each distinct thing is one row.

An attribute describes one entity with a single, indivisible value. full_name, email, unit_price. If you catch yourself wanting a column that holds a list or several values, you are looking at a missing entity.

A fast test for whether something is an entity or an attribute: do you need to store facts about it? "Category" starts as an attribute of a product (the category text column in the course schema). The day someone wants to store a category description, a display order or a manager, it has become an entity of its own.

Relationships and cardinality#

A relationship connects entities, and its cardinality says how many on each side. Say it as two sentences, one for each direction.

• One customer places many orders. One order belongs to exactly one customer. (one-to-many)
• One order contains many products. One product appears in many orders. (many-to-many)
• One employee has one badge. One badge belongs to one employee. (one-to-one)

Also decide optionality. Must an order have a customer? Yes, it is mandatory, so orders.customer_id is NOT NULL. Must a customer have an order? No, Jon Smith has none, so the relationship is optional on that side.

Diagrams use crow's foot notation. Read the symbols at each end of the line.

|| means exactly one, o{ means zero or many. So one student has zero or many enrollments.

Many-to-many always becomes three tables#

A relational table cannot hold a many-to-many relationship directly. A student takes many courses, a course has many students. The answer is a junction table (also called an associative or bridge table) with a foreign key to each side.

Notice what lives in the junction table. enrolled_at and grade do not belong to a student or to a course. They belong to the pairing. This is the clue that the junction is really an entity of its own, and the same shape as order_items holding quantity and unit_price.

CREATE TABLE students (
    student_id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    full_name  VARCHAR(100) NOT NULL,
    email      VARCHAR(255) NOT NULL UNIQUE
);

CREATE TABLE courses (
    course_id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    title     VARCHAR(150) NOT NULL,
    credits   SMALLINT NOT NULL CHECK (credits BETWEEN 1 AND 10)
);

CREATE TABLE enrollments (
    student_id  INTEGER NOT NULL REFERENCES students (student_id) ON DELETE CASCADE,
    course_id   INTEGER NOT NULL REFERENCES courses  (course_id),
    enrolled_at DATE    NOT NULL DEFAULT CURRENT_DATE,
    grade       CHAR(1) CHECK (grade IN ('A','B','C','D','F')),
    PRIMARY KEY (student_id, course_id)
);

CREATE INDEX idx_enrollments_course ON enrollments (course_id);

The composite primary key guarantees a student cannot enroll in the same course twice. Its index serves lookups that start with student_id. Questions that start from the course side ("who is in this course?") need the extra index on course_id.

Keys: natural versus surrogate#

A natural key is a real-world identifier, like an email, a passport number or an ISBN. A surrogate key is a meaningless number the database generates.

Natural keys sound appealing and age badly. Emails change. Government numbers get reissued. Two systems disagree on format. When a key changes, every foreign key pointing at it has to change too.

The usual practice: surrogate primary key for identity, plus a UNIQUE constraint on the natural identifier to still enforce uniqueness. That is exactly what customers does with customer_id and email.

A composite natural key is fine for junction tables, where the pair really is the identity.

Dependent (weak) entities#

Some entities cannot exist without a parent. An order line has no meaning outside its order. These dependent entities usually carry the parent's key as part of their own primary key. That is the case for order_items (order_id, product_id), and it pairs naturally with ON DELETE CASCADE: remove the order and its lines go with it.

Modeling hierarchies and subtypes#

Sometimes several entities share most of their attributes. Payments might be card, bank transfer or wallet, each with a few unique fields. There are three standard shapes.

Start with the single table and a CHECK that enforces which columns are required for which type. Split only when the nullable columns become unmanageable.

Mistakes that are expensive to fix#

Lists in a column. tags = 'red,blue' or an array of foreign keys you cannot constrain. Use a junction table.

One-size-fits-all "details" table (EAV). A table of entity_id, attribute_name, value feels flexible and turns every query into a pivot with no types and no constraints. Use real columns, or JSONB for genuinely free-form data.

Polymorphic foreign keys. A comments table with target_type and target_id pointing at "whichever table". The database cannot enforce it. Use separate foreign key columns, or one junction table per target.

Storing derived data you cannot keep correct. An order_total column that someone must remember to update on every line change will drift. Compute it, or keep it correct with a view or trigger.

Over-using nullable columns. A table with thirty columns that are each null for most rows is usually several entities pretending to be one.

Naming inconsistency. customer_id in one table, cust in another, CustomerID in a third. Pick one scheme: singular or plural table names, snake_case, and <table>_id for keys.

Checking the model#

Go back to the questions from step 1 and write the query for each one, even roughly. Each should need a clear path of joins from table to table. If a question needs a join that has no key to join on, or needs data that nothing stores, you found the gap while it still costs nothing to fix.

Practice#

1. A ride-sharing app has riders, drivers, vehicles and trips. A driver may use different vehicles over time, a trip has one rider and one driver. List the entities, write the cardinality sentences in both directions for each relationship, and mark the many-to-many ones.
2. Write the CREATE TABLE statements for your ride-sharing model. Include primary keys, foreign keys, at least two CHECK constraints and an index on every foreign key.
3. Decide whether "payment method" is an attribute or an entity in the course shop. Defend your answer by naming one question the business might ask that requires your choice.
4. In order_items, why is (order_id, product_id) a good primary key but product_id alone would not be? What question would break if a customer wanted two separate lines of the same product in one order?
5. A colleague proposes a contacts table with a column phones holding '555-0101;555-0102'. Redesign it and write the query that finds the contact owning '555-0102'.