ER Diagrams & Schema Design for LLD

ER Diagrams & Schema Design

Your class diagram describes objects and their relationships in memory. The moment those objects need to survive a process restart, that model has to be translated into tables, columns, keys, and indexes — and the translation is not mechanical. A List<LineItem> field on an Order class doesn't become a column; it becomes a decision about foreign keys, junction tables, and how many rows you're willing to scan. This guide is that translation layer: how to read and draw ER diagrams, how to normalize (and when to stop), and how to keep a schema alive under production traffic while it changes.

1. Entity Identification: Nouns Become Tables

The first pass at a schema is almost linguistic: nouns in the domain become entities (tables); verbs and relationships between them become foreign keys or junction tables. In a typical e-commerce domain: Customer places an Order. An Order contains LineItems. A LineItem references a Product. Four nouns, three relationships.

Not every noun deserves a table. A ShippingAddress that only ever exists embedded inside one Order and is never queried independently is a candidate for embedding as columns (or a JSON column) rather than a separate table — that's a normalization trade-off covered in Section 3.

2. Relationship Mapping: 1:1, 1:N, M:N

Every relationship in your object model collapses into one of three cardinalities, and each has a distinct, memorizable schema pattern.

2.1 One-to-One (1:1)

A Customer has exactly one CustomerProfile (billing preferences, marketing opt-ins) — split out for size or access-pattern reasons, not because it's a different "kind" of entity.

CREATE TABLE customer_profiles (
    id            BIGSERIAL PRIMARY KEY,
    customer_id   BIGINT NOT NULL UNIQUE REFERENCES customers(id),
    marketing_opt_in BOOLEAN NOT NULL DEFAULT false,
    preferred_currency VARCHAR(3) NOT NULL DEFAULT 'USD'
);

The UNIQUE constraint on the foreign key is what makes this 1:1 instead of 1:N — without it, one customer could have many profile rows.

2.2 One-to-Many (1:N) — FK Goes on the Many Side

A Customer places many Orders; each Order belongs to exactly one Customer. The foreign key always lives on the "many" side. This is the single most common source of schema mistakes for engineers new to relational design — putting the FK on the wrong table, or trying to store a list of order IDs on the customer row.

CREATE TABLE orders (
    id           BIGSERIAL PRIMARY KEY,
    customer_id  BIGINT NOT NULL REFERENCES customers(id),
    status       VARCHAR(20) NOT NULL DEFAULT 'PENDING',
    created_at   TIMESTAMPTZ NOT NULL DEFAULT now()
);
-- customer_id is indexed so "all orders for customer X" stays cheap
CREATE INDEX idx_orders_customer_id ON orders(customer_id);

2.3 Many-to-Many (M:N) — Junction Table

An Order can contain many Products (via line items), and a Product can appear on many Orders. This needs a junction table in between — and that junction table is frequently a first-class entity in its own right (it's where quantity and unit_price live).

CREATE TABLE order_line_items (
    id                     BIGSERIAL PRIMARY KEY,
    order_id               BIGINT NOT NULL REFERENCES orders(id),
    product_id             BIGINT NOT NULL REFERENCES products(id),
    quantity               INT NOT NULL CHECK (quantity > 0),
    unit_price_snapshot    NUMERIC(10,2) NOT NULL,  -- price AT TIME OF ORDER, not current_price
    UNIQUE (order_id, product_id)
);
CREATE INDEX idx_line_items_order_id ON order_line_items(order_id);
CREATE INDEX idx_line_items_product_id ON order_line_items(product_id);

3. Normalization in Practice: 1NF Through 3NF

Normalization removes redundancy by splitting data into smaller, related tables. Each "normal form" fixes a specific class of anomaly. In interviews and in practice, you rarely need to name-drop past 3NF — but you need to recognize violations of 1NF-3NF on sight, because they cause real bugs.

1NF — Atomic Values, No Repeating Groups

Every column holds a single, atomic value — no comma-separated lists, no arrays hiding multiple facts in one cell.

-- VIOLATION: repeating group crammed into one column
CREATE TABLE orders_bad (
    id          BIGINT PRIMARY KEY,
    customer_id BIGINT NOT NULL,
    product_ids VARCHAR(500)  -- '101,102,205' — not atomic, not queryable, not indexable
);

-- FIXED: one row per (order, product) fact — this is 1NF
CREATE TABLE orders (
    id          BIGSERIAL PRIMARY KEY,
    customer_id BIGINT NOT NULL REFERENCES customers(id)
);
CREATE TABLE order_line_items (
    order_id    BIGINT NOT NULL REFERENCES orders(id),
    product_id  BIGINT NOT NULL REFERENCES products(id),
    PRIMARY KEY (order_id, product_id)
);

2NF — No Partial Dependency on a Composite Key

Applies when the primary key is composite: every non-key column must depend on the whole key, not just part of it.

-- VIOLATION: product_name depends only on product_id, not on the full
-- (order_id, product_id) key — it's a partial dependency
CREATE TABLE order_line_items_bad (
    order_id     BIGINT,
    product_id   BIGINT,
    product_name VARCHAR(200),  -- depends only on product_id
    quantity     INT,
    PRIMARY KEY (order_id, product_id)
);

-- FIXED: product_name lives in products, keyed only by product_id
CREATE TABLE products (
    id   BIGSERIAL PRIMARY KEY,
    name VARCHAR(200) NOT NULL
);
CREATE TABLE order_line_items (
    order_id   BIGINT NOT NULL,
    product_id BIGINT NOT NULL REFERENCES products(id),
    quantity   INT NOT NULL,
    PRIMARY KEY (order_id, product_id)
);

3NF — No Transitive Dependency

Non-key columns must depend on the key directly, not on another non-key column.

-- VIOLATION: customer_city depends on customer_id, which depends on
-- orders.id — a transitive dependency through customer_id
CREATE TABLE orders_bad (
    id            BIGINT PRIMARY KEY,
    customer_id   BIGINT NOT NULL,
    customer_city VARCHAR(100)  -- transitively dependent, duplicated on every order
);

-- FIXED: customer_city lives once, on customers
CREATE TABLE customers (
    id   BIGSERIAL PRIMARY KEY,
    city VARCHAR(100)
);
CREATE TABLE orders (
    id          BIGSERIAL PRIMARY KEY,
    customer_id BIGINT NOT NULL REFERENCES customers(id)
);

When to Denormalize on Purpose

Normalization trades write-simplicity for read-cost (more joins). Denormalization is a deliberate, documented trade in the other direction — never an accident.

4. Primary Keys, Foreign Keys, and Indexing Strategy

Primary key choice

Default to a surrogate key (BIGSERIAL or UUID) even when a natural key looks unique today — natural keys have a habit of turning out not to be.

Indexing decision table

An index speeds up reads at the cost of slower writes (every insert/update/delete maintains every index on that table) and extra storage. Index deliberately, not everywhere.

-- Composite index: supports "orders for a customer, filtered by status"
-- Column order matters: put the equality-filtered, high-selectivity column first
CREATE INDEX idx_orders_customer_status ON orders(customer_id, status);
 
-- Covering index: query never touches the table heap for this lookup
CREATE INDEX idx_orders_covering ON orders(customer_id, status) INCLUDE (created_at, total_amount);

5. Soft Deletes vs Hard Deletes

Hard delete (DELETE FROM orders WHERE id = ?) is simple and keeps tables small, but destroys audit history and breaks any FK still pointing at the row.

Soft delete keeps the row and flags it, preserving history and referential integrity — at the cost of every query needing a filter.

ALTER TABLE customers ADD COLUMN is_deleted BOOLEAN NOT NULL DEFAULT false;
ALTER TABLE customers ADD COLUMN deleted_at TIMESTAMPTZ;
 
-- Every "live" query must now filter:
SELECT * FROM customers WHERE is_deleted = false AND email = 'a@b.com';

The classic trap: a plain UNIQUE constraint on email blocks re-registration after a soft delete, because the "deleted" row still occupies the unique value.

-- WRONG: soft-deleted rows still block new signups with the same email
ALTER TABLE customers ADD CONSTRAINT uq_email UNIQUE (email);
 
-- RIGHT: partial unique index — uniqueness enforced only among live rows
CREATE UNIQUE INDEX uq_customers_email_live
    ON customers(email)
    WHERE is_deleted = false;

6. Audit Columns and Multi-Tenant Schema Design

Standard audit columns

Nearly every table in a production schema should carry these, regardless of domain:

ALTER TABLE orders ADD COLUMN created_at TIMESTAMPTZ NOT NULL DEFAULT now();
ALTER TABLE orders ADD COLUMN updated_at TIMESTAMPTZ NOT NULL DEFAULT now();
ALTER TABLE orders ADD COLUMN created_by BIGINT REFERENCES users(id);
ALTER TABLE orders ADD COLUMN updated_by BIGINT REFERENCES users(id);

updated_at should be maintained by a trigger (or ORM lifecycle hook) rather than trusted from application code — application code forgets; triggers don't.

CREATE OR REPLACE FUNCTION set_updated_at() RETURNS trigger AS $$
BEGIN
    NEW.updated_at = now();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;
 
CREATE TRIGGER trg_orders_updated_at
    BEFORE UPDATE ON orders
    FOR EACH ROW EXECUTE FUNCTION set_updated_at();

For a full audit trail (not just "last modified"), append-only history is more reliable than columns on the live row:

CREATE TABLE order_audit_log (
    id          BIGSERIAL PRIMARY KEY,
    order_id    BIGINT NOT NULL REFERENCES orders(id),
    action      VARCHAR(20) NOT NULL,   -- CREATED, STATUS_CHANGED, CANCELLED
    old_value   JSONB,
    new_value   JSONB,
    changed_by  BIGINT REFERENCES users(id),
    changed_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

Multi-tenant schema design

Three standard strategies, in increasing order of isolation (and operational cost):

-- Shared-table strategy: every tenant-scoped table carries tenant_id,
-- and it participates in every index and every unique constraint.
CREATE TABLE orders (
    id          BIGSERIAL PRIMARY KEY,
    tenant_id   BIGINT NOT NULL,
    customer_id BIGINT NOT NULL,
    status      VARCHAR(20) NOT NULL
);
CREATE INDEX idx_orders_tenant_customer ON orders(tenant_id, customer_id);
 
-- Postgres Row-Level Security enforces isolation even if application code forgets the filter
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON orders
    USING (tenant_id = current_setting('app.current_tenant')::BIGINT);

7. Schema Evolution: Zero-Downtime Migrations

Production schemas change under live traffic. A naive ALTER TABLE orders ADD COLUMN priority VARCHAR(10) NOT NULL DEFAULT 'NORMAL' can, on some databases/versions, lock the table for a full rewrite. The safe pattern splits every "breaking-looking" change into backward-compatible steps, each independently deployable.

Adding a required column

-- Step 1: nullable add — cheap, no table rewrite in most engines
ALTER TABLE orders ADD COLUMN priority VARCHAR(10);
 
-- Step 2: (deploy) application code now writes 'NORMAL'/'HIGH' on every insert
 
-- Step 3: backfill in batches, not one giant transaction
UPDATE orders SET priority = 'NORMAL' WHERE priority IS NULL AND id BETWEEN 1 AND 100000;
-- repeat in batches...
 
-- Step 4: only after backfill is confirmed complete
ALTER TABLE orders ALTER COLUMN priority SET NOT NULL;
ALTER TABLE orders ALTER COLUMN priority SET DEFAULT 'NORMAL';

Renaming or removing a column safely

Never rename in place — old code is still deployed and reading the old name during a rolling deploy.

General migration principles

8. Full Domain Example: ER Diagram End to End

Putting Sections 2, 5, and 6 together into one coherent schema:

Interview Questions

• Given a domain description in plain English, how do you decide which nouns become tables versus embedded columns?
• Explain why the foreign key for a 1:N relationship always lives on the "many" side. What breaks if you try to model it the other way (an array of IDs on the "one" side)?
• Walk through 1NF, 2NF, and 3NF with a concrete violation and fix for each.
• When would you deliberately denormalize a schema, and what do you do to keep the denormalized copy from going stale?
• What's the difference between a plain UNIQUE constraint and a partial/filtered unique index, and why does soft-delete design need the latter?
• How do you add a NOT NULL column to a table with 100 million rows without downtime?
• Design a multi-tenant schema for a SaaS product. What are the trade-offs between a shared tenant_id column, schema-per-tenant, and database-per-tenant?
• Why should updated_at be set by a database trigger rather than application code?