How to run a database schema review, with a checklist

A bad line of code is fixed with another deploy. A bad column is fixed with a migration, a backfill, a coordinated release, and an apology to whoever built a dashboard on it. Schema mistakes are the expensive kind, which makes the schema the part of a system most worth reviewing before it exists, and usually the part reviewed least: it arrives as a migration file in the middle of a large pull request, and the reviewer reads the code around it.

This is a process for reviewing schema changes as a team, followed by a 28-point checklist to review against.

When a schema change needs a review

Not every change does. Adding an index is rarely worth a meeting. Review when a change adds a table, adds or alters a relationship, changes a type or a constraint on data that already exists, or removes anything. A fair rule: if undoing it would need a migration of its own, someone else should look first.

The process

1. Review the design, not the migration

A migration says what will change. It does not show what the schema will be afterwards, and a reviewer cannot judge a new table without seeing the tables around it. Review the change as a before and after of the schema, on a diagram, and derive the migration once the design is agreed.

2. Let a tool find the mechanical problems first

People should not spend a review spotting a missing primary key. Run the proposed schema through a linter before anyone reads it. The database schema checker flags missing primary keys, foreign keys with no index, mismatched key types, and money stored in floats, which clears a good part of the checklist below before the review starts.

3. Propose it in writing, with the reason

The author writes the change as a proposal: the new version of the schema and two or three sentences on what it is for and what it will be queried by. “Add cancelled_at” cannot be reviewed. “Add cancelled_at so finance can report cancellations by week” can, because now the reviewer knows to ask about the index.

4. Review asynchronously, on the diagram

Reviewers read the proposal in their own time and comment on the specific table they have a question about. Async review gets more careful answers than a meeting and leaves a record. Meet only when the comments show a real disagreement, and then meet with the diagram open so people can point.

5. One person decides, and it is not the author

Name who approves schema changes for each area. Consensus among five people produces either no decision or the design nobody objected to.

6. Merge, then generate the migration

Once approved, the design becomes the schema, and the migration is the difference between the old one and the new one. Generating it from the two versions, with the SQL migration generator or your ORM, means what was reviewed is what runs.

In DB Planner these steps are one flow: a proposal shows its changes against the current documents, teammates comment on the tables, someone other than the author merges it, and a merge is refused if the schema moved on in the meantime. The setup is in building an ER diagram with your team.

The checklist

28 questions in 8 groups. No schema needs a perfect score; a reviewer needs to have asked.

Naming

  • Tables and columns follow one convention: one case, and either all singular or all plural.
  • A foreign key is named for what it points at (user_id, not uid or owner).
  • No column name needs its table name to be understood (status of what?), and none repeats it (orders.order_total).
  • Booleans read as a yes or no question (is_active, has_shipped).

Keys

  • Every table has a primary key.
  • The key type is the same everywhere it is referenced: a bigint key is not pointed at by an integer column.
  • A natural key that must be unique (email, SKU, slug) has a unique constraint, even when a surrogate id is the primary key.
  • A join table has a key that prevents the same pair being inserted twice.

Types

  • Money is an exact type, a decimal or integer minor units, never a float.
  • Timestamps record a time zone, or the team has written down that everything is UTC.
  • String lengths are a decision, not a default of 255.
  • A column with a fixed set of values is an enum or a lookup table, not free text.

Relationships

  • Every foreign key column has a declared reference, not just a matching name.
  • Each relationship has the right cardinality: a one-to-one is enforced by a unique constraint, a many-to-many has a join table.
  • What happens on delete is chosen for each reference: cascade, restrict, or set null.
  • Nothing is stored twice that can be reached by a join, unless the copy is deliberate and noted.

Constraints and nullability

  • Every column is NOT NULL unless there is a reason for it to be empty, and the reason is known.
  • Defaults exist where an insert would otherwise have to guess.
  • Rules the application depends on (a positive quantity, an end after a start) are check constraints, not only code.

Indexes

  • Every foreign key that is joined or filtered on has an index.
  • The queries this change exists to serve have an index that fits them, with columns in the order the query uses.
  • No index duplicates the leading columns of another.

Migration safety

  • A new NOT NULL column on an existing table has a default or a backfill plan.
  • A rename is deployed in steps the running application survives: add, copy, switch, drop.
  • Anything that drops a column, a table, or narrows a type names where the data goes.
  • The change can be rolled back, or the team has agreed that it cannot.

Documentation

  • Any column whose meaning is not obvious from its name has a note.
  • The reason for an unusual decision is written beside it, not in the reviewer’s memory.

What reviewers miss most often

  • The second query. A design is reviewed against the feature that prompted it. Ask what else will read this table in a year: reporting, exports, support tooling.
  • Nullable by default. A column nobody thought about is a column that allows NULL, and every reader of it then has to handle a case that was never meant to exist.
  • Renames in disguise. A column that disappears while a similar one appears might be a rename or might be a drop and an add, and the two migrate very differently. Make the author say which.
  • The relationship that is really many-to-many. “A user has one team” is true until the first customer asks for two. See one-to-many vs many-to-many for the test.

Keeping it light

A review process survives only if it is cheaper than the mistakes it prevents. Keep proposals small, one change each. Let the linter take the mechanical items so people read for meaning. And review where the schema already is, so nobody has to rebuild the picture in their head from a migration file before they can have an opinion.