Database XP / Shape

Free text where a constrained set belongs

Status columns are where this happens most.

A status stored as free text will accumulate variants. Active, active with a trailing space, ACTIVE, Actve, and a value somebody used once during a migration. Every consumer then needs to know the full set, which nobody does, and queries silently miss rows.

The fix is a constraint or a reference table, and the objection is always that the set of values will change. It will, and adding a value to a constrained set is a deliberate change that everybody can see, which is exactly the property being sought.

The related problem is a column holding several things. A notes field that in practice carries a status for some rows, a reason for others, and a genuine note for the rest is unqueryable, and the knowledge of how to interpret it lives in one person.

Where a free text field genuinely is required, keeping it separate from anything structured is what prevents it from silently becoming a data field, which is how it always begins.

It is worth adding that a constrained set should live where it can be read. A check constraint listing permitted values is enforceable and invisible to anybody browsing the data; a reference table with descriptions is both enforceable and self documenting, and it gives reporting somewhere to join for a human-readable label.