Schema Design Patterns & Keys

Normalisation tells you which tables to create, and ER diagrams show how they connect. Real projects then raise smaller questions that the textbooks skip. Should the key be a number or a UUID? Should a status be text, an ENUM or a separate table? How do you remember who changed an order and when? What changes when one database serves many shops?

This lesson collects the patterns that working developers use for these questions, and the patterns to avoid. It builds on Constraints & Keys, Normalisation & Table Design and ER Diagrams & Relationships.

Choosing a primary key type

Most tables use a surrogate key, a meaningless ID. In MySQL you have two common choices:

InnoDB stores each table in primary key order (see Indexes & EXPLAIN), and every secondary index stores a copy of the primary key. An increasing number therefore keeps inserts at the end of the table and keeps indexes small. Random UUIDs scatter inserts across the whole table and make every index bigger. For most applications, AUTO_INCREMENT is the right default:

CREATE TABLE invoices (
  invoice_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  ...
);

Use BIGINT for tables that can grow large. A signed INT stops at 2,147,483,647, and running out of IDs on a busy table is a real outage.

If you need UUIDs, for example because mobile apps create records offline, store them compactly. MySQL's UUID() returns a version 1 UUID, and UUID_TO_BIN(uuid, 1) rearranges its time parts so that new values sort after old ones:

CREATE TABLE devices (
  device_id BINARY(16) PRIMARY KEY,
  owner_name VARCHAR(80) NOT NULL
);

INSERT INTO devices (device_id, owner_name) VALUES (UUID_TO_BIN(UUID(), 1), 'Kabir');

SELECT BIN_TO_UUID(device_id, 1) AS device_id, owner_name FROM devices;

Pass the same flag to BIN_TO_UUID that you used with UUID_TO_BIN, or the text you get back is scrambled. Applications can also generate time-ordered version 7 UUIDs, defined in RFC 9562, which have the same benefit.

Internal IDs and public IDs

Showing /invoice/115 in a URL tells everyone how many invoices you have issued, and invites people to try /invoice/116. Access checks must stop that anyway, but many teams keep two identifiers: the number as the internal primary key, used in joins, and a random public ID for URLs, emails and APIs:

CREATE TABLE orders_v2 (
  order_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,   -- joins and foreign keys
  public_code CHAR(12) NOT NULL UNIQUE,                  -- e.g. 'K7Q2M9XA4D1P', made by the app
  customer_id BIGINT UNSIGNED NOT NULL,
  placed_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);

Natural keys, real-world identifiers such as an email address, a PAN, a GSTIN or a vehicle number, belong in a UNIQUE column rather than in the primary key. They can change (people change email), they can be entered wrongly and corrected, and every foreign key that copied them would have to change too.

Lookup tables instead of magic values

The status column in orders holds plain text such as 'shipped'. Nothing stops a typo like 'shiped'. You have three ways to restrict the values: a CHECK constraint, an ENUM (see MySQL Data Types), or a lookup table (also called a reference table) that lists the allowed values, with a foreign key pointing at it. The lookup table can also hold a display label, a sort order and flags:

CREATE TABLE order_statuses (
  status_code VARCHAR(20) PRIMARY KEY,
  label VARCHAR(40) NOT NULL,
  is_final INTEGER NOT NULL,
  sort_order INTEGER NOT NULL
);
INSERT INTO order_statuses (status_code, label, is_final, sort_order) VALUES
  ('pending', 'Waiting to be packed', 0, 1),
  ('shipped', 'On the way', 0, 2),
  ('delivered', 'Delivered', 1, 3),
  ('cancelled', 'Cancelled', 1, 4);

SELECT s.label, s.is_final, COUNT(o.order_id) AS orders
FROM order_statuses s
LEFT JOIN orders o ON o.status = s.status_code
GROUP BY s.status_code, s.label, s.is_final, s.sort_order
ORDER BY s.sort_order;

label                | is_final | orders
---------------------+----------+-------
Waiting to be packed | 0        | 2
On the way           | 0        | 2
Delivered            | 1        | 10
Cancelled            | 1        | 1
(4 rows)

Adding a new status, such as 'returned', is now an INSERT rather than a change to the table structure, and reports show friendly labels in a sensible order instead of alphabetical codes. In MySQL you would add FOREIGN KEY (status) REFERENCES order_statuses (status_code) to orders so typos are rejected. Use a CHECK for small lists that never change, such as CHECK (quantity > 0), and a lookup table for lists that the business may extend.

History tables: who changed what, and when

orders.status holds only the current status. When a customer complains "my order was stuck for three days", you need the history. Do not overwrite it: append a row to a history table every time the status changes, and read the current value from the latest row:

CREATE TABLE order_status_history (
  history_id INTEGER PRIMARY KEY,
  order_id INTEGER NOT NULL,
  status VARCHAR(20) NOT NULL,
  changed_by VARCHAR(40) NOT NULL,
  changed_at DATETIME NOT NULL
);
INSERT INTO order_status_history (history_id, order_id, status, changed_by, changed_at) VALUES
  (1, 108, 'pending', 'website', '2025-03-15 10:02:00'),
  (2, 108, 'shipped', 'Rahul', '2025-03-17 16:40:00'),
  (3, 111, 'pending', 'website', '2025-04-10 12:15:00'),
  (4, 115, 'pending', 'website', '2025-05-20 20:05:00');

SELECT order_id, status, changed_by, changed_at
FROM (
  SELECT h.*,
         ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY changed_at DESC) AS pos
  FROM order_status_history h
) AS latest
WHERE pos = 1
ORDER BY order_id;

order_id | status  | changed_by | changed_at
---------+---------+------------+--------------------
108      | shipped | Rahul      | 2025-03-17 16:40:00
111      | pending | website    | 2025-04-10 12:15:00
115      | pending | website    | 2025-05-20 20:05:00
(3 rows)

The history shows that order 108 waited two days before Rahul shipped it. Many systems keep both: the current value in orders.status for fast lookups, and the full trail in the history table. A trigger or the application code writes both in one transaction so they never disagree (see Stored Procedures & Triggers).

The same idea gives an audit table for sensitive data such as salaries or bank details: every change records the old value, the new value, who made it and when. Add created_at, updated_at and often created_by columns to every important table as well; they cost little and answer many "when did this happen?" questions.

One database, many businesses

Software sold to many businesses, such as a billing app used by hundreds of shops, often keeps all of them in one database. This is called multi-tenant design, and each customer business is a tenant. Every table gets a tenant column, and that column must appear in every unique key and most indexes:

CREATE TABLE tenant_invoices (
  invoice_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  shop_id INT UNSIGNED NOT NULL,
  financial_year CHAR(7) NOT NULL,          -- '2025-26'
  invoice_no INT UNSIGNED NOT NULL,
  amount DECIMAL(12,2) NOT NULL,
  UNIQUE KEY uq_invoice_number (shop_id, financial_year, invoice_no),
  KEY idx_shop_year (shop_id, financial_year)
);

Invoice number 1 can exist once per shop per financial year, not once in the whole database. The biggest risk in this design is a query that forgets WHERE shop_id = ? and shows one shop's data to another, so many teams enforce the filter in one central place in their code.

Patterns to avoid

• Entity-attribute-value (EAV) tables. A table with columns entity_id, attribute, value can store "anything", but every value becomes text, constraints are impossible, and a simple report needs one join per attribute. For a few flexible extra fields, use a JSON column; for fields you query often, use real columns.
• Polymorphic foreign keys. A comments table with parent_type and parent_id that can point at a product or an order cannot have a real foreign key. Prefer separate link tables, such as product_comments and order_comments.
• Comma-separated lists in one column. '3,7,9' breaks first normal form; use a junction table.
• Copying the same column into many tables "for speed". Denormalise only after you have measured a slow query, and write down which table is the source of truth.

Key points

• Prefer BIGINT UNSIGNED AUTO_INCREMENT keys; if you need UUIDs, store them as BINARY(16) with UUID_TO_BIN(uuid, 1) or use time-ordered UUIDs.
• Keep a random public ID for URLs, and put natural keys such as email or GSTIN in UNIQUE columns.
• Use lookup tables for value lists the business may extend; use CHECK for fixed rules.
• Append status changes to a history table instead of overwriting them; read the latest row with ROW_NUMBER().
• In multi-tenant tables, include the tenant column in every unique key and index.
• Avoid EAV tables, polymorphic foreign keys and comma-separated lists.

CREATE TABLE price_history (
  change_id INTEGER PRIMARY KEY,
  product_id INTEGER NOT NULL,
  old_price DECIMAL(10,2) NOT NULL,
  new_price DECIMAL(10,2) NOT NULL,
  changed_by VARCHAR(40) NOT NULL,
  changed_at DATETIME NOT NULL
);
INSERT INTO price_history (change_id, product_id, old_price, new_price, changed_by, changed_at) VALUES
  (1, 1, 799.00, 749.00, 'Simran', '2025-05-01 10:00:00'),
  (2, 1, 749.00, 699.00, 'Neha', '2025-06-01 09:30:00'),
  (3, 4, 1899.00, 1799.00, 'Simran', '2025-05-15 11:00:00');

SELECT p.product_name, h.old_price, h.new_price, h.changed_by, h.changed_at
FROM (
  SELECT ph.*,
         ROW_NUMBER() OVER (PARTITION BY product_id ORDER BY changed_at DESC) AS pos
  FROM price_history ph
) AS h
JOIN products p ON p.product_id = h.product_id
WHERE h.pos = 1
ORDER BY p.product_name;