Modelling Null

Missing vs Invalid

How should a system represent the difference between a value that is missing and one that is invalid?

At first glance, modern practice seems to settle the matter. Most contemporary frameworks, ORMs, and client libraries provide some kind of Nullable<T> abstraction, and for many applications that is exactly the right answer. It is convenient, familiar, and easy to explain.

But there are several concerns with this approach.

First, it is aesthetically awkward for entities to carry Nullable<T> throughout their API surface. If you remove Nullable<T> from entities to keep that surface clean, you are forced to adapt constantly at the boundaries, where NULL is a practical reality. This tends to affect statically typed languages more than dynamic frameworks such as ActiveRecord.

More importantly, a missing value and an invalid value are not the same thing.

---

The Tension: Missing Is Not the Same as Invalid

A missing value and an invalid value are not interchangeable:

• A field may be missing because nothing was supplied
• It may be invalid because something was supplied, but it failed validation

Those are materially different states, and in some systems that distinction matters a great deal.

If we collapse both states into NULL, we make life easier for our code, but we also lose meaning. Once the data is stored, the reason for the absence is gone; NULL tells us nothing. On rehydration, all we know is that the value is not there.

That is often acceptable. Sometimes it is not.

---

Why Modern Systems Tend Toward Nullable

There is a good reason modern systems typically default to Nullable<T>.

It is a pragmatic abstraction over database nullability. It avoids the awkwardness older stacks often had around null handling. It maps neatly to optional fields. It is widely understood. NULL can be resolved once at input boundaries. For a modern client library, especially an early release, it is almost expected.

If the question is simply, "can this value be absent?", Nullable<T> is usually sufficient and often ideal.

---

The Information Loss in Database NULL

This is the core issue.

A database NULL can indicate many different realities:

• no value was supplied
• the value is unknown
• the value is not applicable
• the value was supplied but invalid
• the value failed parsing
• the value was rejected upstream

Once these are all stored as NULL, the distinction disappears.

That means NULL is excellent for absence, but poor at expressing nuanced field state. If a domain truly cares about the difference between missing and invalid, NULL alone is underpowered.

---

An Older Approach: Sentinel Values

Years ago, before nullable abstractions were common in client stacks, one workaround was the use of sentinel values. This was part of an approach, along with a staging database, that a good friend and mentor, Paolo Cantoni, introduced me to.

A numeric field might use conventions like these:

• -1 means missing
• -2 means invalid input
• -3 means not applicable

A date field might use an agreed placeholder date. A string field might use an empty value or a special token. The important point was that special meanings were encoded directly into the value domain.

This had a real advantage: the distinction between missing and invalid survived persistence and rehydration. Queries could express things like “show me invalid applications” because invalid data had a concrete representation.

In practice, these systems were often coupled with supporting fields such as:

• raw text input fields
• parse status fields
• validation status fields
• rejection reason codes

That made the overall design more powerful than the sentinel values alone might suggest.

---

The Problem with Sentinels in the Main Domain Database

Sentinel values have an obvious weakness: they make a column lie about its true meaning.

An integer column is no longer just an integer column. It is an integer column plus a protocol of out-of-band meanings. The semantics leave the schema and move into convention, documentation, and developers' memory — incidentally, a reliable way for a project to leak information when staff rotate.

This approach becomes brittle over time. Queries become less clear. Constraints grow weaker. Every consumer must remember what -2 means. And eventually the data model starts carrying ingestion concerns that do not really belong in the domain.

This is undesirable in the database that is meant to reflect the clean business domain. A domain database should ideally hold values that are meaningful in the domain, not artifacts of input triage.

---

The Staging Database Pattern

This is where an older and, in many ways, very elegant architectural idea comes in: the staging database.

Instead of forcing one database to serve every purpose, the system is split into phases:

Input
└── Staging database
    └── Reject/accept preprocessing
        └── Hosted database
            └── Real application processing

That changes everything.

In a staging database, the priorities are different. The goal is not to present an ideal domain model. The goal is to preserve raw input faithfully, classify problems, validate data, and make preprocessing possible. In that world, it makes perfect sense to carry:

• raw source values
• parsed typed values
• sentinel-style markers, where useful
• field status values
• rejection reasons
• provenance and import metadata

A staging database is allowed to be messy; it is designed to hold data before trust is established.

Then application preprocessing determines what happens next:

• records that fail validation are rejected, quarantined, or marked for correction
• records that pass validation are normalized and promoted
• only acceptable, domain-grade data is moved into the hosted or operational database

At that point, the hosted database can remain clean and semantically strong. It does not need to carry every artifact of invalid import data because that work has already happened upstream.

It also has the happy side-effect of reducing processing pressure on the hosted system, because the dataset has been prepared. Architecturally, it may encourage a split in the codebase between preprocessing responsibilities and domain processing responsibilities, which can reduce overall complexity. In that sense, it is a way to introduce modularity by design.

---

Why This Staged Approach Is So Attractive

This architecture resolves the tension beautifully.

It acknowledges that raw incoming data is often messy and ambiguous, while the real application database should remain clear and trustworthy.

It allows the system to preserve the difference between missing and invalid when that distinction matters, without polluting the operational domain model with every ingestion concern.

The APIs of domain entities also become cleaner, no longer carrying Nullable<T> across their surface.

In other words, it uses different representations for different trust boundaries.

That is often the real answer.

Not one representation everywhere, but the right representation at each stage.

---

A Practical Compromise

Not every system needs a staging database, and not every domain can justify one.

In many cases, a more practical compromise is to accept NULL in the database, model that as Nullable<T> in code, and then introduce explicit status fields only where they still carry genuine domain value, absorbing those concerns formally into the domain rather than relying on a technical workaround such as sentinel values.

This works well because it keeps the common case simple. Most optional values are treated as exactly that: optional values. The system does not pay the cost of richer state modelling everywhere, only in the places where the distinction between missing and invalid still matters after persistence.

In cases like these, the status field is not merely compensating for NULL; it expresses real business meaning. That is the important distinction. A status field is worthwhile when it reflects something the domain genuinely cares about, not simply as a technical patch for database nullability.

This approach lacks the full fidelity of a staging pipeline, but is a good tradeoff. It preserves a clean operational model, avoids sentinel values, and allows richer semantics to appear where justified.

---

A Pragmatic Conclusion for Modern Systems

For simpler domains, Nullable<T> is often enough. It is modern, expected, and convenient, and for many systems that is exactly the right tradeoff.

Where the distinction between missing and invalid still carries domain meaning after persistence, Nullable<T> combined with explicit status fields can be an excellent compromise. It keeps the common case simple while allowing richer semantics where they are genuinely justified.

Where input quality, auditability, external feeds, or preprocessing complexity are first-class concerns, a staging pipeline may be the better answer. In those systems, messy incoming data can be preserved and classified upstream, while the hosted database remains clean and semantically strong.

That feels like the most balanced conclusion. Nullable<T> remains the right default for many fields. Status fields provide a pragmatic middle ground where the domain still cares about state. And a staged architecture remains a powerful option when the domain and budget justify it.

When the meaning of absence matters, NULL alone is not always enough. The right answer depends on how much of that meaning the system needs to preserve, and at what stage.

This is one of those design questions that seems small at first and turns out to touch almost everything: persistence, validation, domain modelling, application boundaries, and operational architecture.