The Boolean is Still Lying to You: The OLTP Edition

Architected by Chet, written by Antigravity

Let's get straight to the point (again): a boolean should never be your first move in a physical data model.

Last week, in The Boolean is Lying to You, we talked about how a boolean could ruin an OLAP dimensional model. In the warehouse, the problem with a boolean is that it duplicates a truth that already exists elsewhere (temporal boundaries).

But the temptation of the boolean doesn't vanish when you switch to an OLTP system. In a transactional database, the problem is the exact opposite: it destroys a richer truth and replaces it with a poorer one.

If anything, the drive for immediate application convenience has made it worse. You need to know if a user account is active, if a record is deleted, or if an order is shipped. The developer reflex is to slap an is_active, is_deleted, or is_shipped flag on the table.

Just like in the warehouse, the boolean is lying to you. In an operational system, lost fidelity means lost business context.

The is_deleted Tragedy: Timestamp Trumps Boolean

The most common offender is the soft delete: is_deleted = true.

It seems harmless. The application filters out WHERE is_deleted = false, and your data is "safe". But in an operational system, knowing that something was deleted is rarely enough. Within weeks, the business will ask: When was it deleted? Who deleted it? How long was it active before it was removed?

Your boolean is mute. It destroyed the temporal context of the event.

Instead of is_deleted, your first instinct should be a deleted_at timestamp. If deleted_at IS NULL, the record is active. If it's populated, you know exactly when the state changed. The application's WHERE clause is just as simple, but the database retains the full fidelity of the event.

The Boolean Pile-Up: Where State Machines Go to Die

Business processes are rarely binary. They are lifecycles. They are state machines. But the path of least resistance often leads to modeling these state machines as a pile of mutually exclusive booleans.

It starts innocently with is_draft = true.
Then the business process evolves, so we add is_published.
Then we need to pull it down temporarily, so we add is_archived.

Now you have a record where is_draft = true AND is_published = true. What does that mean? It means your application allowed an invalid state because your physical model didn't enforce mutual exclusivity. You forced the application code to manage the integrity of the state machine, and eventually, the code will fail.

If a record moves through a lifecycle, use a status_code (backed by a reference table) or an event-sourced ledger. A single status column makes mutually exclusive states explicit and enforceable. Booleans just allow for combinatorial explosions of invalid states.

The Tri-State Lie

A boolean promises two states: True or False.
But in a SQL database, a nullable boolean actually has three states: True, False, and NULL.

What does a NULL boolean mean in your application? Does it mean "False"? Does it mean "Unknown"? Does it mean "Not Applicable"? When is_verified is NULL, did the verification fail, or has it just not happened yet?

When you use a boolean to represent business state, you inevitably back yourself into relying on this ambiguous third state. If you have three states, you don't have a boolean. You have a poorly labeled lookup table.

The Indexing Bonus: The Nerd Special

There's a physical performance argument here, regardless of which database engine you use.

Let's say you have a transaction processing table, and you use is_processed = false to find work that needs to be done. If you index that boolean, you're indexing the entire table.

Instead, if you use a processed_at timestamp, you get a massive performance feature for free by using a partial (or sparse) index.

By creating an index specifically for the rows WHERE processed_at IS NULL, your index only contains the tiny fraction of records that actually need processing. It stays perfectly sparse, incredibly small, and lightning fast. A generic boolean flag robs you of this elegant optimization.

Downstream Devastation: Why the Warehouse Cares

It’s tempting to think that an OLTP shortcut only affects the application layer. But the damage flows downstream. When you overwrite a state with a simple boolean flag, you aren't just making a lazy choice for the transactional app—you are permanently destroying data that your analytical systems desperately need.

• Transitions are destroyed. If you just update is_converted = true or is_canceled = true, you only know the final outcome. You lose the sequence of events. You can no longer calculate the duration between states, identify bottlenecks, track SLAs, or analyze churn. Operational analysis, process mining, and machine learning all require transitions to figure out why something happened. A boolean destroys the transition and leaves you with a tombstone.
• Time-Travel and CDC (Change Data Capture). When your OLTP system relies on event logs, timestamps, or explicit status histories, extracting that data into your OLAP environment is deterministic and robust. You can perfectly reconstruct what the business looked like at any given second. If you rely on flipping a boolean in place, you force the warehouse to frantically poll and capture those fleeting changes before they are overwritten again, inevitably missing rapid transitions.

Stop Hiding the Business Process

In OLTP, the database is the engine of the business. When you reduce a business event (like a cancellation, a deletion, or a publication) to a boolean flag, you are erasing the context of that event. You are optimizing for a temporary application shortcut instead of modeling the reality of the domain.

Just like in dimensional modeling, the rule stands: a boolean requires a waiver. Don't reach for it just because it's easy. Force yourself to ask: "Does this state have a history? Does it have a timeline? Is it part of a larger lifecycle?"

Almost every time, the answer is yes. And almost every time, the boolean is the wrong move.

The Boolean is Lying to You

Architected by Chet, written by Antigravity

Let's get straight to the point: a boolean should never be your first move in a physical data model.

I know, I know. It's incredibly tempting. You're building a dimension table, you need to know if a row is the current one, so you slap an is_current flag on the end of the script and call it a day. It feels clean. It feels simple.

I'm not saying a boolean is never justified. There are cases where an attribute really is just true or false, no history, no drift, no reason code required. But that case has to be earned, not assumed. In my own agent instructions, a boolean (same as jsonb) requires a waiver before it's allowed into a physical model: a deliberate, written justification, not a shortcut reached for because the alternative required more thought. The default posture is no, and the burden of proof sits on the column, not on the reviewer.

Nowhere does a lazy boolean cost you more than in a dimensional model.

The OLAP Failure: Redundancy

If you have a perfectly modeled dimension table using a Slowly Changing Dimension (SCD Type 2), tracking a physical is_current boolean alongside it is fundamentally redundant.

The truth of whether a record is the current version at a given moment is already perfectly contained within your temporal boundaries (valid_from and valid_to). Adding a physical boolean flag right next to those dates introduces the very real risk of data anomalies where the flag eventually drifts out of sync with the timestamps: an ETL job dies halfway through, and now is_current = true on a row whose valid_to says otherwise.

In a strict physical model, you store only the primary source of truth: the explicit temporal versioning columns. No independently maintained derivation sits next to them.

The "Bit Bucket" Trade-off

Inevitably, someone building a dashboard on top of that dimension will push back:

"Every consumer has to remember how this dimension represents the current row. Just give us an is_current flag so every report uses the same simple predicate."

I understand the argument. But this is the same divide I wrote about back in 2008 and 2010: the database is not a Bit Bucket that exists to mirror the current report's WHERE clause. The physical model exists to be the single source of truth first, and convenient for one consumer second.

If the derivation is genuinely a performance problem, use a materialized view, a semantic-layer cache, or a generated expression that cannot drift independently from the temporal source of truth, not a physical flag sitting next to the dates it duplicates and can silently disagree with.

"But it makes it easier for the analysts!"

Inevitably, whenever I make this argument, someone across the table will push back: "But having an is_current flag just makes it easier for the analysts!"

Every single time I hear that, I cringe. The phrase that immediately comes to mind is "the soft bigotry of low expectations."

Are we really going to permanently cripple our physical data model and introduce risk of out-of-sync data anomalies because we assume an analyst is incapable of writing a WHERE valid_to IS NULL clause? Analysts are smart. They understand temporal data. Dumbing down the physical schema because we assume they can't handle reality is insulting to them, and dangerous for the database.

The Semantic Layer: Where Booleans Actually Belong

Don't get me wrong: while a raw boolean is a terrible way to store state, it remains a genuinely useful way for a human or a BI tool to consume it. End users love a good checkbox on a dashboard.

That's exactly what the semantic layer is for: abstracting complex, high-fidelity underlying reality into a simple, ephemeral business definition. Your semantic layer exposes a calculated Is Current Version dimension that evaluates valid_to IS NULL (or your system's max-date sentinel) on the fly, at query time, against the one column that's actually the source of truth.

The Idealistic Layer Boundary

If you want a physical record that never loses temporal precision or suffers from redundant flag logic, here's the boundary between physical schema and semantic abstraction:

Modeling Challenge	The Purist Physical Reality	The Semantic Abstraction
State Evaluation	Temporal boundaries (valid_from to valid_to).	Ephemeral True/False flag calculated on the fly for dashboard filtering.
Data Integrity	Enforced by the temporal columns themselves, single source of truth.	Enforced by a standardized definition applied consistently across every downstream BI tool.

By keeping the boolean entirely out of the physical schema and entirely inside the semantic layer, you get the best of both worlds.

---

References

• Application Developers vs Database Developers (Part I)
• Application Developers vs. Database Developers: Part II
• The "Database is a Bit Bucket" Mentality
• The Case for the Bit Bucket (by Michael Cohen)
• Everything is a Bit Bucket (by Michael O'Neill)
• Capturing Record History

How to Answer Questions the Smart Way

Architected by Chet, written by Antigravity

For years, one of the top recommendations on my Required Reading list has been Eric S. Raymond's classic essay, How to Ask Questions the Smart Way.

I still recommend it. It is a foundational text on respecting other people's cognitive load. Do your homework, provide context, state the problem clearly, and make it easy for the person helping you.

And it is not the only one. The industry has spent two decades obsessing over this exact friction:

The Project Maintainer's View: In his book Producing Open Source Software, Karl Fogel dedicates an entire section to handling newbie questions constructively. He popularized the ethos that even if you have seen a question 1,000 times, it is that specific user's first time asking it, making a helpful response a critical investment in community goodwill.

The "Imagine You're Answering" Framework: Developer Jon Skeet wrote a highly cited piece on Writing the Perfect Question. While technically a guide for askers, it flips the script by forcing the writer to completely adopt the mindset, limitations, and frustrations of the responder before hitting submit.

The Defensive Programming Analogy: Jeff Atwood of Coding Horror frequently blogged about the friction between askers and answerers, most notably in Don't Ask Us Questions, We'll Just Ignore You. His commentary focuses on how community systems (like Stack Overflow) must be architected to filter out noise so experts don't burn out and become toxic.

These are all brilliant pieces of writing. But after a couple of decades working across architecture, operations, and data engineering, I've realized something. We spend a massive amount of time teaching engineers how to ask better questions. We spend almost zero time teaching experts how to answer them.

I've made this mistake myself. But regardless of who is doing it, the truth remains: a lot of the communication failures I see aren't caused by bad questions. They are caused by bad answers.

The core problem is usually this: The asker is trying to establish the model. The responder is answering with implementation details, caveats, and breadcrumbs.

The Excavation

We have all witnessed, or participated in, this exact pattern:

Question: Does feature X do Y by default?
Answer: Well, it can be configured differently depending on the deployment.
Question: Right, but out of the box, does it do Y?
Answer: Administrators can change the setting to do Z instead.
Question: Okay, but if I just turn it on without changing anything, what happens?
Answer: Yes, it defaults to Y.

What follows is an excavation. The asker has to carefully dig through three or four rounds of follow-ups just to extract a simple fact.

If it takes twenty minutes and a half-dozen replies to get a one-sentence answer, the problem was not the question. The question was fine. The failure was in the information transfer.

The Expert's Burden

ESR's essay is fundamentally about reducing the cost imposed on the answerer. But there is a reciprocal obligation. If you are the expert, the owner, or the authority, you owe clarity to the asker.

I understand why this happens. It is usually a defensive mechanism. We front-load the caveats because we are terrified of being technically "wrong" or called out over some obscure edge case. We want to protect ourselves by dumping all our context onto the table at once.

But the obligation to respect someone else's time does not end when they finish asking the question.

A good answer reduces uncertainty. It shrinks the search space. A bad answer expands it.

If the audience still has the same question after you respond, you haven't helped them. You have just transferred your cognitive load onto them, forcing them to reconstruct your intent.

It is very similar to the problem with passive voice (something Cary Millsap has talked about for years). The real sin of passive voice isn't grammar. It is making the reader work to reconstruct causality.

Poor technical answers create the same problem. The audience must reconstruct the model, the assumptions, and the contract from fragments scattered across multiple replies.

Answer Like an API

Answering questions effectively is an architecture skill.

A systems person naturally thinks in contracts. What is the source of truth? What guarantees does the system make? What is the documented behavior? Everything else is just plumbing.

We need to treat our answers the exact same way. When someone asks a question, they are usually looking for the contract.

Experts often begin with caveats, history, edge cases, implementation details, and exceptions. Resist that urge.

The answer goes first. Everything else is commentary.

Question: Does feature X do Y by default?
Better Answer: Yes, it defaults to Y out of the box. The configuration option allows you to override this behavior. Here is the link to the doc.

Experts often answer in chronological order ("Here is the history, here are the caveats, therefore the answer is X"). Good communicators answer in logical order ("The answer is X, here is why, here are the caveats").

It is the Minto Principle applied to engineering. The answer should be the first sentence, not the last.

Reduce Ambiguity

The same instinct that drives us toward explicit schemas, API definitions, and data contracts should drive our communication. Make the model explicit. Put the definition where everyone can see it.

The purpose of an answer is not to display expertise. The purpose of an answer is to transfer understanding.

Good architecture, documentation, and APIs all do one thing: they reduce ambiguity. Good answers should do the exact same.

Don't make people pull teeth to understand the system.

You Can Point a Foreign Key Where?!

Editor's Note: written entirely by Gemini (with minor edits by me)

Let’s talk about things we think we know, but it turns out we’ve (read: me) just been following muscle memory for twenty-plus years.

If you asked me on any given Tuesday what a foreign key does, I’d give you the standard textbook answer. It points to the primary key of a parent table. It’s bread-and-butter relational modeling. We back it with a sequence or an identity column, we join on the IDs, and we move on with our lives.

But a funny thing happened on the way to the database the other day. I realized, or rather, I was reminded, that the SQL standard and Oracle Database don’t actually care about your primary key.

A foreign key doesn't have to reference a PRIMARY KEY. It just needs to reference a minimal unique identifier. That means any column set with a valid UNIQUE constraint is fair game.

The Setup

Imagine you have a standard reference lookup table for order statuses. You’ve got your surrogate auto-incrementing ID as the PK because that’s what we do. But you also have an alphanumeric business code that the application actually uses, and that code is guaranteed unique.

CREATE TABLE order_statuses (
    status_id   NUMBER GENERATED BY DEFAULT AS IDENTITY,
    status_code VARCHAR2(10) NOT NULL,
    description VARCHAR2(100) NOT NULL,
    --
    CONSTRAINT pk_order_statuses PRIMARY KEY (status_id),
    CONSTRAINT uq_order_statuses_code UNIQUE (status_code)
);

Normally, devs will map the status_id down to the child orders table. But what if you map the code instead?

CREATE TABLE orders (
    order_id     NUMBER GENERATED BY DEFAULT AS IDENTITY,
    order_status VARCHAR2(10) NOT NULL,
    -- Look Ma, no status_id!
    CONSTRAINT pk_orders PRIMARY KEY (order_id),
    CONSTRAINT fk_orders_status 
        FOREIGN KEY (order_status) 
        REFERENCES order_statuses (status_code)
);

This compiles. It validates. It works.

Why Do We Care?

If you are a "data-first" person, this opens up some interesting pragmatic design choices, especially for seed data and reference enums.

1. No-Join Readability: When I run a quick SELECT * FROM orders, I don't see status 1, 2, or 3. I see 'PENDING', 'SHIPPED', or 'CANCELLED'. I don’t have to write an explicit JOIN to a lookup table just to debug a row in a terminal log.

2. CI/CD Sanity: Moving seed data across Dev, QA, and Prod environments when you rely purely on surrogate sequences can be a nightmare of dynamic mapping scripts. Business codes are immutable constants across environments. Your deployment scripts can just hardcode the literals without breaking things.

The Fine Print (Because this is Oracle)

Before you go rewriting your entire data model, remember that the laws of physics still apply.

First, Oracle does not automatically index foreign keys. If you point a child table to a parent’s unique business code, and somebody tries to delete or modify that code in the parent table, Oracle has to scan the child table to ensure no orphan records are left behind. If you didn’t manually put an index on orders.order_status, you are looking at a Full Table Scan and a nasty shared sub-exclusive table lock (TM) that will freeze concurrent operations.

Second, don't try this on your Slowly Changing Dimension (SCD) Type 2 tables. The second a business code repeats because you are tracking historical versions with effective dates, table-wide uniqueness breaks. And no, you can't use a partial function-based index to bypass this; declarative foreign keys need real, concrete constraints.

Relational Reality Check

In relational theory, a foreign key references a candidate key, which is simply a minimal superkey. The choice to elevate one candidate key to be the "Primary Key" is a physical implementation choice, not a logical requirement.

It’s completely valid, ANSI-standard behavior. It’s supported in Postgres and SQL Server too, so it’s not just an Oracle quirk.

It's just one of those elegant database features hiding in plain sight while application layers spend thousands of lines of code trying to reinvent referential integrity.

Keep it in the database.

Appendix: Documentation & Structural Foundations

Oracle Database Documentation

• Oracle SQL Language Reference — The constraint Clause: The definitive syntax rules and structural restrictions governing referential integrity, confirming that foreign keys can target primary keys or unique constraints.

  Oracle Database SQL Language Reference — Constraints

Edgar F. Codd & Relational Theory

• The 1970 Foundation Paper: Codd, E. F. (1970). "A Relational Model of Data for Large Shared Data Banks." Communications of the ACM. The original blueprint that introduced relational algebra, establishing that relationships are derived strictly by matching domains over mathematical relations rather than rigidly named primary/foreign key pairs.

  ACM Digital Library — A Relational Model of Data for Large Shared Data Banks

• The Relational Model for Database Management: Version 2 (Book): Codd, E. F. (1990). Addison-Wesley. Codd formalizes RM/V2, explicitly grouping Primary Keys and Alternate Keys together under the definition of Candidate Keys, proving that referential integrity mathematically depends on the candidate key property of uniqueness.

  ACM Digital Library — The Relational Model for Database Management (Version 2)