Designing a schema

Key points

• Store each fact once. A city name repeated in a thousand rows is a thousand places to update.
• Foreign keys make the relationship explicit and let the database reject orphan rows.
• Choose the narrowest type that fits: INT over BIGINT, DATE over DATETIME when there is no time.
• Use DECIMAL for money, never FLOAT - binary floating point cannot hold 0.10 exactly.
• Denormalise only when a measured query is too slow, and accept you now maintain two copies.

Example

CREATE TABLE customers (
  id      INT AUTO_INCREMENT PRIMARY KEY,
  name    VARCHAR(120)  NOT NULL,
  city    VARCHAR(80)   NOT NULL,
  joined  DATE          NOT NULL
) ENGINE=InnoDB;

CREATE TABLE orders (
  id           INT AUTO_INCREMENT PRIMARY KEY,
  customer_id  INT            NOT NULL,
  product      VARCHAR(120)   NOT NULL,
  amount       DECIMAL(10,2)  NOT NULL,   -- money: never FLOAT
  status       ENUM('pending','shipped','cancelled') NOT NULL,
  FOREIGN KEY (customer_id) REFERENCES customers(id)
) ENGINE=InnoDB;

Constraints are the last line of defence: application code can forget, the database cannot.