How do you decide whether an attribute is converted in the layer or stored in a form the database can filter and sort directly?
answer
- who else reads this column
- what the engine must still evaluate
- opacity is a requirement, not a default
- re-encoding is a backfill, not an edit
basics
~20 sDecide by who else reads the column and what the database must still do with it. If predicates, ordering, aggregation or other consumers touch the value, store a form the engine understands; reserve opaque encodings for values nothing else interprets.
solid answer
~50 sThe trade is between convenience for one application and capability for everyone else. A layer-only encoding — a packed structure, a document blob, an application-specific code, an encrypted value — keeps the domain type clean and moves all interpretation into one codebase. That is fine while that codebase is the only reader. The moment a report, an export, another service or an operator needs the value, they must reimplement the codec, and any predicate, ordering, aggregate or constraint the engine could have evaluated now has to happen after loading. So ask: will anything filter, sort, group or constrain on this value; who else reads the table; can the engine index the stored form; and how expensive is re-encoding once rows exist. Opacity is a legitimate answer only when it is the requirement, not the default.
go deeper
Know that the stored form is a choice, and that a value the database cannot interpret is one the database cannot filter, sort or constrain for you.
Compare the two forms concretely: what a predicate, an index and a constraint can do with a legible value, and what has to move into application memory when the value is opaque.
Argue from consumers and operations — reports, exports, support queries, incident diagnosis — and describe the backfill with a dual-read window that re-encoding a populated column really requires.
Own it as a contract decision: name who may write the column, what queries must stay answerable, when opacity is a hard requirement, and what evidence would justify paying to change the encoding later.
## The question behind the question Every converted attribute is a small bet: that the value's meaning can live in the application and the database only needs to store bytes. Sometimes that bet is right and buys a much cleaner domain model. When it is wrong, the cost does not appear at the mapping — it appears months later in a report that cannot be written, a filter that must load a million rows to evaluate in memory, or a migration that has to touch every row of a large table. This is a design decision, not a mapping preference, and it is separate from choosing the column's declared type, its precision or its zone handling — those belong to whoever owns the schema's data types. The question here is only: **what shape does the value take on its way into a column that already exists?** ## Five axes to weigh 1. **Pushdown.** Will anything filter, sort, group, aggregate or constrain on this value? Everything the engine cannot evaluate, the application must, by loading rows it should never have loaded. 2. **Other consumers.** Reporting, exports, an operator running an ad-hoc query, a second service reading the same table. Each one either reads the value or reimplements the codec, and reimplemented codecs drift. 3. **Index usability.** A comparison the engine understands can use an ordinary index. A comparison that requires decoding first cannot, unless the engine can index the same expression. 4. **Cost of changing it.** Re-encoding a populated column is a backfill with a dual-read window, not a mapping edit. The larger the table and the more consumers, the more the first choice sticks. 5. **Opacity as a requirement.** Encryption, tokenisation and deliberate compaction are real requirements. When one of them applies, losing pushdown is the price you agreed to pay, and it should be recorded as such rather than rediscovered. ## How the common cases usually fall | Value | Stored form that usually wins | Why | |---|---|---| | A fixed set of constants | an explicit stable code, one column | filterable, groupable, legible in a report | | A monetary amount | a numeric amount plus a separate currency column | the engine can sum and compare; the pair keeps the amount meaningful | | A wrapped identifier | the inner value in its natural column type | joins and foreign keys need the plain value | | A multi-field value | flattened into named columns | each part is constrainable and indexable | | A sparse, open-ended structure nobody filters on | a document-shaped column | avoids a wide table of mostly-null columns | | A secret | an opaque encrypted value | pushdown is deliberately sacrificed | The pattern in the table is one rule: **store the form that keeps the questions answerable in the database**, and reach for an opaque form only when nothing will ask questions of it, or when opacity is the point. ## What a layer-only encoding really costs - **Every other consumer inherits a dependency** on one application's private format, usually without a specification. - **Predicates move into application memory**, turning a selective query into a full read plus a loop, which scales with the table rather than with the answer. - **Constraints disappear.** The engine cannot enforce uniqueness, a range or a reference on something it cannot interpret, so those rules survive only as long as every writer is well behaved. - **Operational work gets harder.** A support question that would be one query becomes a script, and an incident is diagnosed in hours rather than minutes. - **Aggregates and reporting need a second copy** of the data, which is a pipeline to build and keep correct. Against that, the honest benefits: one place owns the meaning, the domain type stays expressive, and the schema does not have to change every time an internal structure does. ## A workable default Start from the readable, engine-comparable form and demand a reason to deviate. When a value genuinely must be opaque, consider storing a **derived comparable column alongside it** — a status, a bucket, a normalised sortable key — so the common queries stay answerable while the sensitive form stays sealed. And decide these questions while the table is small: the whole decision is cheap before there are rows and expensive after, which is the real reason it belongs in a design discussion rather than in a mapping file nobody reviews.
- A value must be encrypted, but support still needs to find records by it. What do you do?Keep the sensitive value opaque and store a separate derived column that answers the query — a deterministic keyed digest for equality lookups, or a coarse bucket for ranges — accepting that the derived column leaks exactly what it lets you ask. That is a decision to make explicitly with whoever owns the data's protection, not a mapping detail.
- How do you re-encode a converted column once it holds millions of rows?Add a column for the new form, backfill in batches, write both forms while old rows remain, move readers over once the backfill completes, then stop writing and drop the old column. Every other consumer of the column has to move in the same window, which is why their existence belongs in the original decision.
- When is a document-shaped column the right call rather than a modelling failure?When the structure is genuinely open-ended and per-row, nothing filters or aggregates on its interior, and the alternative is a wide table of mostly-null columns or a generic attribute-value sprawl. The tell that it was wrong is the first request to filter on something inside it.
saying these in an interview costs you the question
- Chooses an opaque encoding because the domain type looked cleaner
- Forgets that reports and other services read the same table
- Assumes re-encoding a populated column is a mapping change
- Ignores that filtering in memory scales with the table
- Treats losing constraints and indexes as a minor detail
- Cannot say what would make them reverse the decision