Database XP / Over time

Document the meaning, not the structure

The column names are visible. What they mean is not.

Anybody can read the schema and see the tables and types. What cannot be recovered is what the values mean: what distinguishes two similar statuses, why this date is sometimes in the future, which rows are historical artefacts of a migration, and what the odd convention in the third column is for.

That knowledge is held by people and leaves with them. A short document, per table, saying what it is for and listing the non-obvious points, is among the highest-value artefacts in a long-lived system and takes an afternoon to write.

Comments in the database itself are better than a separate document, because they cannot be separated from the thing they describe and they survive a migration to new infrastructure. Most databases support them and almost nobody uses them.

The parts most worth documenting are the exceptions: the rows that behave differently, the values that mean something historical, and the assumptions that a query must respect to produce a correct answer.

It is worth adding that the most valuable documentation is written by whoever asks the question rather than by whoever knows the answer. A new colleague working out what a column means writes a better note than the person who has always known, because they record the thing that was confusing rather than the thing that seems obvious.