Another column for every new requirement: where did the data model go wrong?

A data model is your business written down as tables. When it is wrong, nothing throws an error. The symptoms show up somewhere else: every new requirement needs another column, every report needs another exception, every fix breaks a screen nobody was looking at. The root cause usually sits in one of three places. You stored a snapshot of a concept instead of the concept itself, you moved the rules out of the database and into application code because that felt more flexible, or you never modelled time at all. The good news is that most of this is fixable without a rewrite. The bad news is that the cost of fixing it grows every month you leave it.

The bill for a bad model does not arrive as storage cost

Data model discussions tend to die on the sentence "disk is cheap". That was never where the bill lands. A customer record that can be created twice becomes a support ticket. A status column holding seventeen different free-text values makes a monthly report wrong. A table with no history means nobody can answer why an invoice was issued for that amount.

All of these look like performance problems first. Before you go hunting for slow queries, ask one question: is this query complicated because it is compensating for the model? A report that joins four tables and then regroups the rows in application code is rarely an index problem.

Storing "status" as free text

Order status, ticket status, subscription status. These columns start as free text, and within a couple of years the same table holds Pending, pending, PENDING and a fourth variant with a trailing space. You have three options, and the choice depends on who gets to change the values.

If the set is closed and changes once or twice a year, a database enum works. On PostgreSQL, know two limits up front. As the documentation states, when ALTER TYPE ... ADD VALUE runs inside a transaction block the new value cannot be used until that transaction commits. And there is no way to remove a value from an enum at all. The supported operations are adding and renaming.

If the business side will manage the values, or if a value carries its own attributes (display name, sort order, active flag, translations), a lookup table with a foreign key is the right answer. The third option is not to store status in a single column at all. For many entities, the thing people actually need is the transition history: when the order was approved, who cancelled it, in what order it moved. Keeping that in an audit trail table and deriving the current state from it answers half the questions you will be asked later.

NULL means three different things at once

You see a NULL in a column. Does it mean "unknown", "not applicable to this record", or "not entered yet"? Those produce different business rules, and they look identical in the database. A corporate customer has no date of birth; an individual customer might simply not have filled it in. If you cannot tell them apart, you cannot write a required-field check that holds.

Two NULL behaviours catch teams out. First, as the PostgreSQL docs say plainly, a CHECK constraint is satisfied when the expression evaluates to true or NULL. So CHECK (price > 0) happily accepts a NULL price. If you want the check to mean what you think it means, you have to add NOT NULL separately.

Second, uniqueness. Under the default behaviour two NULLs are not considered equal, so a UNIQUE column can accumulate as many NULL rows as you like. PostgreSQL 15, released on 13 October 2022, added UNIQUE NULLS NOT DISTINCT to change that. Note that the default is implementation-defined, and the PostgreSQL documentation itself warns about portability here.

A working rule: reserve NULL for "we do not know yet". When a field is "not applicable to this type of record", that is a modelling signal. You have probably squeezed two different entities into one table.

If the rule is not in the database, someone will eventually route around it

"We validate in the application" holds as long as there is exactly one write path. In practice the writers multiply: the admin panel, the nightly import job, a one-off data fix script, an integration service, and that one UPDATE somebody ran by hand in production. Foreign keys, UNIQUE and CHECK constraints are the only layer all of those paths go through.

If adding a constraint to a large table sounds frightening, there is a two-step route. In PostgreSQL you can add the constraint NOT VALID, which enforces it for new rows without scanning the existing ones, and validate it later with VALIDATE CONSTRAINT. As the documentation explains, the validation step only takes a SHARE UPDATE EXCLUSIVE lock, because new rows are already covered by the constraint and only pre-existing rows need checking. That means you can start cleaning up without taking the table offline.

The key-value table somebody opened "for flexibility"

The attribute_name and attribute_value table, created because every customer needs different fields, is the classic trap Bill Karwin documents as Entity-Attribute-Value in SQL Antipatterns. The first edition came out in 2010 and the pattern shows up just as often today.

Here is what it costs. Values are stored as text, so there is no type checking and a date field can contain the word "tomorrow". You cannot apply NOT NULL, because the column is shared by every attribute. You cannot apply a foreign key. And every report turns into a pivot that converts rows back into columns. If you have ever seen a table self-joined ten times to render a ten-field screen, this is why.

There is one legitimate case: your product genuinely supports customer-defined fields. Even then, pair the storage with a metadata table that defines each field (its type, whether it is required, which tenant owns it) and validate against that definition. In a multi-tenant product, if you do not draw that line early, customer-specific fields and core product fields end up tangled together.

JSON columns: data you store, not data you query

JSON support in modern databases is genuinely useful, and the test for using it is simple. A JSON column is right for data you write whole and read whole: the raw response from a third-party service, the exact contents of a form at submission time, optional settings. It is wrong for data you filter, join and aggregate on.

This is not a matter of taste, it shows up in indexing. PostgreSQL gives you GIN indexes over jsonb. In MySQL you cannot index a JSON column directly: as the manual explains, you extract the value into a generated column and build a functional index on it, and for array fields you define a multi-valued index using the CAST(... AS ... ARRAY) syntax. Multi-valued indexes arrived in MySQL 8.0.17 on 22 July 2019, and they come with limits worth knowing: they cannot act as a covering index, they do not support range scans, and they cannot be used in a foreign key.

The practical rule: the day a field inside your JSON shows up in the WHERE clause of a report, promote it to a real column.

Key choice: a tax number is not a primary key

Natural keys look tempting. Tax number, email address, the product code that comes from the ERP. All three change. Companies merge, customers update their email, the ERP revises its coding scheme. When your primary key changes, every foreign key pointing at it has to change too.

The practical approach: use a meaningless surrogate key for relationships, and keep the natural key in its own column with a UNIQUE constraint. The business rule still holds, but your relationships are not hostage to it.

If you need distributed or client-side generated identifiers, choose the UUID version deliberately. The UUID specification was updated in May 2024 by RFC 9562, which obsoletes RFC 4122. The RFC is explicit about why random UUIDs hurt: values created in succession are not close to each other in the index, so inserts land at random locations. UUIDv7 puts a Unix timestamp in the most significant bits, which keeps new values near each other and preserves index locality. For database keys, that is the version the RFC points you to.

Today's value and yesterday's value cannot share a row

The most expensive modelling mistake is forgetting time. If the customer's address lives on the customer table and the order only stores a customer id, then the day that address changes, last year's invoice starts showing the new one. Price, tax rate, discount, company name: same problem, every time.

The fix is not to abandon normalisation, it is to separate two different questions. "What is this customer's current address" and "what address was this order billed to" are not the same question. The answer to the second belongs next to the order, frozen as it was at the time. That is a snapshot, and it is deliberate duplication.

The same separation applies between when a record was entered and when it takes effect. An exchange rate may have been entered yesterday evening while being valid for today. This is where date and timezone handling and the rules for storing money and rates meet.

Deleting is a modelling decision too

Most teams use a deleted flag or a deleted_at column instead of a real delete. Reasonable, with two conditions. First, every query written from that point forward has to include the filter, and one day somebody will forget. A view that returns non-deleted rows by default takes most of that risk away.

Second, uniqueness. A user who wants to recreate a deleted record with the same email hits an error, because the UNIQUE constraint covers the whole column. The fix is a partial index: enforce uniqueness only for rows where the deletion timestamp is empty.

Retention is part of the model as well. For some record types, how long you must keep or must delete data can come from regulation; do not guess that period on the engineering side, settle it with legal and write it into the table as policy.

Changing the schema is cheaper than you think, unless you do it in one step

Teams usually avoid fixing the model because they expect the table to lock. On PostgreSQL that fear is mostly outdated. The ALTER TABLE documentation is clear: when you add a column with a non-volatile DEFAULT, the value is stored in the table's metadata and no rewrite is needed, which makes the statement fast even on large tables. Adding a column with a volatile DEFAULT (clock_timestamp(), for instance), a stored generated column or an identity column does rewrite the entire table and its indexes. Changing a column's type normally rewrites too.

The real risk is in single-step changes. Renaming a column breaks instantly for the application version still running. The pattern that helps is Parallel Change, published by Danilo Sato on Martin Fowler's site in May 2014: expand, migrate, contract. Add the new column and write to both, migrate readers across, then remove the old one. The pattern traces back to Joshua Kerievsky's work in 2006, and it is the data-layer equivalent of zero-downtime deployment practice.

If nobody owns the model, it degrades a little every sprint

In most teams schema changes slip through inside a migration file that nobody reviews. Two light habits close the gap. Make migration files a separate item in code review: what does this new column represent, what does NULL mean here, which constraint is missing. And keep a short data dictionary, one paragraph per main table: what this table represents, what makes a row unique, whether this is the system of record or whether another system owns the value. When reports do not reconcile, the reason is usually that those paragraphs were never written.

One thing you can do this week

Pick the two tables that generate the most complaints and run four checks. Write down what NULL means in each column and flag the ambiguous ones. List the rules validated in application code that have no database constraint, then add the two most important ones with NOT VALID and validate them. Count the distinct values in your free-text status columns with a GROUP BY; if the list is longer than you expected, plan the move to a lookup table. Finally, take one historical question people ask in reports and check whether today's model can answer it at all.

Those four checks take an afternoon, and they usually surface the thing that will define your ticket queue for the next six months. If you want help reviewing an existing model or getting a new product's schema right the first time, come with a concrete list of tables and the conversation moves much faster.