When Field Meaning Becomes Ambiguous
Columns are created with a specific intent, but over time that intent erodes. A status field gains new values, a nullable column starts storing different types of data, and a foreign key begins pointing to unexpected records.
These review cases illustrate common patterns of schema drift. Each case includes the warning signs, the root cause, and the questions that a schema-intent review should surface.
Common Review Cases
Recognise these patterns in your own schema.
Case 1: Status Field Proliferation
Warning signs: A status column that started with 3 values now has 12. No documentation explains when each value was added or who uses them.
Root cause: Multiple teams added status values over time without coordinating. The column became a shared flag with no single owner.
Review questions: Who owns this status field? Are all values still in use? Can any be deprecated? Should the status be split into separate columns?
Case 2: The Nullable That Should Not Be
Warning signs: A column declared NOT NULL at creation is now nullable. Some rows contain NULL where the application expects a value.
Root cause: A migration relaxed the constraint to accommodate a new feature, but the application was not updated to handle NULL.
Review questions: Why was the constraint relaxed? What does NULL mean in this context? Should the column be split or should the application handle NULL explicitly?
Case 3: Orphaned Foreign Key
Warning signs: A foreign key column contains values that do not match any row in the referenced table. Queries that join on this key silently drop rows.
Root cause: The constraint was never enforced, or it was dropped during a migration and never restored.
Review questions: How many orphaned rows exist? Should they be cleaned up or archived? Can the constraint be safely re-enabled?
Case 4: The Overloaded Column
Warning signs: A single column stores different types of data depending on the row. A "metadata" JSON column has become a dumping ground for unrelated fields.
Root cause: Teams used the flexible column as a shortcut instead of proposing schema changes through the review process.
Review questions: What distinct data shapes exist in this column? Should any be promoted to dedicated columns? Who owns each data shape?
Conducting a Field Ambiguity Review
Follow these steps to review fields that may have drifted from their original intent:
- Inventory: List all columns in the table and their original documented purpose.
- Sample: Query a random sample of rows and check whether the data matches the documented intent.
- Interview: Ask the current data owner and consumers whether the column still serves its original purpose.
- Flag: Mark columns where the actual data does not match the documented intent.
- Decide: For each flagged column, decide whether to update the documentation, clean the data, or restructure the schema.
- Document: Record the review outcome and update the ownership and purpose statements.
Found a case in your own schema?
Prepare your findings for handoff with the right documentation.