Designing good tables

Data types: the vocabulary

A data type is a promise the engine enforces on every insert. Changing it later on a full table is expensive, so recognize the families.

Numbers and money

Integers are cheap; size is the only decision. Money must be DECIMAL, because most decimal fractions have no exact binary form and pennies drift:

-- MySQL: an e-notation literal is a DOUBLE
SELECT 0.1e0 + 0.2e0;                 -- 0.30000000000000004
-- PostgreSQL: cast to double precision
SELECT 0.1::float8 + 0.2::float8;     -- 0.30000000000000004
-- DECIMAL / numeric gives exactly 0.30

Dates and times

A MySQL TIMESTAMP is converted from the session's time zone to UTC on the way in and back on the way out. A DATETIME is stored as typed:

SET time_zone = '+04:00';            -- this session thinks in Dubai time
UPDATE orders
   SET paid_at = '2026-03-01 09:00:00', paid_local = '2026-03-01 09:00:00'
 WHERE id = 13;
SET time_zone = '+00:00';            -- same session, now UTC
SELECT paid_at, paid_local FROM orders WHERE id = 13;

PostgreSQL splits the same way: timestamptz stores UTC, timestamp ignores time zones. Neither has a 2038 cliff.

Constraints: rules the engine enforces for you

A constraint is checked on every INSERT and UPDATE, whichever application sends the statement. Bad rows that get in stay until someone cleans them up.

CREATE TABLE orders (
  id          INT AUTO_INCREMENT PRIMARY KEY,
  customer_id INT NULL,                       -- NULL = guest checkout
  order_date  DATE NOT NULL,
  status      ENUM('pending','paid','cancelled') NOT NULL DEFAULT 'pending',
  total       DECIMAL(10,2) NOT NULL DEFAULT 0,
  CONSTRAINT fk_orders_customer FOREIGN KEY (customer_id)
    REFERENCES customers(id) ON DELETE SET NULL
) ENGINE=InnoDB;

What a foreign key does when the parent disappears

The referential action says what happens to child rows when the parent is deleted.

DELETE FROM customers WHERE id = 1;          -- goodbye Amira
SELECT id, customer_id, status, total FROM orders WHERE id IN (10, 11);

MySQL enforces foreign keys only on InnoDB tables; PostgreSQL can declare one DEFERRABLE so the check waits until COMMIT. Every foreign key column needs an index: InnoDB creates one, PostgreSQL does not.

Generated ids

Most tables need an id nobody has to invent (MySQL vs PostgreSQL collects the renamed words).

-- MySQL
CREATE TABLE customers (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100) NOT NULL);
INSERT INTO customers (name) VALUES ('Eva');
SELECT LAST_INSERT_ID();                        -- 5
-- PostgreSQL
CREATE TABLE customers (id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY, name text NOT NULL);
INSERT INTO customers (name) VALUES ('Eva') RETURNING id;   -- 5

What a UUID costs

A UUID can be generated anywhere without asking the database. It costs 16 bytes instead of 4, and a random version-4 value lands anywhere in the index, which in InnoDB is the table itself, so every insert touches a different page. Version 7 puts the timestamp in the leading bits, so new rows land at the end.

Normalization, shown on the shop

Normalization puts each fact in exactly one place, so it cannot disagree with itself. Most schemas, the shop included, stop at third normal form.

orders.total breaks the rules on purpose: storing it means a list of orders needs no join. The cost is drift, so denormalize only for a measured slow query, and write down what keeps the copy in sync (application code, a trigger from chapter 12, or a nightly rebuild).

Relationships and the ER diagram

Tables relate in three shapes (chapter 1 introduced them).

Many-to-many is the shape people forget, so it helps to have a second example that is not an invoice. Films and actors: La La Land has many actors, and J.K. Simmons is in both La La Land and Whiplash. Neither fact fits in a column on either table, so a junction table holds the pairs, and the role belongs to the pairing rather than to the film or to the person.

CREATE TABLE cast_members (
  movie_id INT NOT NULL REFERENCES movies(id),
  actor_id INT NOT NULL REFERENCES actors(id),
  role     VARCHAR(100) NOT NULL,
  PRIMARY KEY (movie_id, actor_id)
);

An entity-relationship diagram draws these shapes in crow's-foot notation, which Workbench uses.

Naming, character sets and collations

Consistent names let you and the next DBA guess a column without opening the schema.

Two habits bite people who switch. PostgreSQL folds unquoted identifiers to lowercase, so a table created as "Customers" needs quotes forever. MySQL on Windows runs with lower_case_table_names=1, chosen only at initialization (see Install MySQL on Windows), so a Linux dump holding both Customers and customers will not load. Lowercase everything.

Character set and collation

A character set says which characters a column can store; a collation says how they compare and sort. MySQL 8 and later default to utf8mb4_0900_ai_ci: Unicode 9.0 rules, accent-insensitive, case-insensitive. PostgreSQL compares text exactly, so the same query gives different answers:

SELECT id, name FROM customers WHERE name = 'chloe';

For exact matching MySQL has utf8mb4_0900_as_cs and utf8mb4_bin; PostgreSQL has ILIKE, lower(), citext and ICU collations. Know which your server does before a UNIQUE constraint on email surprises you.

Changing tables that already have data

Both engines use ALTER TABLE; what differs is what happens underneath while the application keeps running. MySQL calls it online DDL and you steer it with ALGORITHM.

-- metadata only, milliseconds on any size of table
ALTER TABLE customers ADD COLUMN phone VARCHAR(30) NULL, ALGORITHM=INSTANT;
-- a type change copies the whole table
ALTER TABLE orders MODIFY total DECIMAL(12,2) NOT NULL DEFAULT 0, ALGORITHM=COPY;
-- asking for INSTANT where it is impossible fails fast instead of surprising you
ALTER TABLE orders MODIFY total DECIMAL(12,2), ALGORITHM=INSTANT;   -- ERROR 1845

Name the algorithm you expect, so the server refuses instead of silently copying. Every ALTER takes a metadata lock, so it queues behind any long-running query on that table and every new query queues behind it: the application appears to hang. A COPY reruns on each replica, so replication lag grows with it (see Backup, restore and replication).

PostgreSQL DDL is transactional, so an ALTER TABLE inside BEGIN ... ROLLBACK is undone completely. MySQL's DDL is atomic but commits the current transaction and cannot be rolled back (see Transactions and concurrency). Most PostgreSQL forms take an ACCESS EXCLUSIVE lock, so set lock_timeout and build indexes with CREATE INDEX CONCURRENTLY.

Schema changes should arrive as migrations: versioned SQL files applied in order by a tool that records what it ran, such as Flyway or Liquibase. Read the one in the pull request, estimate lock and disk, take a backup, run it in a window.

Where to read more

• MySQL 8.4: Data Types

  Every type, size and range.

• PostgreSQL: Data Types

  The same list, plus arrays and jsonb.

• MySQL: FOREIGN KEY Constraints

  Referential actions in detail.

• MySQL: Online DDL Operations

  Which ALTER is which algorithm. Bookmark it.

• PostgreSQL: ALTER TABLE

  Which forms rewrite and lock.