From Schema to ProductionLesson 6.4

Schema documentation: writing a data dictionary

data dictionary definition, column-level comments, table-level comments, documentation tools, dbdocs.io, README schema docs, naming conventions documentation, onboarding value

What Is a Data Dictionary?

A data dictionary documents every table, column, data type, constraint, and business meaning in your schema. It is the Rosetta Stone for new developers joining the team — they should be able to understand the schema without asking anyone.

SQL-Level Comments

-- MySQL / PostgreSQL table comment
ALTER TABLE orders
  COMMENT = 'Customer purchase orders. Soft-deleted via deleted_at.';

-- Column comments (MySQL)
ALTER TABLE orders
  MODIFY COLUMN status VARCHAR(20) NOT NULL DEFAULT 'pending'
  COMMENT 'Order lifecycle: pending > confirmed > shipped > delivered | cancelled';

ALTER TABLE orders
  MODIFY COLUMN total DECIMAL(10,2) NOT NULL
  COMMENT 'Snapshot of sum(order_items.unit_price * quantity) at time of order creation. Not recalculated after edits.';

Tooling

dbdocs.io — write schema docs in DBML, generate beautiful HTML docs.
SchemaSpy — auto-generates ER diagrams and column docs from a live database.
README in migrations folder — a short decision log explaining why major structural choices were made.

The best schema documentation is the schema itself: clear table names, clear column names, ENUM values that need no explanation, and inline SQL comments for anything that is not obvious. Documentation that lives outside the codebase rots; comments in SQL files get reviewed in PRs.