Designing Good Schemas

Think Before You CREATE

A good schema makes data honest and queries easy. A poor one produces duplicates, contradictions and painfully slow reports.

Normalization Basics

• 1NF: columns hold atomic values; no lists inside cells.
• 2NF: all columns depend on the whole primary key.
• 3NF: columns depend on the key, nothing but the key.

Normalization removes duplicated and inconsistent data by separating concepts into their own tables.

A Healthy Example

CREATE TABLE customers (id INT PRIMARY KEY, name VARCHAR(100));
CREATE TABLE products  (id INT PRIMARY KEY, title VARCHAR(100), price DECIMAL(10,2));
CREATE TABLE orders (
  id INT PRIMARY KEY,
  customer_id INT REFERENCES customers(id),
  ordered_at  TIMESTAMP
);
CREATE TABLE order_items (
  order_id INT REFERENCES orders(id),
  product_id INT REFERENCES products(id),
  quantity INT NOT NULL DEFAULT 1,
  PRIMARY KEY (order_id, product_id)
);

No customer details live in orders, no product data in items - everything references clean master tables.

Denormalization on Purpose

For read-heavy analytics you sometimes store shortcut columns (like a cached total). Do it deliberately and document why, not by accident.

Key Points

• Normalize to eatduplicate or contradictory data.
• Refer to master data through foreign keys.
• Composite keys model many-to-many relationships.
• Denormalize only intentionally, for hot analytics paths.