Designing a Schema for a Real Application

Start from the questions, not the tables#

Before drawing anything, list what the app must answer: "show a customer's orders newest first", "find all unpaid orders older than 7 days", "total sales per day". Your schema and indexes exist to answer those questions quickly. A design that ignores them looks tidy and performs badly.

Conventions that save you later#

• Plural snake_case table names, singular column names: orders.customer_id.
• Every table gets a surrogate primary key (id BIGINT UNSIGNED AUTO_INCREMENT).
• Every table gets created_at and updated_at.
• Foreign keys named <parent_singular>_id.
• Constraint and index names with prefixes: fk_, uq_, idx_.

CREATE TABLE addresses (
  id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  customer_id INT UNSIGNED NOT NULL,
  line1       VARCHAR(200) NOT NULL,
  city        VARCHAR(80)  NOT NULL,
  postal_code VARCHAR(12)  NOT NULL,
  is_default  TINYINT(1)   NOT NULL DEFAULT 0,
  created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_addresses_customer (customer_id),
  CONSTRAINT fk_addresses_customer FOREIGN KEY (customer_id) REFERENCES customers (id)
);

Snapshot prices, don't join to them#

order_items.unit_price copies the product price at purchase time. If you looked up products.price instead, changing a price would rewrite history for old orders. Anything that must stay true about a past event gets copied into the event row. This is deliberate denormalization.

Soft deletes#

ALTER TABLE customers ADD COLUMN deleted_at DATETIME NULL;
SELECT * FROM customers WHERE deleted_at IS NULL;

Soft deletes keep history and allow undo, but every query must remember the filter, and unique keys get awkward (a deleted user's email still blocks a new signup). Wrap the filter in a view or a query helper. For data you must truly erase, such as privacy requests, hard delete.

JSON columns, used carefully#

ALTER TABLE products ADD COLUMN attributes JSON;
UPDATE products SET attributes = '{"color":"black","wireless":true}' WHERE id = 3;
SELECT name FROM products WHERE JSON_EXTRACT(attributes, '$.wireless') = TRUE;
SELECT name, attributes->>'$.color' AS color FROM products;

JSON fits flexible, rarely filtered attributes. It's poor for data you join on or filter heavily, since you lose column types, constraints, and plain indexes. If you query a JSON path often, add a generated column and index it:

ALTER TABLE products
  ADD COLUMN color VARCHAR(30) AS (attributes->>'$.color') STORED,
  ADD INDEX idx_products_color (color);

Keyset pagination#

-- page 1
SELECT id, name FROM products ORDER BY id LIMIT 20;
-- next page: pass the last id you saw
SELECT id, name FROM products WHERE id > 20 ORDER BY id LIMIT 20;

Unlike OFFSET, this seeks straight to the position through the index, no matter how deep the page. When sorting by a non-unique column, add the id as a tie-breaker: WHERE (created_at, id) < (?, ?) ORDER BY created_at DESC, id DESC.

Common mistakes#

• Looking up current prices for historical orders.
• Soft deletes without handling unique constraints.
• Storing relational data in JSON.
• Deep OFFSET pagination on big tables.

Practice#

Design tables for a booking app: users, rooms, bookings, and payments. Write the CREATE TABLE statements with keys, constraints, and timestamps. Add a unique rule preventing the same room being booked twice for the same start time, and write the keyset query for "my bookings, newest first, page after a given booking".