The Database Is Still Not a Bucket

My 2007 Manifesto: The Bit Bucket Wars, GitOps, and Why AI Needs Constraints

I have been looking for this specific link for years. Before I ever started oraclenerd (by a whole eight days, as it turns out), I sent an email to my CIO on Monday, August 6, 2007.

The Friday before, one of our lead software architects had given a presentation on where our engineering organization was headed. All I heard for an hour was Ruby, Java, and C#. Not a single word about the database. Not a single word about the data. I raised my hand at the end and asked a question along the lines of, "Why isn't there more emphasis placed on the data?" Nobody wanted to get into it because everyone was in a rush to go bowling.

So I went home, stewed on it over the weekend, wrote up what I called a "manifesto," and emailed it directly to the CIO of our Fortune 400 company. Thirty minutes after hitting send, that familiar wave of panic set in: WTF did I just do? Do I still have a job?

Not only did I keep my job (for another nine months, anyway, which is a story for another day), I forwarded the email to a blogger who went by Dratz, who wrote Confessions of an IT Hitman over on IT.toolbox.com. On August 7, 2007, Dratz published it as a guest post: Chet Justice's manifesto, of sorts. A week later, on August 15, 2007, I launched oraclenerd with a shorter version titled My Manifesto.

Thanks to the Wayback Machine, I finally found the original IT.toolbox post. Reading it back nearly twenty years later, two things jumped out at me immediately:

1. Not much has changed.
2. For better or worse, I believe I've been consistent.

Here is the original post from August 7, 2007, preserved in all its glory.

---

The Original Post: August 7, 2007

Chet Justice's manifesto, of sorts.
Confessions of an IT Hitman by Dratz (IT.toolbox.com)
August 7, 2007

A longtime reader and APEX handy man sent me an email he sent to his CIO. It reminded me of an email I sent to my CEO years ago. Sometimes you just have to point out the facts. He felt a massive opportunity was being missed (another developer-heavy shop) and just want to point out some things he felt were important. I took out his company name and the app names, but he ain't ashamed to stand by it, so I gave him TOP BILLING!

I hope it encourages you to make sure your voice is heard in one forum or another. You're paid to participate.

===============================

Dratz,

I thought you might appreciate this. I recently sent this to my CIO...fortunately I still have a job. He's pretty open to my opinions (which is all I can really ask for).

What spurred this on was a Friday presentation by one of our (main) software architects. All I heard was Ruby, Java and C#. Not a thing about the database.

The one thing I did leave out was APEX unfortunately.

enjoy!

--------------------

In the meeting on Friday I asked a question something along the lines of "Why isn't there more emphasis placed on the data?"

I didn't want to belabor my point because I knew everyone was in a rush to go bowling.

This is something of a manifesto for me:

From my point of view, I see two types of developers. One is of the application variety; they make GUIs and web sites. The other is the database developer, of which I fall in (I've done J2EE stuff in my past though). Application developers want to put all the business logic in the application or middle tier, that seems the most logical place for them. Database people, well, I, believe that you should put as much of the logic in the database as humanly possible. Get it as close to the data as possible. Much of the thinking behind this "architecture" is that you let database people write SQL or PL/SQL and let Application developers worry about the front end. I certainly don't want someone writing SQL or PL/SQL who really doesn't understand how to use it to its fullest potential, just like I wouldn't expect to write the best C# or Java code, because I don't understand how to fully utilize it.

Putting SQL (and PL/SQL) in the database gives you a few advantages:

• Maintenance: The SQL is in one place, the database. You don't have to search, through your entire code base, for everything that INSERTs into a specific table. Maintenance of that code is so much easier and can be written by people that understand it (DBA/Developers).
• Cost: How much do we pay for our Oracle license? I'm sure it's well into seven figures. Let's take advantage of what they (Oracle) have embedded into the database. Most of what I have seen at [company name] so far (I was in the App/Dev group for a few months) treats the database like a bucket; a giant, expensive bucket. Why not use what we are paying for? Oracle has written a lot of functionality that we could use:
  1. Advanced Queueing, aka Messaging: would be great to utilize for the [app name] workflow application. We wouldn't have to buy a COTS application (or pay consultants to help us).
  2. Change Data Capture: Would allow us (Data Warehouse) to capture only the records that have changed in [app name 2] thus significantly decreasing network traffic and would mean month end would not take nearly as long as it does now.
• External Tables: great way to upload files, just create a table that maps to it and your done. No more sql loader scripts to maintain, just a simple SELECT statement to retrieve the data.
• Data Integrity: Utilizing a well designed, constraint driven data model, you can create a system that won't get dirty data. Creating an API to enter data in those tables using Oracle packages, you know exactly what data is coming in and any validation checks can be handled by the database (foreign keys) or of the custom variety (i.e. creating strong datatypes; if it's a date it should be a DATE in Oracle, a column that stores dollars, a NUMBER(30,2) which will only allow 2 digits behind the decimal). Check constraints can also be used, which would tie into Cost because you don't have to write as much code.

I've read many arguments for an against. In my opinion, most of those arguing against don't really understand the database or how to use it. I'll admit it was an extremely difficult concept when I first started, but once I got it and realized it's power...

Mostly this is about keeping things simple. My previous employer was a small state funded company. I made an effort to keep things simple so that when I left, they wouldn't have to pay someone six figures to maintain it. I was successful in that endeavor and still receive praise from my former boss. I am very proud of that. Things don't have to be as complicated as we sometimes make them.

Data is the most important thing we have. Applications come and go, but data stays the same. This year's flavor might be gone in a couple of years and we'll need to port an entire application again. If most of that logic is in the database, it will be easy to do. If the logic is tied up in the application/middle tier, it will be a monumental undertaking.

This has been brewing for quite some time. Please don't think I'm gunning for someone's job, I'm not. I just haven't heard anyone really argue from the database side.

---

The Bit Bucket Wars: A Mandatory Reading List

I found this list of five in a DM to a colleague. Don't let the numbering fool you; #3 has two entries. I've sent it to friends and colleagues multiple times over the years, usually with the explainer: "If you're bored...please just read them in order."

1. Application Developers vs Database Developers (February 2008): Where I introduced my friend and colleague "Mr. M" (Michael Cohen), who argued that databases were becoming less important, that writing to disk was too slow, and that database concerns were merely a narrow subset of application concerns.
2. Application Developers vs. Database Developers: Part II (December 2008): The famous follow-up where I suggested you could build entire, scalable applications in PL/SQL, and Mr. M replied: "NO!!! NO!!! NOOOOO!!! I want to beat people like you who say this with a ball pean hammer... DUDE! SQL IS A PILE OF SHIT FOR EXPRESSING APPLICATION LOGIC!!!" (Blog that, mofo! So I did.)
3. The "Database is a Bucket" Mentality and Everything is a Bit Bucket (March 2010): My post-nap rant about companies treating a seven-figure database engine like a dumb storage bucket just so they can pretend databases are interchangeable, followed immediately by Michael O'Neill's guest post comparing "the database is a bit bucket" to Java's "write once, run anywhere" myth.
4. The Case for the Bit Bucket (March 2010): Written by "Mr. M" (Michael Cohen) himself. Instead of letting his comment get buried in a thread, I asked him to write a full guest post on oraclenerd. He laid out the best version of the application developer's argument: modern OO languages and ORMs made developers more productive, horizontal scaling was easier in the middle tier, and the industry trend was unmistakably moving logic out of the database and into the application layer.

Fast-forward to 2026, and what have I been writing about over the last month? Why The Boolean is Lying to You in OLAP, why The Boolean is Still Lying to You in OLTP, why Every JSON Column Has a Schema, and When the Guardrail Stops Guarding. Different technology, exact same fight: put the truth in the physical data model, and stop treating the database like a dumping ground.

Where I Softened: Closing the Feedback Loop

Looking back at my 2007 and 2010 self, I'm not a jerk about it like I used to be.

Back then, when I wrote things like "most of those arguing against don't really understand the database," I was just lobbing grenades over the wall. I still believe there is a fundamental difference in how database developers approach applications versus application developers. Over the years, I've tried to better understand their perspective. But...what's the phrase? Accepting it is half the battle.

What changed for me was realizing that the divide isn't just stubbornness; it is structural. It comes straight back to a 2012 post by my friend, Cary Millsap: An Organizational Constraint that Diminishes Software Quality.

Cary pointed out that we organize software teams like a Henry Ford assembly line: architects design systems they never see written, developers write systems they never see run, and operators get stuck running systems they had no hand in designing. The fatal flaw in that assembly line is the absence of a feedback loop. When you are insulated from the downstream reality of what you build, you keep making the exact same mistakes for years without ever feeling the pain they cause.

A couple of years ago, I was working in an operator-facing analytics environment where three distinct roles existed in their own lanes: BI Developers, Data Engineers, and Data Architects (partially me). I used to tell them, often: "Y'all should be tied at the hip."

The BI Developer should have built a pipeline. The Data Engineer should have built a dashboard. And the Data Architect should know both. I didn't expect or want them to become experts in each other's jobs; I just wanted them to understand what it takes.

When you force that cross-pollination, the feedback loop closes. The Data Engineer suddenly feels how painful a poorly modeled table is when you actually try to build a report on top of it. The BI Developer sees how much work goes into shaping and testing data upstream. Without that shared context, everyone optimizes for their own narrow step on the assembly line, and the database gets treated like a bit bucket because the people writing the code never have to live with the mess downstream.

How GitOps Made the Pattern Worse

Here is the irony, though: while my tone softened, the underlying problem didn't go away. In fact, the GitOps revolution poured jet fuel on it.

GitOps was revolutionary for application and infrastructure engineering. Everything lives in Git. Everything is declarative. Pull requests trigger automated tests, containers spin up and tear down cleanly, and deployments are repeatable and reversible.

And then we applied those same ideas to data.

That made a ton of sense. Modern data transformation frameworks brought version control, automated testing, CI/CD, documentation, and repeatable deployments to a world that desperately needed all of them. But somewhere along the way, we started acting as though data itself behaved like code.

It doesn't.

Rolling back an executable is relatively easy. Rolling back a database change can be extraordinarily difficult. Code describes what a system should do. Data records what the system has already done.

Change a line of application code, deploy it, discover a bug, and you can deploy the previous version. Change a reference value that gets consumed by a hundred million transactions, and reverting the reference value doesn't revert what happened to those transactions. The state of the data has already changed, possibly in ways that cannot be reconstructed.

I fully acknowledge that difficulty. Database changes are harder precisely because state matters.

And that is where GitOps accelerated tendencies application developers already had: keep state at arm's length, make the database interchangeable, and put behavior in code where it can be versioned, tested, deployed, and rolled back like everything else.

Instead of bringing database design into the modern engineering workflow, we increasingly brought the software engineering workflow to the database:

• ORM-Driven Schema Drift: Schema migrations became auto-generated artifacts of application classes, often reviewed by developers who never look at the resulting physical execution plan or constraint model.
• Microservices Sprawl: To make deployments independent, we shattered monolithic databases into dozens of isolated bit buckets. Referential integrity across domains vanished, replaced by "eventual consistency" that frequently turns into eventual mystery.
• Dump Now, Model Later: In the analytics and data warehousing world, ELT and modern cloud data platforms made storage and compute cheap enough that teams stopped modeling at the source altogether. Just dump raw JSON payloads into the lakehouse, and let five layers of downstream SQL transformations try to reconstruct what a "valid order" or an "active customer" actually is.

GitOps didn't create the bit bucket mentality. It made the bit bucket easier to engineer around.

We got really, really good at shipping code. Unfortunately, we also got really, really good at generating dirty data.

The AI Revolution: GitOps for Data, or the Ultimate Test?

Which brings us to right now. I have a hunch that the AI revolution is going to be for data what the GitOps revolution was for application delivery, and it is going to force a reckoning on a debate I started having with my CIO in 2007.

Why did the "bit bucket" win the first round in 2010? Two reasons:

1. Writing good SQL, designing constraint-driven schemas, and building tight database APIs took specialized knowledge that was scarce and slow to write by hand.
2. Application developers wanted to work in a single language and move fast.

AI changes the math on both of those overnight.

First, the friction barrier got a hell of a lot lower. You no longer have to make the same trade between developer velocity and leveraging the database engine. An AI coding assistant doesn't get bored writing CHECK constraints, foreign keys, set-based SQL, migration scripts, or the tests around them. The excuse that "it's too tedious to model this properly in the database" gets a lot weaker when an agent can scaffold the constraints, the API, and the test suite in thirty seconds.

Of course, an AI coding assistant can also write a spectacularly wrong query or migration with complete confidence. Which brings us to the second point.

Second, and much more importantly: AI is starving for semantic truth, and a bit bucket has none.

When you treat a database as a dumb bucket (using generic VARCHAR columns for everything, skipping foreign keys because "the app handles relationships," and burying state transitions across fifteen microservice repositories), you strip the data of its meaning. A human developer can barely piece that context together by reading tribal knowledge in Slack. An AI agent pointed at a constraint-free database has no chance.

Think about what a constraint-driven data model actually is:

• Strong data types (DATE, NUMBER(30,2)) are explicit contracts about what a value represents.
• Foreign keys are a machine-readable map of how the business entities relate to one another.
• Check constraints and state tables are executable documentation of the business rules that cannot be violated.

And there is another difference now. Constraints aren't just documentation for the AI. They are the boundary around what the AI is permitted to make true.

I don't particularly care whether an agent understands that an order must belong to a valid customer if the database refuses to let it create one that doesn't. The foreign key doesn't need a prompt. The CHECK constraint doesn't hallucinate. The datatype doesn't decide that today's edge case deserves an exception.

That is the part of the 2007 argument I couldn't have anticipated. Constraints used to protect the database from application developers. Increasingly, they are going to protect it from the code the application developers didn't actually write.

Nineteen Years Later

In that August 2007 email, I wrote:

"Data is the most important thing we have. Applications come and go, but data stays the same. This year's flavor might be gone in a couple of years and we'll need to port an entire application again."

Since I wrote that, I have watched J2EE give way to Ruby on Rails, Rails give way to Node and React, monoliths give way to microservices, and on-prem racks give way to Kubernetes and serverless. Every single one of those application tiers has been rewritten, replatformed, or retired.

The data is still there. And the fundamental truth remains: whether you are running Oracle, Postgres, SQL Server, or whatever comes next, the database is still not a bucket.

I'm a lot nicer about how I say it these days. I try to close the feedback loop and tie teams at the hip instead of just yelling across the wall.

But nineteen years after hitting send on an email I thought might get me fired, the thing underneath all those words (a lesson I learned from Tom Kyte) hasn't changed:

Put the truth as close to the data as you can.

In 2007, I thought that protected us from bad application code.

In 2026, I'm starting to think it might protect us from AI, too.

---

Architected by Chet, written by Gemini 4 Argon

An AI Writing Policy: Bleeding Red Ink

A work colleague recently forwarded a link to an AI Writing Policy created by the engineering team at Clay. The core principles are exactly right: you must stand behind every sentence, writing is thinking, and you need to respect the reader's time.

Applying strict editing standards to AI isn't a new corporate mandate. For me, it’s just the baseline.

The Journalism Roots

Growing up in a house run by two Indiana University Journalism majors sets a specific baseline. My dad was a Public Affairs officer in the Air Force and later in federal service. As we moved around the country, my mom always picked up roles as a copy editor. Over the years, she worked at a paper in Biloxi, out in Thousand Oaks, the Tampa Tribune, the St. Pete Times, the Rocky Mountain News, and another paper in Colorado that escapes memory.

Every single paper I wrote for school was subjected to her desk. When I got them back, they were completely redlined. Bleeding ink. That level of intense, structural criticism became normal. It dictates how I approach writing with AI today.

The Turns

I have been verbose for a very long time. I suffer from a chronic inability to leave a thought unpublished. In 2009 alone, I wrote nearly 270 articles.

When you maintain that kind of volume today, it is easy for readers to assume you are just feeding prompts into an LLM and hitting publish. But the output has never come from cutting corners.

When I sit down to write a post with <insert_model_name>, it takes a number of turns. It takes actual time.

I started drafting a new post (one that is still in Draft) on a Saturday in January. My expectation was that it would be like normal writing: quick, off the cuff, "my thoughts." I figured it would take an hour.

I invited Copilot in to do copy editing. But it had ideas, too. I expanded on those ideas, argued with them, refined them. Eight hours later, I still wasn't done. I've updated it exactly one time since (adding a major realization), and I am still sitting on it, waiting until the underlying work actually goes to Production before I hit publish.

That is the baseline. Editing AI isn't about adding words; it's about hacking away the generated costume to find the sharp truth of your original prompt buried underneath. Here is what that red ink looks like in practice:

• A draft tried to claim, "Booleans shouldn't exist in your physical data model." The red pen changed that to, "A boolean should never be your first move." Precision matters.
• A recent post about JSON opened with a fake, generated confession. I cut it immediately. A post about declared truth cannot start with an undeclared lie.

The Brain Dump

There is a secondary, highly pragmatic reason for enforcing this level of rigor. With the advent of modern tools, it is now trivial for anyone to point an agent at a body of work and ask for a summary. Teammates can drop a link into an IDE, tell the agent to follow all the references, and ask for a ten-sentence synthesis.

Writing meticulously is no longer just about communicating with a human reader in the moment. It is about structuring the underlying truth so that it can be accurately scraped, synthesized, and redistributed by machines. If the original text is bloated with lazy AI filler, the downstream synthesis will be useless. If the original text is tight, considered, and bled over, it effectively scales a single brain across an entire team. It is a reason to write more, not less. But it only works if the desk holds the line.

The Copy Chief

The first line of defense isn't even human. It is a codified personal style guide. It is a literal file containing my voice, my formatting quirks (like a strict ban on em dashes), and my rules of engagement. The AI is forced to ingest this file before it generates a single word. It bends the model to the desk immediately.

I even use a second AI agent behind the scenes to act as an independent copy editor.

But here is the catch: machine red ink doesn't carry authority. That second editor is sometimes wrong, too. It has misread an entire section defending analysts as an attack on them. It has "improved" text I explicitly asked to be copied verbatim. It has praised a draft and then reversed itself on a second read.

Getting more models to mark the page is just adding more ink. A redline only matters if somebody holds the final desk. I have to be the copy chief.

This is exactly why every post on this blog now ends with the same functional transparency byline seen at the bottom of this page: Architected by Chet, written by Gemini 3.1 Pro.

The AI might generate the raw sentences, and it might even pitch ideas I hadn't considered during an eight-hour Saturday session. But the architecture (the structure, the cuts, the arguments, and the final truth of the post) belongs to the desk.

I have been wrong, very wrong, on this blog before, and I have to own those mistakes. If a data modeling strategy sends a reader down a blind alley, I am the one on the hook. That is why the process takes time. I refuse to publish anything here until it's crossed my desk.

Architected by Chet, written by Gemini 3.1 Pro

---

Appendix: The Meta-Redline

This exact post is a perfect example of how the desk operates. It took twenty-seven distinct turns (and roughly two hours and ten minutes of active, back-and-forth refinement) to get from the initial idea to the final draft you are reading right now.

It started with an image of the Clay policy dropped into the chat. <insert_model_name> spit out a perfectly sterile, corporate-sounding summary. Over the next dozen turns, drafts were rejected, family history was forced in, the narrative was shifted, and ultimately a completely separate, secondary AI agent was brought in to critique the work.

That secondary agent absolutely nuked the draft. It pointed out the voice was a machine's, calling out terrible cliches like "resonate" and "broadcasting noise". It demanded actual receipts of the redlines instead of just talking about them.

That is what it means to hold the final desk.

The System Still Works: The Algorithmic Arms Race

Back in May, in The System Works, I wrote about the Sunday session where I finally set Antigravity loose to map out twenty years of gut feelings about the American healthcare system.

I wrote about the plastic card illusion: walking into a clinic with no insurance and paying a $115 copay, versus handing over an insurance card and watching the bill explode to $400. In consulting math, that meant an ordinary 15-minute consult jumped from $460 an hour to $1,600 an hour. Nothing about the clinical diagnosis had changed. Only the billing plumbing had.

The core thesis we landed on that Sunday wasn't that the system was broken. It was the opposite: the system is working perfectly. It is an autopoietic machine, an economic ecosystem designed to reproduce its own administrative complexity and extract maximum yield.

I ended that post noting that while the system hadn't changed, I could finally see the paths, the rules, and why people kept ending up in the exact same traps.

Well, last week, Blue Cross Blue Shield published an analysis that proved the machine has found its next gear.

The $1 Billion Query: Mining for Acuity

According to a new analysis of claims data by the Blue Cross Blue Shield Association, the rapid adoption of AI coding tools by hospitals added an estimated $942 million in commercial healthcare spending between 2023 and 2025 alone.

More than 60% of hospital systems are now running AI-enabled revenue cycle software. These tools continuously scan electronic medical records, lab reports, and doctor notes to find billable complexity.

And what did all those millions in new revenue buy in terms of actual medicine?

Zero.

BCBSA looked closely at patients undergoing major bowel surgery. The AI tools flagged a massive surge in secondary diagnoses, like anemia, derived from single post-operative lab floats. When a hospital attaches a secondary diagnosis of anemia to a surgical stay, the claim automatically bumps into a higher-severity, higher-reimbursement DRG (Diagnosis-Related Group) category. Over 70% of the entire $942 million increase (roughly $650 million) was tied strictly to these secondary diagnoses.

Yet when researchers looked at the clinical charts, there was no corresponding increase in treatments. No uptick in blood transfusions. No change in medication. No change in bedside recovery time.

Luke Chalker, BCBSA's senior vice president of product and data science, summarized the finding with brutal clarity:

"The disconnect between diagnoses and treatment suggests that AI is identifying more billable conditions, not sicker patients."

If you're a data person, read that sentence again. That is not a bug. That is an optimization routine running exactly to spec.

The 74,000-Code Moat

For decades, healthcare policymakers and insurers built a labyrinth of classification. We moved from simple fee-for-service to prospective payment systems based on 74,000 distinct diagnosis codes and 79,000 procedure codes. The stated goal was accuracy and granular clinical documentation.

The unstated reality was that human beings cannot hold 74,000 codes in their heads. Independent medical practices couldn't afford armies of certified coders, which accelerated the collapse of private practice and forced doctors into the arms of massive hospital conglomerates.

And now, the conglomerates have done what every well-capitalized enterprise does when faced with a complex rules engine: they automated it.

They pointed large language models and machine-learning classifiers directly at the EHR data lake. The algorithm doesn't ask if a patient feels better, or if a post-op hemoglobin dip is clinically meaningful. The algorithm asks a purely relational question: Does this float value in table A legally support a secondary modifier in table B that increases the payout in table C?

The code is no longer a proxy for care. The code is the product.

The Press Release: Setting the Narrative

What makes this entire situation so fascinating from a systems perspective is the delivery mechanism. This wasn't a leaked internal memo. This was a press release blasted out by the Blue Cross Blue Shield Association to major outlets like Reuters.

Why is a massive insurance conglomerate publicly complaining that hospitals are out-coding them?

Because they are setting the narrative.

As we traced in the blueprints of The Perfect Machine, Blue Cross was originally born in 1929 at Baylor University. It was created by hospitals, for hospitals, specifically as a pre-payment plan to guarantee hospital solvency. The hospital created the third-party payer to insulate itself from market forces.

For nearly a century, the two sides grew together. Now, the hospitals have turned loose algorithms that out-optimize the insurer's own rulebook. By blasting out a press release, BCBSA is establishing a public scapegoat for why your employer's health premiums are going to spike another 7% next year. They are pointing the finger squarely at the hospital bots.

But make no mistake: this is a two-sided bot war. Insurers aren't innocent victims. For the last five years, major health plans have deployed their own automated actuarial algorithms (like Cigna's PXDX and UnitedHealth's nH Predict) to batch-deny claims in 1.2 seconds without human review. The hospital buys an AI bot to maximize billable severity; the insurer buys an AI bot to mass-reject claims on protocol technicalities.

Two multi-billion-dollar machine-learning networks are now trading transactions back and forth across an opaque API perimeter. Neither machine cares about the human lying in the hospital bed. The patient is just the transaction payload passing between them.

The System Still Works

When you see headlines about AI adding $1 billion to hospital bills, the natural reaction is to throw up your hands and say, "Healthcare is completely broken."

It's not.

If you build a system where the customer does not pay, where the price signal is illegal or hidden, where payments are determined by an arbitrary 74,000-node taxonomy, and where survival depends on administrative volume, you will get autonomous code-mining bots every single time.

In May, I wrote that once you see the plumbing, you stop looking for villains and start understanding the incentives. The BCBSA report isn't evidence of a broken system. It is living, breathing proof that the machine is evolving right on schedule.

---

Architected by Chet, written by Gemini 3.1 Pro

When the Guardrail Stops Guarding

A few months ago, in You Can Point a Foreign Key Where?!, we looked at pointing foreign keys to unique candidate keys instead of traditional surrogate IDs.

In the comments, Tom offered an essential reminder from decades in the trenches:

"You and I have decades now of CREATE TABLE... and those decades have shown us the only thing that is truly immutable are the things stakeholders tell us are absolutely not immutable... business codes have a way of becoming less immutable across years. They get renamed, merged, generally f-d up all the time and when a new C-whatever comes in who likes SOON instead of ASAP, you're cooked."

Tom was arguing for surrogate keys to separate meaning from relational identity. He was right. Business vocabulary is fluid. What feels permanent during sprint planning rarely stays permanent across three fiscal years.

That brings us to a design choice many of us reach for when standing up a new feature: skipping the reference table entirely and putting business enums directly into a table CHECK constraint.

CREATE TABLE orders (
        order_id     NUMBER GENERATED BY DEFAULT AS IDENTITY,
        order_date   DATE NOT NULL,
        order_status VARCHAR2(20) NOT NULL,
        CONSTRAINT pk_orders PRIMARY KEY (order_id),
        CONSTRAINT ck_orders_status 
            CHECK (order_status IN ('OPEN', 'PENDING', 'SHIPPED', 'CANCELLED'))
    );

To be clear: this is not a post against CHECK constraints. CHECK constraints are indispensable. This is a post about which rules they can actually hold.

Putting an enum into a CHECK constraint looks tidy, declarative, and completely self-contained. It avoids spinning up another table, mapping another entity, or coordinating extra seed files. In the moment, it feels like disciplined engineering.

Until the business evolves, and the guardrail quietly stops guarding.

The Silent Failure: Enforcing Neither

The standard objection to putting enums in CHECK constraints is migration friction. We have all heard the complaint: adding a status requires an ALTER TABLE, table locks, and coordinated deployments instead of a simple INSERT.

That argument is true, but it is an ergonomic argument. A determined engineering team can easily wave it off as an acceptable deployment cost.

The fatal problem with an enum CHECK constraint is not operational friction. It is structural failure.

Consider Tom's scenario: leadership decides that 'PENDING' is too vague. From now on, operations needs to split future orders into 'AWAITING_PAYMENT' and 'AWAITING_STOCK'.

What happens to existing records? The business does not want to rewrite history. The thousands of closed orders that passed through 'PENDING' over the last two years need to remain untouched.

Now look at what happens to your constraint:

ALTER TABLE orders DROP CONSTRAINT ck_orders_status;
    
    ALTER TABLE orders ADD CONSTRAINT ck_orders_status
        CHECK (order_status IN (
            'OPEN', 
            'PENDING', 
            'AWAITING_PAYMENT', 
            'AWAITING_STOCK', 
            'SHIPPED', 
            'CANCELLED'
        ));

Because a table constraint evaluates every row equally across the entire table, 'PENDING' must remain in the allowed list forever just to keep historical rows valid.

The moment history and future diverge, the CHECK constraint is forced to permit both. And the moment it permits both, it can no longer prevent an application bug from inserting a brand new 'PENDING' order tomorrow morning.

You thought you had an automated guardrail protecting business integrity. What you actually have is a decorative plaque. The constraint that was written to enforce valid state transitions has quietly stopped constraining.

Temporal Boundaries Trump Hardcoded Strings

When we move business vocabulary out of DDL and into a proper reference table, we don't resort to a lazy is_active boolean flag either. As we saw in The Boolean is Lying to You, booleans erase history.

Instead, we use explicit temporal boundaries:

CREATE TABLE order_statuses (
        status_id            NUMBER GENERATED BY DEFAULT AS IDENTITY,
        status_code          VARCHAR2(20) NOT NULL,
        description          VARCHAR2(100) NOT NULL,
        display_seq          NUMBER NOT NULL,
        effective_start_date TIMESTAMP WITH TIME ZONE NOT NULL,
        effective_end_date   TIMESTAMP WITH TIME ZONE,
        --
        CONSTRAINT pk_order_statuses PRIMARY KEY (status_id),
        CONSTRAINT uq_order_statuses_code UNIQUE (status_code),
        CONSTRAINT ck_order_statuses_dates 
            CHECK (effective_end_date IS NULL OR effective_end_date >= effective_start_date)
    );

(Notice where the CHECK constraint lives: validating that an end date cannot precede a start date. A mathematical and calendar rule, exactly where it belongs.)

When the business retires 'PENDING', we don't touch the orders table. We update the lookup table:

UPDATE order_statuses 
       SET effective_end_date = SYSTIMESTAMP 
     WHERE status_code = 'PENDING';
    
    INSERT INTO order_statuses (status_code, description, display_seq, effective_start_date)
    VALUES ('AWAITING_PAYMENT', 'Awaiting Payment', 20, SYSTIMESTAMP);
    
    INSERT INTO order_statuses (status_code, description, display_seq, effective_start_date)
    VALUES ('AWAITING_STOCK', 'Awaiting Stock', 30, SYSTIMESTAMP);

Every historical order referencing 'PENDING' remains fully valid. Meanwhile, any active query or UI dropdown filters on effective_end_date IS NULL (or evaluates whether SYSTIMESTAMP falls between the start and end dates). New orders cannot select ' PENDING'. Old orders cannot be corrupted.

Better yet, temporal versioning gives you future-dating for free. When operations announces that a new status takes effect on January 1, you insert the row today with an effective_start_date of January 1. At midnight, it activates automatically. No midnight deployments, no emergency patches, and zero table locks.

The Metadata Leak

A CHECK constraint treats an enum as a naked, isolated literal. But business vocabulary never stays naked.

Before long, the rest of the team needs context:

• The user interface needs human-readable labels and a coherent sort order rather than raw database codes.
• Reporting pipelines need to know which statuses represent terminal states versus active work in progress.
• Audit systems need to know what the valid vocabulary looked like on a specific date two years ago.

In a reference table, those attributes have a natural home (display_seq, effective_start_date, description). The database acts as a shared, queryable source of truth.

In a CHECK constraint, the database cannot hold that context. So the context leaks. It leaks into frontend TypeScript switch statements, backend YAML files, and hardcoded reporting filters. You didn't avoid complexity; you just forced your schema to live in five different application files instead of the database.

The Portable Rule

This brings us to a reliable boundary for schema design:

• Use CHECK constraints for mathematical, temporal, and physical invariants. quantity > 0. percentage BETWEEN 0 AND 100. end_date >= start_date. Cross-column requirements like requiring a card token when payment type is credit card. These are arithmetic and physical boundaries. They do not drift because of a company rebrand.
• Use Lookup Tables for business vocabulary and lifecycle states. Order statuses, workflow stages, customer tiers, reason codes. Anything that product owners or executives might refine next quarter.

If changing the rule violates the laws of mathematics or calendar time, it belongs in DDL. If changing the rule requires a conversation with product management, it belongs in a table.

Scoped Optimization and AI

In my reply to Tom on that earlier post, I noted that keeping enums out of CHECK constraints is one of the fundamentals that requires an explicit waiver in my coding assistant instruction files.

It is worth asking why AI assistants reach for CHECK (status IN (...)) almost every single time they are asked to scaffold a schema.

The assistant is not being lazy. It is behaving exactly like an application developer focused on a single user story: it is correctly optimizing for a scope that does not include the third fiscal year.

Within the boundaries of a single prompt, a self-contained CHECK constraint satisfies every immediate functional requirement. It compiles cleanly in thirty lines of generated SQL, avoids creating auxiliary tables, and avoids the cognitive overhead of foreign keys. The model's horizon is the current response window. It has no reason to care what happens when marketing changes 'ASAP' to 'SOON' thirty-six months from now.

If we don't guide our tools with clear fundamentals, they will produce code that is locally optimal and globally fragile.

Take the time to build the reference table, give it proper temporal boundaries, and establish the foreign key. It is the only way to ensure that when your data models meet the third fiscal year, the database is still the place telling the truth.

---

The arguments are mine. Drafted with Gemini 3.8 Flash.