A tradeoff in defining database schemas

27 Apr, 2026

Here are some decisions of a sort that comes up frequently:

1. You have a hats table in your database with uuid, hat_type (e.g., fedora or trilby), size, and dress_code_level columns. Now you need to describe fascinators in your database also. They also have sizes and dress code levels, but also attachment_type, attachment_position, and has_veil. Do you (i) create a separate fascinators table or (ii) add some columns to your hats table, rename it to millinery, and accept that fields like attachment_type can be null in some cases but not others?
2. You need to store user settings. Right now the only settings are a preferred time zone and a preferred theme (dark or light mode), but some day you'll probably need more. Do you (i) have columns for preferred_time_zone and preferred_theme, and accept that you'll need to change the schema later to support more settings, or (ii) put settings in a settings JSON¹ column?
3. You are collecting data about hat interactions. Sometimes people tip their hats, sometimes they put them on, sometimes they take them off, and so on. These events have some, but only some, overlapping fields. Do you (i) try to cluster like events in tables like hat_tips and hat_on_events, so that events in those tables have near-identical or identical attributes, or (ii) put all of them in an events table?

Call an approach that favors (i)-type answers "table faithfulness"² and an approach that favors (ii)-type answers "table minimalism." The first approach tends to respect the semantics of table names and columns better, and to give values (including nulls) more consistent meaning between rows. The second approach tends to make it easier to know where data is and to process data without lots of joins and table lookups.

Any specific decision will, of course, depend on your specific domain and tooling, but I tend to value table faithfulness and (i)-type solutions more than other people do:

1. When the meaning of a column is consistent for rows in a database, you can more easily do integrity checks (and especially programmatic integrity checks). For example, you can raise an alarm if two columns are null at the same time.³
2. Table and column names are forms of documentation, and extremely commonly referenced ones at that. The more semantically accurate they are, the better.
3. Many database features (indexes, range queries, grouping, and so on) assume, or at least work better when, you've prioritized table faithfulness.
4. Table faithfulness discourages practices like stuffing extra fields into settings columns haphazardly and keeping business logic in developers' heads.
5. Modern tooling makes it easier to do migrations and learn what tables and columns⁴ exist, even when there are many of them. So, the drawbacks of faithfulness are getting less and less severe.

This is almost always a tradeoff, and sometimes an intermediate or (ii)-type solution is best. Over the years, though, I've very often found myself advocating for more faithfulness, all things considered, and I've rarely regretted that. (Even more often, I've had to deal with the consequences of a bloated "here's where all the main data lives, and please message this one person if you have any questions" table.) So, if you're facing such a decision⁵ and not sure what to do, please consider this a vote for faithfulness.

P.S.: Ideally, your persistence layer is encapsulated sufficiently well that most of your code never knows or cares which approach you take, but that's another post.

#generative AI #nuts and bolts #software