Every internal tool, every CRM, every inventory system is a CRUD wrapper around a database. The schema is the skeleton nobody sees until it breaks. Daniel wrote in with a whole taxonomy of questions about exactly this. He says schema design is an underappreciated art form, and with agentic AI getting powerful enough to generate schemas in seconds, the temptation is to skip the thinking entirely. He wants to know what skill sets are actually needed to do this well, and what happens when you get it wrong.
He gave some examples too, right? The home inventory versus business inventory thing?
Yeah. He laid out a few scenarios. Say you're building an inventory system. In a home inventory, books might be a type of item — attributes like author, ISBN, genre. In a business inventory, books might need to be a top-level entity with their own pricing, supplier relationships, stock levels. Neither is wrong. It depends on what the application needs to do. Same with shekels — are they a currency subtype, a coin subtype, or a top-level entity? If you're a numismatist tracking collections, shekels need era, mint, metal composition. If you're a business accepting multiple currencies, shekels are just a currency with an exchange rate. Then there's multi-site segregation — do you split at the schema level, table level, or just a site ID column? He says there's no single right answer to any of this, and he's right. It depends on data contracts, application needs, and scale. And users will find failure modes you never envisioned.
So today we defend the art form, examine what intelligent schema creation actually requires, and ask whether the AI era is making us worse at it.
That's the question. Let's start with what we're actually talking about when we say schema design is an art form.
CRUD apps. Create, read, update, delete. Every internal dashboard, every project management tool, every inventory tracker — they all reduce to operations on a database. The schema is the invisible skeleton that determines what the app can and cannot do. And the thing is, it looks like a boring upfront chore. A thing you do before the real work starts. But it's not a chore — it's a design problem with no single correct answer.
That's the part people miss. You can normalize a schema to fifth normal form and still have built the wrong thing.
Right. Every schema decision encodes assumptions about how data will be used. And those assumptions are almost always wrong in ways you cannot predict. You don't know on day one that users are going to want partial quantities, or multiple phone numbers per contact, or that the thing you modeled as an attribute is actually an entity with its own lifecycle. The art is designing something that survives contact with reality.
And the timing matters. January twenty twenty-six saw a wave of AI-assisted schema generators hitting the market. You describe your app in natural language, it spits out a migration file. Thirty seconds and you've got tables.
Which is useful for scaffolding. But the danger is that teams treat it as the finished product. The AI doesn't understand your data contracts. It doesn't know your scale. It doesn't know that your users are going to want to search inventory by supplier region and that the naive schema will require a five-table join for that query. It produces generic schemas that work for toy examples.
So the temptation is to skip the thinking. And the thinking is the whole thing.
Let's walk through Daniel's examples, because they're exactly right for illustrating the trade-offs. The books problem. Home inventory — you're tracking what you own. Books have a title, an author, maybe a genre. They live inside a larger items table as a type, or they get a type-specific attributes table. The book-ness is an attribute of the item. Business inventory — you're a bookstore. Books are not attributes of items. Books ARE the items. They have suppliers, wholesale prices, restock thresholds, ISBNs that need to be unique across the system. They need their own table with foreign keys to suppliers and purchase orders.
Same physical object. Completely different place in the schema. And you cannot know which is right without understanding what the application is going to do with the data.
The shekels example is even better because it has three valid answers depending on context. Numismatist — shekels are a subtype of coin. They have a mint, an era, a metal composition, a condition grade. The coin-ness is the important thing. The shekel-ness is a refinement. International business — shekels are a currency. They have an ISO code, an exchange rate, a symbol. The fact that they're also physical coins is irrelevant to the schema. And then there's the third case Daniel didn't spell out but implied — what if you're a coin dealer who also accepts multiple currencies? Now shekels are simultaneously a coin in your inventory and a currency in your payment system. They might need to exist in two different places in the schema with different attributes.
That's where the art form really shows. Knowing when one thing in the real world is two things in the database.
And you have to decide that before you write a single CREATE TABLE statement. Because if you get it wrong, you're doing a migration that touches every row in the system. Martin Fowler wrote about this years ago — the SchemaMigration pattern. Migrations are inevitable. The goal is not zero migrations. The goal is reducing their frequency and their pain when they happen. A well-designed schema makes migrations surgical. A poorly designed one makes them a rewrite.
The multi-site question Daniel raised is the same kind of problem but at the infrastructure level. Schema-level segregation means separate databases per site. Strongest isolation — one site cannot accidentally see another site's data. But cross-site queries become... what, federated queries across database connections?
Yeah, and they're slow and fragile. You're joining across connection strings. Table-level segregation — one database, but each site gets its own set of tables. Better for cross-site queries because you're in the same database, but now your application has to know which table to hit based on the site context. And if you add a site, you're creating tables programmatically, which is its own kind of terrifying.
Row-level is the simplest — one table, site ID column, filter everything. But now a missing WHERE clause leaks data across sites. And you're one developer mistake away from showing the wrong site's inventory to the wrong user.
The trade-off is isolation versus flexibility. Schema-level gives you ironclad isolation but makes cross-site reporting painful. Row-level gives you maximum flexibility but puts all the isolation burden on the application layer. And the application layer will fail. It always does. Someone will forget the filter.
I've seen that exact bug. A dashboard that showed every site's financials to every user because the developer tested with one site and never added the WHERE clause.
And that's a data contract violation. Every schema is a contract between the database and the application. The schema says, here is the shape of the data, here are the constraints, here is what I guarantee. The application says, I will respect these constraints and I expect these guarantees. A poorly designed schema is a contract the application will eventually violate.
The contract metaphor is useful. Because contracts have parties, and the parties have interests. The database wants consistency. The application wants speed. The developer wants simplicity. The user wants features that the schema didn't anticipate. Every schema design is a negotiation between those parties.
And the negotiation never ends. That's the thing. Schema design is not a one-time decision. It's an ongoing conversation with the data. The best schemas are the ones that know they're going to change and make room for that.
Let's talk about what happens when the negotiation fails. The failure pattern.
The integer quantity problem. Classic. You build an inventory system. Quantity is an integer. Makes sense — you count things in whole numbers. Books, chairs, whatever. Then someone needs to track half a meter of cable. Or three point seven liters of solvent. The schema said whole numbers, and now every report has a rounding error, or worse, the application rejects valid data.
The fix is changing the column type, which locks the table, which means downtime, which means a maintenance window, which means someone is awake at three in the morning running a migration on production.
And that's the easy fix. The hard one is when the integer assumption is baked into business logic. If you've got a stored procedure that calculates reorder points based on integer thresholds, changing the column type doesn't fix the procedure. You're hunting through the codebase for every place that assumed whole numbers.
The phone number one is even more common. CRM, single phone number field. Then users want work, home, mobile. Then they want extensions. Then they want time zones so the system doesn't call someone at three in the morning. The single field becomes a table with a type enum and a number and a time zone and a priority. And now every report that joined on phone number is broken.
The root cause in both cases is the same. The designer modeled what they could see, not what the data actually needed to represent. They saw a quantity and thought integer. They saw a phone number and thought string. They didn't ask, what are the edge cases, what are the future needs, what are the ways this data will be queried.
There's a fallacy here that's worth naming. The one true way fallacy. The belief that for any domain, there's a canonical normalized form. Normalize to third normal form and you're done.
Normalization is a tool, not a goal. It reduces redundancy and prevents update anomalies. Those are good things. But over-normalization can cripple performance. If every read requires a six-table join, your application is slow and your queries are unreadable. Denormalization can be the right choice for read-heavy workloads. The art is knowing when to break the rules.
I've seen schemas normalized to the point of absurdity. A addresses table, a cities table, a states table, a countries table — all joined for every user profile load. The database spent more time joining than serving.
And the opposite failure — under-normalization — is just as bad. The same data duplicated across twelve tables, and when it changes, someone has to remember to update all twelve. They won't. One will get missed. Now you've got inconsistent data and nobody knows which copy is authoritative.
So what are the actual skill sets? Daniel asked what kind of thinking is needed to do this well.
I'd say four things. First, domain modeling. Not database design — domain modeling. Understanding the business deeply enough to know what the data represents, not just what it looks like. You have to know that a book in a home inventory is a possession and a book in a bookstore is a product. Same word, completely different thing in the domain.
That's the part AI cannot do. AI sees the word book and gives you a books table. It doesn't know the context.
Second, query pattern analysis. You need to know what questions the application is going to ask before you design the schema. If the most common query is show me all inventory below reorder threshold across all sites, the schema needs to support that efficiently. That might mean denormalizing the threshold into the inventory table rather than joining to a separate thresholds table.
And the queries change. The schema that was perfect for last year's queries is terrible for this year's.
Which brings us to the third skill — migration planning. Designing schemas that can evolve without breaking existing data. This means things like using views to insulate the application from schema changes, or adding columns with defaults instead of changing existing ones, or designing tables with extension points from the start.
Extension points?
A JSONB column for attributes that don't fit the schema yet. A key-value table for metadata. It's not elegant, but it means you can add new data without a migration. The trade-off is that querying JSONB is slower and you lose type safety. But sometimes that's the right trade-off.
And the fourth skill?
Constraint thinking. Knowing when to enforce constraints at the database level versus the application level. Database constraints are reliable — foreign keys, check constraints, unique indexes. They cannot be bypassed. Application constraints are flexible — you can change them without a migration. The art is knowing which constraints are fundamental to data integrity and which are business rules that might change.
A foreign key on user ID is fundamental. A rule that says discounts cannot exceed twenty percent is a business rule that marketing will change next quarter.
Put the discount rule in the application, not in a check constraint. Put the foreign key in the database, not in the application. And the failure pattern of getting this wrong is either data corruption or migration hell.
Let's talk about the AI temptation directly. Because it's not hypothetical anymore. These tools exist. They work, sort of.
They work for the happy path. Describe a blog — you get a posts table, a users table, a comments table. It's fine. It's what any tutorial would give you. The problem is that real applications are not tutorials. They have weird edge cases and domain-specific requirements that the AI cannot infer from a paragraph of description.
And the AI doesn't push back. It doesn't say, are you sure you want quantity as an integer, because I've seen this go wrong. It just generates what you asked for.
The skill that matters is knowing when to override the AI. Knowing what questions to ask that the AI didn't ask. The AI is a junior developer who writes clean code but has never seen a production outage at three in the morning. The senior developer is the one who looks at the generated schema and says, this will break when we hit a million rows, this join will be slow, this column type is wrong for the edge cases.
And the junior developer is getting better. The January twenty twenty-six releases are impressive. But they're still pattern-matching against training data. They don't understand your business.
The common mistakes list from the database design literature is basically a catalog of things AI-generated schemas get wrong. Wrong data types — the integer quantity problem. Ignoring indexing — the AI generates tables but doesn't think about query patterns. Over-normalization — the AI loves third normal form because that's what the textbooks say. Under-normalization — the AI duplicates data because it doesn't recognize that two things are the same entity. Not planning for growth — the AI doesn't know your scale.
Every one of those is a symptom of skipping the taxonomy design phase. The phase where you sit with the domain and figure out what the things actually are before you decide what tables they go in.
And that phase is not technical. It's conceptual. It's drawing boxes and arrows on a whiteboard and arguing about whether a supplier is an attribute of a product or an entity with its own lifecycle. It's the conversation Daniel was pointing at with the books and shekels examples.
The thing I keep coming back to is that users will find failure pattern you never envisioned. Daniel said that explicitly. And he's right. You cannot anticipate everything. But good schema design reduces the blast radius when the unexpected happens.
That's the real metric. Not did you get it right the first time — you won't. But when you got it wrong, how hard was it to fix? Did you change one column type, or did you rewrite half the application?
The JSONB extension column you mentioned. Ugly but flexible.
Ugly but flexible is a legitimate design philosophy for schemas. It's not the only one, and it's not always right. But it acknowledges something that the one-true-way people miss — the schema is going to change, and the best thing you can do is make change cheap.
Hilbert.
Hilbert: I spent six months as a data architect for a shipping container company. We tracked container locations. The schema had location as a single text field — latitude, longitude, altitude, all in one string, comma-separated. The CEO wanted a report. How many containers are on water versus on trucks. We had to parse that string in every query. Every single query had a substring split. It was a nightmare.
That's... that's exactly the kind of thing we're talking about.
Hilbert: We argued for three months about whether location should be one column or three. Three months. We finally split it into three columns. Six months later we changed it again because we added container orientation — which way the doors were facing. The schema was never going to be right the first time.
So you're saying the art form is real but people overthink it.
Hilbert: I'm saying half the time the right answer is start simple and refactor later. The problem isn't bad schema design. It's treating schema design as a one-time decision instead of an ongoing conversation with the data. My container company spent three months in meetings. Three months of salaries, three months of arguments. And then we changed it anyway.
So the art is not in getting it right the first time.
Hilbert: The art is designing for change. The best schema I ever built had two things on every table. A version column, integer, incremented on every change. And a JSONB column called extensions. It was ugly. The purists hated it. But it never needed a migration. New requirements went into extensions. If they stuck around, we promoted them to real columns later. If they didn't, we dropped the key and moved on.
That's a pattern I've seen in a few places. The extensions column as a staging area for schema evolution.
Hilbert: It cost me eighteen thousand dollars in consulting fees to learn that pattern. The container company paid me forty-five dollars an hour for six months, and then spent another twelve thousand on the migration I told them they'd need. I sent them the invoice and they paid it. I still have the spreadsheet somewhere.
The version column is interesting. It means every row carries its own schema version.
Hilbert: Means you can write migration code that handles old rows and new rows differently. You're not forced to migrate everything at once. You migrate on read, or on write, or on a background job when load is low. The database doesn't care that row four thousand is version two and row four thousand one is version three.
That's a level of pragmatism that you don't get from a schema generator.
Hilbert: The schema generator doesn't know about three in the morning. It doesn't know about the CEO who wants the report by Friday. It doesn't know that the real question isn't what's the right schema, it's what's the cheapest schema to change when we're wrong.
Which connects to something we were saying earlier about the contract metaphor. The schema is a contract, but it's a contract that both parties know will be renegotiated.
Hilbert: Contracts have amendment clauses. Schemas should too.
The JSONB extensions column is the amendment clause.
Hilbert: It's one kind of amendment clause. There are others. Views, stored procedures, API layers. Anything that puts a buffer between the raw tables and the application. The people who design schemas without buffers are the ones who do the three-in-the-morning migrations.
I'm thinking about how this applies to the AI question. If the AI generates a schema with no buffers, no extension points, no versioning — it's generating a schema that assumes it's correct. And it's not correct. It's never correct.
Hilbert: The AI is an optimist. Schema design requires pessimism.
That might be the best summary of this whole conversation.
Hilbert: I was a pessimist at twenty-two and it made me a very good data architect. Also made me terrible at parties. But the schemas held up.
Did the container company ever get it right?
Hilbert: They got bought by a larger container company. The new company had their own schema. We migrated everything into their system over a weekend. Their schema was worse. They stored timestamps as strings.
Of course they did.
Hilbert: I didn't say anything. I was a contractor. I billed the hours and went home.
So where does that leave us? Is the art form dying or evolving?
I think it's evolving. The AI tools are going to get better. They're already useful for scaffolding and for simple cases. But the skill of knowing when the AI is wrong — that becomes more valuable, not less. The AI can generate a schema. It cannot tell you that your domain model is wrong. It cannot push back on your assumptions. It cannot say, I've seen this pattern fail before, here's what happens.
The best schema designers I've known are the ones who have seen enough failures to know what questions to ask before writing a single CREATE TABLE statement. They've done the three-in-the-morning migrations. They've parsed comma-separated location strings. They know what breaks.
And that knowledge is not going away. It's just becoming harder to recognize as a skill, because the easy parts are automated. The hard parts — the domain modeling, the trade-off analysis, the pessimism — those are still human.
The art form is safe. It's just going to look different. Fewer people writing DDL by hand. More people looking at AI output and saying, no, this join will be slow at scale, this column type is wrong, this needs an extensions field.
The craft moves up a level of abstraction. From writing schemas to reviewing them. From designing tables to designing for change. Hilbert's point about buffers and versioning — that's the kind of thing you only learn from experience. The AI doesn't have experience. It has training data.
Thanks to our producer Hilbert Flumingtop for keeping this show running, and for the container company story that I suspect has more chapters.
This has been My Weird Prompts. If you want to send us your own questions about database design, or anything else, email the show at show at my weird prompts dot com.
We'll be back soon.
