Database Schema Design Basics — From ER Diagram to SQL

1. From Requirements to Entities

Requirements arrive as prose. "A customer can place orders, each order contains several products, and we need to remember the price at the time of purchase." Your job is to turn that sentence into nouns and verbs, then decide which nouns deserve a table.

The extraction rule is mechanical: nouns that have attributes and a lifecycle become entities; verbs become relationships.

• Customer — has a name, email, created date; exists independently.
• Order — has a date, status, total; belongs to exactly one customer.
• Product — has a SKU, name, current price; exists independently of any order.
• Order line — the verb "contains" becomes a table, because an order relates to many products and each occurrence carries its own data (quantity, price paid).

Two tests separate real entities from attributes that just look like them:

1. Can it exist on its own? An address is usually an attribute of a customer, not a separate entity — unless several customers can share one and you must update it in one place.
2. Does it have a many-to-many relationship? If "order" and "product" connect many-to-many and the connection carries data, that connection is an entity. This is how order_items earns its place.

Note what the requirement forces: "remember the price at the time of purchase" means the price cannot live only on products. Copying it into order_items.unit_price is deliberate denormalization, and the requirement is telling you to do it. Reading requirements for sentences like that — anything about history, snapshots, or "at the time of" — is how you avoid a schema that silently rewrites the past whenever a price changes.

2. Choosing a Primary Key: Auto-Increment vs UUID vs Business Key

A primary key does three jobs: it identifies a row, it is indexed (usually as the clustered index in MySQL/InnoDB and SQL Server), and it is what foreign keys point at. The choice affects insert performance, index size, and how hard your migrations are later.

Auto-increment / sequence integer

A server-generated monotonic number. Small (4 or 8 bytes), fast to compare, and because inserts land at the end of the B-tree they hit one hot page instead of scattering across the index. The catch: the value only exists after the insert, so you must round-trip or use RETURNING to learn it, and sequential IDs leak volume to anyone who reads your URLs.

CREATE TABLE customers (
    id          BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,  -- PostgreSQL 10+
    email       VARCHAR(255) NOT NULL UNIQUE,
    created_at  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- MySQL 8.0 / MariaDB equivalent:
-- id BIGINT AUTO_INCREMENT PRIMARY KEY

UUID

Generated anywhere — client, application server, or database — without coordination. Ideal when IDs must be created before the row is written, when data from several systems is merged, or when exposing a guessable ID is a security problem. The costs are real: 16 bytes instead of 8, a larger secondary-index footprint (every index stores the primary key), and, with random UUIDv4, insert order that jumps across the whole B-tree. Prefer UUIDv7 or ULID, which prefix a millisecond timestamp and behave much closer to sequential values.

CREATE TABLE events (
    id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),  -- pgcrypto / PG13+
    payload     JSONB NOT NULL,
    created_at  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

Business / natural key

A value from the domain: an ISBN, a national tax ID, an email address. Tempting because it is "already unique," but it couples your schema to a real-world identifier that can change. People change emails. Companies reissue codes. A 20-character string key bloats every foreign key and every index that carries it.

The pattern that avoids the trade-off entirely: surrogate primary key plus a unique business column. Let the table have a BIGINT identity key for joins and indexes, and keep the email or SKU as a separate UNIQUE column for lookup and deduplication. You get small keys and domain integrity at once.

3. Foreign Keys and Referential Integrity

A foreign key is a constraint that says "this value must exist in that other table." It is the database enforcing the relationship you drew on the diagram, and it is the difference between an integrity guarantee and a hopeful application check that one code path forgets to run.

CREATE TABLE orders (
    id           BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_id  BIGINT NOT NULL,
    order_date   DATE NOT NULL DEFAULT CURRENT_DATE,
    status       VARCHAR(20) NOT NULL DEFAULT 'pending',
    CONSTRAINT fk_orders_customer
        FOREIGN KEY (customer_id) REFERENCES customers (id)
        ON DELETE RESTRICT
        ON UPDATE CASCADE
);

Besides blocking orphaned rows, a declared foreign key lets the optimizer know the cardinality of a join, which is often the single biggest reason a query plan improves after you add constraints you "didn't strictly need."

ON DELETE CASCADE vs RESTRICT

This clause decides what happens to child rows when the parent is deleted, and guessing wrong is how teams lose data. Four behaviors, one clear rule for choosing:

The judgement call: CASCADE for ownership, RESTRICT for reference. If the child row is a piece of the parent — delete the order, the line items go with it — cascade is correct and saves you a manual cleanup in every code path. If the child merely points at the parent, cascade is a booby trap: deleting one user quietly removes every order they ever placed, and no one notices until an accounting report comes up short. Default to RESTRICT, and add cascade only where ownership is genuine.

4. Normalization: 1NF, 2NF, 3NF

Normal forms are not academic trivia; each one names a specific anomaly that costs you real money. The first three cover almost every practical schema.

First normal form (1NF) — atomic values

Every cell holds a single value, and each row is unique. A tags column containing 'red,blue,green' is not atomic: you cannot index it usefully, you cannot join on it, and filtering for one tag means a LIKE '%blue%' full scan that also matches "blueberry."

-- Violates 1NF: three tags stuffed into one column
CREATE TABLE posts_bad (
    id    BIGINT PRIMARY KEY,
    title VARCHAR(200),
    tags  VARCHAR(500)   -- 'sql,database,performance'
);

-- 1NF fix: one row per value, own table
CREATE TABLE post_tags (
    post_id  BIGINT NOT NULL REFERENCES posts (id) ON DELETE CASCADE,
    tag      VARCHAR(50) NOT NULL,
    PRIMARY KEY (post_id, tag)
);

Second normal form (2NF) — no partial dependencies

1NF plus: every non-key column depends on the whole primary key, not part of it. This only bites composite keys. Consider an order item keyed by (order_id, product_id) that also stores product_name. Product name depends only on product_id, half the key — so renaming a product means updating every order that ever referenced it, and they can drift out of sync. Remove it to the products table, and copy it into the order item only if the requirement demands a historical snapshot.

Third normal form (3NF) — no transitive dependencies

2NF plus: no non-key column depends on another non-key column. If orders stores both customer_id and customer_city, the city depends on the customer, not on the order. Change a customer's city and the old orders now disagree with the customer record. Drop the column and join for it.

-- Violates 3NF: customer_city transitively depends on customer_id
CREATE TABLE orders_bad (
    id            BIGINT PRIMARY KEY,
    customer_id   BIGINT NOT NULL REFERENCES customers (id),
    customer_city VARCHAR(100)   -- duplicate of customers.city
);

-- 3NF: keep only the foreign key
CREATE TABLE orders (
    id          BIGINT PRIMARY KEY,
    customer_id BIGINT NOT NULL REFERENCES customers (id),
    order_date  DATE NOT NULL
);
-- Read the city through the join:
SELECT o.id, c.city
FROM orders o
JOIN customers c ON c.id = o.customer_id;

The mnemonic that sticks: 1NF is about cells (one value each), 2NF is about partial keys, 3NF is about indirect facts (a column that could be derived from another non-key column).

5. When to Break the Rules on Purpose

Normalization is optimized for correct writes. Reads are the other half of the job, and sometimes a fully normalized schema needs three joins to render one screen. Deliberate denormalization is a legitimate engineering choice when it is measured and maintained:

• Precomputed aggregates. Store orders.item_count and orders.total instead of summing line items on every read. The write path updates them in the same transaction.
• Historical snapshots. Copy unit_price and product_name into order_items at purchase time. This is not a duplicate — it is a record of the past, and it must not follow the product's current price.
• Read models for reporting. A denormalized fact table refreshed on a schedule, kept out of the transactional path.
• JSON columns for genuinely schemaless data. Third-party webhook bodies or feature flags that vary per row. Use them for payloads you only read whole, never for anything you need to filter and join on regularly.

The discipline that makes this safe is simple: only denormalize with a named query in mind and one owner responsible for keeping the copy in sync. If you cannot say which read is too slow and how the redundant column gets updated, you are not denormalizing — you are accumulating drift. Normalize first, measure with EXPLAIN, then break the rule where the data says it matters.

6. Common Table Design Mistakes

Multi-value columns and comma-separated lists

The single most common schema defect. Lists in a column break 1NF, cannot be indexed, cannot be joined, and force string parsing into every consumer. When a value needs its own identity (it will be filtered, sorted, or joined), it needs its own row. The fix is always a link table like post_tags above.

Wide tables with dozens of nullable columns

A table with 60 columns where half are NULL for any given row usually means two entities got merged. Nullable columns are not free: they add a bitmap to every row, complicate every NOT NULL check the optimizer could otherwise exploit, and let the application write half-populated rows the schema cannot reject. Split the optional group into its own table with a foreign key, or model it as a proper subtype (a payment_methods table with a type discriminator).

Using a mutable business value as the primary key

Emails, usernames, and phone numbers change. When they are the primary key, a change ripples into every foreign key, every index, and every cached URL. Use a surrogate key and a UNIQUE constraint on the business value.

Forgetting NOT NULL on columns that are always populated

If a column is required by the business, say so in DDL. NULL is not "empty string" or "zero" — it is "unknown," and it propagates: NULL = NULL is not true, aggregates skip it, and WHERE col = '' silently misses every row where the value was never set. Constrain early; adding NOT NULL later requires a full table scan and a backfill.

No constraints at all — "we validate in the app"

Applications have many code paths and one database. A CHECK or unique constraint is enforced for every one of them, including the script someone runs at 2 a.m. That is not paranoia; it is the cheapest bug prevention available.

7. Naming Conventions

Naming is not cosmetic — inconsistent names cost every query author a lookup and make generated tooling harder to write. Pick a convention and hold it; the specific choice matters less than applying it everywhere.

Two conventions carry more weight than the rest. First, never mix plural and singular table names — pick plural, because a table holds a set. Second, name every foreign key column after the table it references, so joins read left-to-right without a mental translation: orders.customer_id = customers.id needs no decoding. Keeping the primary key simply id in every table is what makes that possible, since the alias (c.id) already disambiguates.

Avoid SQL reserved words as identifiers (order, user, group, key). They are legal when quoted, but quoting is contagious: once you quote one identifier you tend to quote them all, and quoted identifiers become case-sensitive in PostgreSQL and MySQL, which turns a rename into a debugging session.

8. From ER Diagram to CREATE TABLE — Full Example

Here is the whole process on a small but realistic feature: a shop where customers place orders, orders contain products, and the price at purchase must be preserved.

The diagram

customers                 orders                     products
+---------------+         +------------------+       +---------------+
| id (PK)       |1       *| id (PK)          |*     *| id (PK)       |
| email         |---------| customer_id (FK) |-------| sku           |
| full_name     |         | order_date       |  via  | name          |
| created_at    |         | status           |       | price_cents   |
+---------------+         | total_cents      |       +---------------+
                          +------------------+              |
                                   |1                       |1
                                   |                        |
                                   |*                       |*
                          +--------------------+            |
                          | order_items        |*-----------+
                          | id (PK)            |  (many-to-many
                          | order_id (FK)      |   resolved by
                          | product_id (FK)    |   the junction)
                          | quantity           |
                          | unit_price_cents   |
                          +--------------------+

Relationships:
  customers 1 --- * orders          (FK on orders)
  orders    1 --- * order_items     (FK on order_items, CASCADE)
  products  1 --- * order_items     (FK on order_items, RESTRICT)

Three translation rules took the diagram to that shape:

1. One-to-many (customer → orders): the foreign key goes on the many side, orders.customer_id.
2. Many-to-many (orders ↔ products): a junction table, order_items, holding both foreign keys.
3. Carried data (quantity, price at purchase): columns on the junction, since the data belongs to the relationship, not to either entity.

The DDL

CREATE TABLE customers (
    id          BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    email       VARCHAR(255) NOT NULL UNIQUE,
    full_name   VARCHAR(150) NOT NULL,
    created_at  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE products (
    id           BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    sku          VARCHAR(40)  NOT NULL UNIQUE,
    name         VARCHAR(200) NOT NULL,
    price_cents  INTEGER      NOT NULL CHECK (price_cents >= 0),
    is_active    BOOLEAN      NOT NULL DEFAULT TRUE
);

CREATE TABLE orders (
    id           BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_id  BIGINT NOT NULL,
    order_date   DATE   NOT NULL DEFAULT CURRENT_DATE,
    status       VARCHAR(20) NOT NULL DEFAULT 'pending'
                     CHECK (status IN ('pending','paid','shipped','cancelled')),
    total_cents  INTEGER NOT NULL DEFAULT 0 CHECK (total_cents >= 0),
    created_at   TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_orders_customer
        FOREIGN KEY (customer_id) REFERENCES customers (id)
        ON DELETE RESTRICT          -- never erase a customer's history silently
);

CREATE TABLE order_items (
    id                BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    order_id          BIGINT  NOT NULL,
    product_id        BIGINT  NOT NULL,
    quantity          INTEGER NOT NULL CHECK (quantity > 0),
    unit_price_cents  INTEGER NOT NULL CHECK (unit_price_cents >= 0),
    CONSTRAINT fk_items_order
        FOREIGN KEY (order_id) REFERENCES orders (id)
        ON DELETE CASCADE,          -- items are owned by their order
    CONSTRAINT fk_items_product
        FOREIGN KEY (product_id) REFERENCES products (id)
        ON DELETE RESTRICT,         -- keep products referenced by history
    CONSTRAINT uq_items_order_product UNIQUE (order_id, product_id)
);

CREATE INDEX idx_orders_customer_date ON orders (customer_id, order_date);
CREATE INDEX idx_items_product        ON order_items (product_id);

Every design decision from this article is visible in those statements:

• Surrogate BIGINT keys everywhere, with the business values (email, sku) as separate UNIQUE columns — small joins, intact domain rules.
• Different ON DELETE rules by intent. Orders restrict (history is sacred), order items cascade (they are parts of the order), products restrict (a product referenced by an old order cannot simply vanish).
• Constraints enforce the domain. CHECK (quantity > 0), the status whitelist, and price_cents >= 0 mean bad data cannot be inserted even by a hand-written script.
• Deliberate denormalization, bounded. total_cents on orders duplicates the sum of line items for fast reads, and unit_price_cents on order_items preserves the historical price. Both are updated in the same transaction that writes the items — the discipline that keeps them honest.
• Indexes match the queries. The composite (customer_id, order_date) serves "this customer's recent orders" with a single range scan; idx_items_product covers the reverse lookup from product to sales.

Verify the result

Before you commit DDL, run it through a validator to catch syntax and dialect problems, and format it so the constraint block lines up — a schema is read far more often than it is written. Try the SQL formatter and the SQL validator, or sketch the boxes first with the ER diagram tool and paste the generated DDL back into the formatter to tidy it.

-- Sanity check after loading: any order whose total disagrees with its items?
SELECT o.id,
       o.total_cents,
       COALESCE(SUM(i.quantity * i.unit_price_cents), 0) AS computed
FROM orders o
LEFT JOIN order_items i ON i.order_id = o.id
GROUP BY o.id, o.total_cents
HAVING o.total_cents <> COALESCE(SUM(i.quantity * i.unit_price_cents), 0);

Run that after every batch import. It is the cheapest possible tripwire for the one thing denormalization gets wrong: the cached total falling out of sync with the rows it summarizes.

FAQ — Database Schema Design