When every column backing a flattened value object is null, what can the layer hand back and why does it matter?
answer
- absence has no storage of its own
- all-null is ambiguous by construction
- layers differ on what comes back
- a marker column or an all-or-nothing constraint
basics
~20 sLayers differ: some return an object whose fields are all null, others return a null reference for the whole value. Either way the round trip can be asymmetric, producing null checks that never fire and updates with nothing to update.
solid answer
~50 sA flattened value has no row of its own, so “absent” has no storage — the only evidence is that every one of its columns is null, and that is ambiguous. One reading is that the value is missing, so the layer hands back nothing; another is that an object exists whose fields are simply not filled in, so the layer hands back a value with all-null fields. Both are defensible and layers pick differently, so the behaviour must be pinned down rather than assumed. The consequences are concrete: a null check that never fires, a validating constructor that refuses to build the all-null object, and a write-then-read cycle that returns a different shape than it stored, which can look like a change and provoke a pointless update. Make the sub-columns nullable as a unit, and add a marker column when absent must be distinguishable from empty.
go deeper
Know that a flattened value has nowhere to record absence except by leaving its columns null, and that the layer must therefore choose what to hand back for that case.
Explain both behaviours and their consequences: a null check that never fires, a constructor that rejects an all-null object, and a round trip that can return a different shape than it stored.
Bring the write-side effects — an update that changes nothing, a bumped version column that can fail a concurrent write — and the fixes: normalise in one place, constrain the group's nullability, add a marker when the distinction is real.
Treat it as a modelling question first. If absent is a real state in the domain it deserves explicit storage; if it is not, remove it at the boundary so no downstream reader has to interpret an ambiguous group of nulls.
## Where the ambiguity comes from A value flattened into its owner's table has **no row and no key of its own**. Its whole existence in storage is a group of columns on the owner's row. So when the domain says “this customer has no secondary address”, the only thing the schema can record is: all of those columns are null. Nothing distinguishes *there is no value* from *there is a value and every field of it is empty* — the schema simply cannot hold that distinction unless you add something to hold it. The layer therefore has to guess on the way back, and the two reasonable guesses lead to visibly different code. ## The two behaviours, side by side | On reading all-null columns | A null check on the attribute | A validating constructor | Comparing to what was written | |---|---|---|---| | Return a null reference | fires | never runs | matches a write of nothing | | Return an object with null fields | never fires | must tolerate all-null input | may look different from a write of nothing | Neither column of that table is wrong in principle. The first keeps “absent” honest and pushes null handling to the caller. The second keeps the attribute non-null so callers can always dereference it, at the price of an object that represents nothing. What is genuinely dangerous is not knowing which one you are on, because the code you write for one silently misbehaves on the other. ## Partial nulls: the worse case All-null is at least a recognisable state. **Partially null** is the state nobody designed: - A city with no street and no postcode — is that an address? - A currency with no amount — is that money? - A start date with no end date may be intentional (an open interval) or may be corruption, and only the domain knows which. Partial nulls arise the moment sub-columns have independent nullability, or when an earlier version of the mapping wrote fewer columns than the current one reads. If “half a value” is meaningless, say so in the schema: make the group nullable **as a unit**, and enforce it with a check constraint asserting that the columns are all null together or all present together. That turns an ambiguity into a rejected write. ## What it costs at write time The round trip is where the ambiguity turns into behaviour: 1. The code assigns nothing, and the layer writes nulls into the group. 2. A later read materialises an object with all-null fields. 3. Change detection compares that object against the loaded snapshot, or against what the domain now holds, and can decide something differs. 4. An update is emitted that changes no data — noise in the statement count, extra row versions, and a version-column bump that can make an unrelated concurrent write fail its check. The same asymmetry breaks equality-based logic: an object with all-null fields is not equal to nothing, so “did this change?” and cache-key comparisons answer differently depending on which side of the round trip they run on. ## Making it deterministic Four measures, in the order they usually pay off: 1. **Decide the semantics explicitly** for each flattened value: is absent a real state in this domain, or is the value always present? Half of these bugs come from never asking. 2. **Normalise in one place.** A single accessor or factory that turns an all-null object into absence, or absence into a canonical empty value, keeps the rest of the code from testing both. 3. **Add a marker column** when absent and empty must genuinely be distinguished — a small boolean or a status column on the owner's row that records that the value was set. Storage cannot infer that; only an explicit column can carry it. 4. **Constrain nullability as a group**, so partial nulls cannot be written at all. ## Query-side consequences Because the sub-columns are ordinary columns, predicates address them directly, and “absent” has to be spelled out: a filter on one sub-column being null matches rows where the value is conceptually missing *and* rows where that one field happens to be empty. Counting “customers with a secondary address” is therefore a predicate over the group — usually one representative not-null column, chosen because the group's nullability moves as a unit, or the marker column if one exists. Relying on the object-side notion of absence in a query does not travel: the database never sees the layer's decision about what all-null means, only the columns.
- How would you make “absent” genuinely distinguishable from “present but empty”?Add a column that records it — a boolean or a status field on the owner's row set when the value is assigned. Nothing else can carry the distinction, because the value's own columns are already all null in both cases. The alternative is to declare that the distinction does not exist in this domain and normalise one of the two states away at the boundary.
- Why can a partially null group be worse than an all-null one?All-null has one plausible meaning and can be normalised. A group with a city but no street has no agreed meaning at all, so each reader invents one. It usually comes from independent nullability or a mapping that grew columns over time. A check constraint requiring the columns to be all null or all present rejects the state instead of leaving it to interpretation.
saying these in an interview costs you the question
- Assumes every layer returns a null reference for all-null columns
- Never checks what a write-then-read round trip actually returns
- Lets sub-columns carry independent nullability with no constraint
- Believes the database can tell absent from empty by itself
- Ignores updates that change no data as harmless noise