SQL Schema Design: Keys, Constraints, Indexes, Migrations

A database schema is the blueprint for the facts an application keeps. Screens and controllers can be rebuilt, but stored relationships, identifiers, and constraints remain after those interfaces change.

Good schema design gives each fact one clear home, rejects impossible states, and supports the queries the application must run. The blueprint should be strict where the business rule is strict and flexible only where absence or variation has a defined meaning.

Start with Facts

Describe the domain in short factual statements before creating tables:

• A customer can place many orders.
• An order belongs to one customer.
• An order contains one or more products.
• A product can appear in many orders.
• Each order item records a positive quantity.

These statements reveal entities, relationships, cardinality, and required values. A screen called "checkout" does not need a checkout table. The underlying customer, order, product, and item facts need tables that remain meaningful after the screen changes.

A Worked Normalization Example

Suppose an imported sales sheet stores one order with lists inside text fields:

Order	Customer	Products
1001	sam@example.com	KB-1, MS-2

A matching quantities value of 1,2 depends on list position. The database cannot enforce that each SKU exists, index one product cleanly, or join an order item without parsing text.

Step 1: Make Each Value Atomic

First normal form gives each row and column a single value. Expand the product list into one row per order and product pair:

CREATE TABLE order_lines_raw (
  order_id bigint NOT NULL,
  ordered_at timestamptz NOT NULL,
  customer_email text NOT NULL,
  customer_name text NOT NULL,
  product_sku text NOT NULL,
  product_name text NOT NULL,
  quantity integer NOT NULL,
  PRIMARY KEY (order_id, product_sku)
);

Order 1001 now produces two rows: KB-1 with quantity 1 and MS-2 with quantity 2. Individual products can be filtered and counted, but order and customer details repeat on both rows.

Step 2: Separate Partial Dependencies

The raw table has a composite key of (order_id, product_sku). Some columns depend on only part of that key: ordered_at depends on the order, while product_name depends on the product SKU. Second normal form moves those facts to their owners.

CREATE TABLE products (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  sku text NOT NULL UNIQUE,
  name text NOT NULL
);

CREATE TABLE orders_stage (
  id bigint PRIMARY KEY,
  ordered_at timestamptz NOT NULL,
  customer_email text NOT NULL,
  customer_name text NOT NULL
);

CREATE TABLE order_items_stage (
  order_id bigint NOT NULL,
  product_id bigint NOT NULL REFERENCES products (id),
  quantity integer NOT NULL CHECK (quantity > 0),
  PRIMARY KEY (order_id, product_id)
);

The product name is now stored once, and the order item contains only facts about the relationship: which product, which order, and how many.

Step 3: Separate Transitive Dependencies

In orders_stage, the customer name depends on the customer email, not directly on the order ID. Third normal form moves the customer facts into a customer table and gives the order a foreign key.

The final orders and order_items tables replace the staging tables.

CREATE TABLE customers (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email text NOT NULL UNIQUE,
  name text NOT NULL
);

CREATE TABLE orders (
  id bigint PRIMARY KEY,
  customer_id bigint NOT NULL REFERENCES customers (id),
  ordered_at timestamptz NOT NULL
);

CREATE TABLE order_items (
  order_id bigint NOT NULL REFERENCES orders (id),
  product_id bigint NOT NULL REFERENCES products (id),
  quantity integer NOT NULL CHECK (quantity > 0),
  PRIMARY KEY (order_id, product_id)
);

Migrate the staged rows into the final customer, order, and order-item tables, then remove the staging tables after verification. The final blueprint has one owner for each customer, product, order, and order-item fact.

Verify the Normalized Result

A join should reconstruct the useful order view without parsing lists:

SELECT
  orders.id AS order_id,
  customers.email,
  products.name AS product_name,
  order_items.quantity
FROM orders
JOIN customers ON customers.id = orders.customer_id
JOIN order_items ON order_items.order_id = orders.id
JOIN products ON products.id = order_items.product_id
WHERE orders.id = 1001
ORDER BY products.sku;

For the sample data, the expected result has two rows:

Order	Product	Quantity
1001	Keyboard	1
1001	Mouse	2

The normalized tables preserve the original order while allowing the database to enforce every relationship and quantity.

Choose Stable Keys

A primary key gives a row stable identity. Generated identity columns or UUIDs work well for entities whose business labels may change. A customer's email, a product SKU, or an article slug can remain unique without becoming the internal relationship key.

CREATE TABLE articles (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  slug text NOT NULL UNIQUE,
  title text NOT NULL
);

The generated identity gives foreign keys a stable anchor. The slug remains a protected public identifier that can follow its own rename and redirect policy.

Composite keys fit relationship tables when the pair itself is the identity. The (order_id, product_id) key prevents the same product appearing twice in one order. If repeated lines are valid, add a separate line ID and encode the real uniqueness rule instead.

Enforce Relationships and Values

Foreign keys keep references valid across every writer. NOT NULL marks required values, UNIQUE protects identifiers, and CHECK rejects values outside a rule.

CREATE TABLE coupons (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  code text NOT NULL UNIQUE,
  discount_percent integer NOT NULL
    CHECK (discount_percent BETWEEN 1 AND 100)
);

The expected result is that discount_percent = 20 succeeds while discount_percent = 140 fails. The rule stays with the data and applies to every writer.

Use a composite unique constraint when uniqueness has a scope. UNIQUE (tenant_id, email) permits the same email in different tenants but prevents duplicates inside one tenant.

Model NULL and Delete Behavior

Use NULL only when absence has a defined meaning. A nullable shipped_at can mean the order has not shipped. A nullable customer ID on an order may mean the ownership rule is missing.

Foreign keys also need deliberate delete behavior. ON DELETE CASCADE fits a child that cannot exist without its parent, such as an order item. Independent business records usually need RESTRICT, archival, or a status change instead of silent deletion.

CREATE TABLE order_items (
  order_id bigint NOT NULL
    REFERENCES orders (id) ON DELETE CASCADE,
  product_id bigint NOT NULL
    REFERENCES products (id) ON DELETE RESTRICT,
  quantity integer NOT NULL CHECK (quantity > 0),
  PRIMARY KEY (order_id, product_id)
);

Soft deletion adds lifecycle rules rather than removing them. Define visibility, uniqueness, restoration, child behavior, and final cleanup before adding a nullable deleted_at column.

Index Real Access Paths

Primary and unique keys have supporting indexes. Foreign-key columns on the referencing side may need separate indexes for joins, filters, parent updates, or deletes.

CREATE INDEX orders_customer_id_idx
  ON orders (customer_id);

CREATE INDEX orders_customer_date_idx
  ON orders (customer_id, ordered_at DESC);

The first index supports common customer-to-order joins efficiently. The second may support a route that lists one customer's newest orders. Confirm the choice with the real query and EXPLAIN, because every additional index increases storage and write work.

Plan Schema Migrations

A correct final blueprint still needs a safe construction sequence. Adding a required column to populated data commonly uses four phases:

1. Add the column as nullable or with a safe transitional default.
2. Backfill existing rows and verify the result.
3. Add the required constraint after every row is valid.
4. Remove transitional code after all writers use the new shape.

Before adding a foreign key, find orphaned values. Find and resolve duplicates before adding a uniqueness constraint. Before splitting a table, compare row counts and reconstructed query results. Run the migration on production-shaped data and record rollback or roll-forward steps.

Common Pitfalls and Debugging

Storing Lists in Text Columns

Comma-separated IDs cannot receive normal foreign keys and produce fragile substring queries. Move each relationship into a row in a join table, migrate the values, and compare counts before removing the text column.

Adding a Foreign Key to Dirty Data

The constraint fails when child rows reference missing parents. Find orphans with a left join, decide whether to repair, archive, or delete them, and add the constraint only after the cleanup result is reviewed.

Normalizing Without Reading the Query

A theoretically tidy model can still miss the application's main access path. Write the important reads beside the facts, then add suitable keys and indexes. Denormalize only after measurement identifies a real bottleneck and a refresh rule is defined.

Using Soft Deletes Without Lifecycle Rules

Rows reappear in queries or block unique values when filters and reuse rules are inconsistent. Centralize the active-row condition, decide whether deleted identifiers can be reused, and test restoration with related records.

A Schema Review Checklist

• Does each table own one kind of fact?
• Does every row have a stable primary key?
• Are business identifiers protected by appropriate unique constraints?
• Do foreign keys express required relationships and delete rules?
• Does each nullable column have a defined absent state?
• Do checks reject invalid quantities, states, and ranges?
• Do indexes follow measured joins, filters, and ordering?
• Can migrations preserve and verify existing data?
• Can the important view be reconstructed with expected results?

Frequently Asked Questions

What is database normalization?

Normalization organizes facts so each value has a clear owner and unnecessary duplication is removed. First normal form keeps column values atomic and singular. Second normal form removes dependencies on part of a composite key. Third normal form removes dependencies between non-key attributes.

Should every table have a generated ID?

Most entity tables benefit from a stable generated key, but relationship tables can use a meaningful composite primary key. Keep changeable business identifiers such as email, SKU, or slug under unique constraints instead of making every foreign key depend on them.

Should every foreign key have an index?

No automatic rule covers every foreign-key index. Index a foreign-key column when joins, parent deletes, updates, or common filters need it. Check the actual query plans and write cost. The referenced primary or unique key is indexed, but the referencing column may need a separate index.

When is denormalization reasonable?

Denormalization is reasonable when a measured read path needs a derived value or historical snapshot and the update rule is explicit. Keep a canonical source, document how the copy is refreshed, and test for drift. Do not duplicate data only to avoid learning joins.

What is the difference between a unique constraint and a unique index?

A unique constraint is a rule about the data; a unique index is the structure most engines build to enforce it. In practice they usually arrive together, but the constraint is what other tools read as intent, so declare that.

How do you rename a column without breaking a live application?

Expand then contract. Add the new column, write to both, backfill the old values, switch reads to the new one, and only then drop the old column. A direct rename breaks whichever deployed version is still using the old name.

Should every table have created_at and updated_at columns?

For anything you will later debug or report on, yes. They cost almost nothing and answer questions no other data can once something has gone wrong. Pure join tables and static lookup tables are reasonable exceptions.

Self-Check

1. What does this statement do against the coupon table's CHECK constraint?

   INSERT INTO coupons (code, discount_percent)
   VALUES ('TOO-MUCH', 140);

   A. Inserts a 100 percent discount. B. Inserts 140 unchanged. C. Fails because 140 is outside the allowed range.

   Answer: C. The CHECK constraint accepts values from 1 through 100, so the database rejects this row.

2. Why does product_name move from the raw order-line table to products?

   A. It depends on the product SKU. B. It depends on the order date. C. It depends on the quantity.

   Answer: A. The name describes the product rather than the complete order-and-product relationship.

3. What should happen before adding a foreign key to existing rows? Find and resolve child values that do not match a parent row, then verify the cleanup.
4. Why keep a generated key beside a unique email address? The generated key remains stable when the business identifier changes, while the unique constraint still prevents duplicate emails.
5. When is a composite index on customer and date justified? When an important query filters by customer and orders the matching rows by date, and its query plan confirms the index helps.

What to Read Next

Continue with SQL joins to query the relationships, then use SQL indexes and query optimization to inspect access paths. Read SQL transactions and ACID before implementing multi-table writes.