The Boolean is Lying to You

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

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)

The System Works

For the last 20 years or so I've thought about what retirement might look like. 

In retirement, I'd go back to school and write a thesis on... I didn't know what to call it. The "Medical Industrial Complex?" Much of this was due to the many, many, many interactions as it relates to Kate. 

"I'll go back to school, do the research, get my PhD." Mostly so I could participate in Spies Like Us shenanigans: Doctor. Doctor. Doctor. Doctor.

I had this gut feeling that something was...broken; none of it made sense. 

When I didn't have insurance, $115 copay. When I did have insurance, $400. 

As a consultant/contractor, I understood how we measure things in time; n dollars/hour. 

OK, so Doc is $115 x 4 (because it may have been 15 minutes, I'm being super generous here), how is it now $400 x 4/hour? How in the hell does handing over a piece of plastic turn a $460/hr rate into $1,600/hour?

I'm (often) told I need a hobby. Outside of work. What many don't realize is that I'm one of the fortunate ones; what I do for a living is not work.

Choose a job you love, and you will never have to work a day in your life. Or whatever that phrase is. 

So, one Sunday, a few weeks back, I set Antigravity (Gemini) loose on my idea. I just told it: "I have an idea I want to explore."

I didn't expect a book. I expected a conversation. But what happened over the next few hours was the mechanical equivalent of defining the schema for 20 years of observations. I threw two decades of "gut feelings" at the machine, and it started mapping the plumbing. Before I knew it, I had a 23-bullet outline and links to 50-75 outside sources.

"Fully fleshed out, how long would this be?"

"350-400 pages."

"OK, let's pare that back"

Gemini proceeded to suggest serializing it on Substack or just going with an abbreviated manifesto.

I chose the manifesto. We pared it down to 8 dense chapters. It wrote the Intro. 

What...do I...OMG...this is...wow. 

If you spend enough time listening to patients, doctors, or politicians, you will hear a single, unifying complaint spoken across every demographic and tax bracket in America:

The healthcare system is entirely broken.

It is the one rare diagnosis found across every boardroom, breakroom, and waiting room in the country. They point to the soul-crushing administrative bloat that forces practitioners into eight-minute volume appointments. They point to the labyrinthine "prior authorization" processes where algorithms deny life-saving treatments.

And yet, this frustration is born entirely from a profound paradox.

If you strip away the billing departments and the insurance mandates, the actual clinical engineering of the American medical system is nothing short of miraculous. If you are diagnosed with a rare leukemia, or require an unprecedented cardiothoracic intervention, there is quite literally nowhere else on earth you would rather be. The United States produces the most advanced, precision-engineered medical science in the history of human civilization. The people executing that science—the physicians, the bio-engineers, the surgical staff—are elite.

That is not the original, but you get the idea. I spent time reading and editing that introduction, changing the tone, the focus, where I ended up creating a GUIDING_PRINCIPLES.md file to keep it inline:

• Dissect the Maze, Don't Indict the Mice
• Empathy for the Inheritor
• No Emotional Accusations, Only Systemic Mechanics
• Labels are "Pre-Written Baggage"
• Bypass Over Bureacracy

Super cool. A few iterations later, I had an Intro. On to Chapter 1. Same process. Edit for tone and clarity, but otherwise let it loose. Chapter 2, same. After Chapter 2, I just let it rip. By the end of that Sunday session, maybe 5 hours, I had a 40 page "book."

Over the next couple of days, I'd spend 30-60 minutes creating cover and chapter art (with Gemini web).

On Wednesday I had reached my quota for the month, the $20/month plan would reset on 4/11 (Saturday). 

I was exploring publishing options, I've always wanted to be a published writer (as I type that, I realize there are north of 800 articles here, so...). 

But like that, that specific itch was gone. I did not revisit on Saturday. I did share with friends and colleagues. I solicited feedback. I began to incorporate feedback and also track who provided what feedback. 

Fast forward a month, and I came head to head with this system again. It was unpleasant to say the least. 

Now, however, I was armed with new tools. The system hadn’t changed, but I could finally see how it moved; where the paths were, and why people kept ending up in the same places.