Database Documentation Best Practices for Engineering Teams

Good database documentation answers four questions for someone who did not build the system: what tables exist and how they relate, what each column means, why the schema is the way it is, and who owns what. The structure can and should be generated from the schema itself; the meaning, the decisions and the ownership have to be written by people, and they stay honest only when they live in the repository and change in the same pull request as the migration.

What to document#

Not everything about a database deserves prose. The list below is what experienced teams keep, roughly in order of how often it is read.

• Structure. Tables, columns, types, keys and relationships. This is the part that is generated, never typed.
• Meaning. What a row represents, what a column holds, the allowed values of a status column and what each means, units and time zones, and which columns are nullable on purpose.
• Invariants the schema cannot express. "An order has at least one item", "a payment never exceeds the order total", "soft-deleted rows are excluded from every report". If a constraint is enforced in application code, say so and say where.
• Data classification and retention. Which columns hold personal data, how long rows are kept, what anonymization or deletion applies. Auditors and incident responders read this first.
• Ownership and access. Which team owns each subject area, who may change it, which services read and write it.
• Decisions. Why identifiers are UUIDs, why a table is denormalized, why money is stored in cents. The reasoning outlives the people.
• Operations. Expected sizes and growth, heavy queries, partitioning, and the migrations that need care in production.

Where it lives#

In the repository that holds the migrations, and nowhere else as a primary copy. A wiki page is where documentation goes to become wrong, because nothing forces it to change when the schema does. Under the migrations directory, or next to it, keep a README that links to the generated structure, the data dictionary and the decision records. Documentation that ships in the same pull request as the migration is reviewed with it, versioned with it and rolled back with it.

For a schema shared by several services, the repository that owns the migrations owns the documentation; the consumers link to it. Two copies will disagree within a month.

How to produce each part#

Put the meaning in the database#

The single most effective habit is to write column and table descriptions as comments in the DDL, because every generator can then read them and every explorer shows them. PostgreSQL and Oracle use COMMENT ON; MySQL and MariaDB attach comments to the column or table definition; SQL Server uses extended properties.

COMMENT ON TABLE visits IS 'One row per visit of a pet to the clinic.';
COMMENT ON COLUMN visits.visit_date IS 'Calendar date of the visit in the clinic''s local time zone.';
COMMENT ON COLUMN visits.description IS 'Free-text notes written by the vet; may be empty.';

-- MySQL and MariaDB
ALTER TABLE visits
    MODIFY description VARCHAR(255) COMMENT 'Free-text notes written by the vet; may be empty.';

Comments travel with the schema through every environment, appear in generated dictionaries, and are diffed in the migration that changes them. Set a review rule that a new table or a new non-obvious column comes with a comment, and enforce it with a linter or a query against the catalog that fails the build when a comment is missing.

Generate the structure#

Anything that can be derived from the catalog should be. Two artifacts cover most needs.

The data dictionary lists every table and column with type, nullability, default, keys and comment. tbls generates it as Markdown from a database connection and can fail the build when the committed dictionary no longer matches the schema; SchemaSpy produces an equivalent HTML site. Both need a database to read, which in CI means running the migrations against a throw-away container first.

The diagram shows the tables and their relationships and is what people open first. Generate it from the migrations on every change and commit it, as described in keeping your database diagram up to date with CI. For schemas beyond a few dozen tables, generate one diagram per subject area and a small overview of how the areas connect. Generating an ER diagram from migration files compares the ways to produce it.

Record the decisions#

Schema decisions are architecture decisions, and the lightweight format that works for architecture works here: a short decision record per decision, numbered, stored under docs/decisions or next to the migrations. Context, options considered, decision, consequences, one page at most. Typical subjects: identifier strategy, soft delete versus hard delete, multi-tenancy layout, time zone handling, how enumerations are stored, when to denormalize. Write the record in the pull request that introduces the change, while the reasoning is fresh, and link it from the table's comment or the README.

Review documentation with the migration#

A pull request that adds a migration should be reviewable as a unit: the migration, the regenerated diagram and dictionary, updated comments, and a decision record if a decision was made. A short checklist keeps it consistent without ceremony.

1. Every new table and non-obvious column has a comment.
2. Every relationship is a real foreign key, or the record says why not.
3. Naming follows the team's conventions.
4. Personal data is marked and its retention is stated.
5. The generated artifacts changed in the way the migration implies, and nothing else changed.

The last item is where generated diagrams pay off in review: a foreign key the author forgot shows up as a missing arrow, and a table dropped by mistake shows up as a missing box.

Keep it honest with automation#

Documentation stays true when something fails if it is not. Three checks are cheap.

• Drift check. Compare the schema the migrations produce with the schema a live environment has, on a schedule. Differences mean a manual change happened outside the migrations; schema drift is a documentation problem before it becomes an outage.
• Freshness check. Fail the build when the committed dictionary or diagram differs from a freshly generated one, or have the pipeline regenerate and open a pull request.
• Comment coverage. A query against the catalog that lists tables and columns without a comment, run in CI, with a threshold that only goes up.

A minimal setup you can adopt this week#

1. Add a README next to the migrations with the owner, a link to the diagram and a link to the dictionary.
2. Add a pipeline that regenerates the diagram from the migrations when they change and opens a pull request, using the SchemaDrawer GitHub Action or a self-hosted generator.
3. Start commenting new tables and columns in the DDL; backfill the ten most-read tables.
4. Write one decision record for the next schema decision you make, and put the template in the repository.
5. Add the review checklist to the pull request template.

Where SchemaDrawer fits#

SchemaDrawer generates the structural part of the documentation, the diagram, straight from the migration files of Flyway, Liquibase, Prisma, Django and 18 other tools. It runs the migrations in Docker against a throw-away database of the engine and version you choose, reads the schema back and returns an SVG with tables, columns, primary keys and foreign keys, deterministic enough to commit and diff. It does not produce a data dictionary and it does not read comments, so it pairs with tbls or hand-written descriptions for the meaning. It is free today; the getting started guide shows the two API calls.

Key takeaways#

• Generate structure, write meaning, record decisions, name owners.
• Keep it all in the repository with the migrations, and change it in the same pull request.
• Comments in the DDL are the cheapest documentation that never gets lost.
• Automation that fails when documentation is stale is the only thing that keeps it fresh.