When a converter stores an enumeration by position rather than by name, what changes and what breaks?
answer
- declaration order is not data
- a rename is visible, a reorder is not
- an explicit code decouples both
- decide what an unknown stored value does
basics
~20 sPosition stores the constant's index in declaration order, so inserting or reordering constants silently re-points every existing row. The name survives reordering but breaks on a rename. An explicit, stable code declared per constant avoids both failure modes.
solid answer
~50 sStoring the position writes a small integer — the constant's index in the order the constants are declared. It is compact and cheap to index, and it couples stored data to source-file order: insert a constant in the middle, or sort the list alphabetically, and every existing row now decodes to a different meaning with no error anywhere. Storing the name writes the constant's identifier as text: wider, readable in ad-hoc queries, immune to reordering, but broken by a rename, which at least a rename is a visible code change. The robust option is a third one — an explicit code declared per constant (`"ACT"`, `"CNC"`, or a number chosen once) that is never reused and never derived from source order. Whatever you pick, decide up front what a read does when a stored value no longer exists in the code.
go deeper
Remember the two encodings and the one-line risk of each: position breaks when constants move, name breaks when a constant is renamed. Say which you would pick and why.
Explain the mechanism — the position is a by-product of source order, so an insert or a sort shifts every stored row's meaning with no error — and describe the explicit-code alternative.
Bring the operational angle: a stored value the code no longer defines, a rolling deploy where two versions write, and the fact that re-encoding existing rows is a backfill with a dual-read window, not a converter edit.
Frame the column as a published contract. Reports, exports and other services read it, so the encoding's stability and legibility outrank the few bytes an integer position saves in the index.
## What a converter is doing here A fixed set of named constants has no native column type, so something must encode it. A **converter** is the layer's declared two-way function for one attribute: an object-side value goes in, a column value comes out on the way to the database, and the reverse runs on the way back. For an enumeration there are three encodings in common use, and the choice is a data-durability decision, not a style one. | Encoding | Column | Survives reordering | Survives a rename | Readable in plain SQL | Note | |---|---|---|---|---|---| | Declaration position | small integer | **no** | yes | no | compact, silently coupled to source order | | Constant name | short text | yes | **no** | yes | wider column and index | | Explicit declared code | text or integer | yes | yes | usually | the mapping owns the code, not the compiler | ## Why position fails quietly The position is not data the author ever wrote down; it is a by-product of the order constants happen to appear in. Three ordinary edits change it: 1. **Inserting a constant in the middle** — every constant after it shifts by one, and every stored row after that point now decodes to its neighbour. 2. **Removing a retired constant** — the same shift, in the other direction. 3. **Reordering for readability** — an alphabetical sort, or a merge that reunites two branches' additions in a different order. None of these raises an error. The column still holds a valid small integer, the read still succeeds, and every affected row now means something else. The defect surfaces later as a report with a strange distribution, or an order stuck in a state nobody set. This is why the position encoding is treated as a trap rather than an optimisation: the storage it saves is a few bytes per row, and the failure it invites is silent data corruption at rest. The name encoding fails on rename, which sounds symmetrical but is not. A rename is a deliberate, visible edit to the constant, usually done through tooling that touches every reference — a moment when someone can notice that stored rows also carry the old spelling. Reordering is invisible and often not even the author's intent. ## The explicit-code pattern Declaring the stored value per constant decouples storage from both source order and source spelling: - The code is chosen once and **never reused** for a different meaning, even after the constant is retired. - Renaming the constant in code changes nothing on disk. - Reordering changes nothing on disk. - Reports and other consumers read a stable vocabulary that does not move under them. The cost is a small amount of ceremony per constant and one more thing to review: a duplicated or recycled code is now a bug the compiler cannot see, so a startup or test assertion over uniqueness earns its keep. ## Reading a value the code no longer has Rows outlive deployments, so eventually a column holds a code the current version does not define — a constant retired last quarter, or a value written by a newer instance during a rolling deploy. There are three honest policies: 1. **Fail the read.** Loud, correct, and it turns one bad row into a broken endpoint. 2. **Map to a sentinel** meaning “unrecognised”. Survivable for read paths, but dangerous if the object is ever written back: unless the sentinel carries the original text, saving it overwrites the real value with the sentinel's own code. 3. **Exclude such rows in the query** with a predicate on the known codes, which keeps the code path clean and quietly hides data. The policy should be a deliberate choice per attribute, not whatever the layer happens to default to. ## Consequences beyond the round trip - **Ordering.** Sorting by the column sorts the *stored* form: alphabetical for names, declaration order for positions. Neither equals the domain's order (`DRAFT` before `SUBMITTED` before `APPROVED`) unless the code deliberately encodes it, so a status ordering usually needs its own sort key or an explicit case expression. - **Filtering.** A predicate has to compare the stored form. Whether the layer applies the converter to a bound parameter varies, and a hand-written or set-based statement certainly does not — those must spell the stored code themselves. - **Changing the encoding later is a data migration**, not a mapping edit. Editing the converter alone reinterprets every existing row in place. The safe route is to add the new form, backfill it, read both while old rows remain, then cut over and drop the old column. - **Index size** is the only real argument for positions, and it is a small one for a set of a dozen constants.
- A row holds a code the current version of the application no longer defines. What are the options?Fail the read loudly, map it to a sentinel meaning “unrecognised”, or exclude such rows with a predicate. The sentinel is the dangerous one: unless it carries the original stored text, saving that object writes the sentinel's own code and destroys the real value. Whichever you pick, make it an explicit decision per attribute rather than an accident of the layer's default.
- How do you change the encoding once millions of rows already exist?As a data migration, never as a converter edit. Add a column for the new form, backfill it, have the application write both and read the new one while old rows may still exist, then cut over and drop the old column. Swapping the converter in place silently reinterprets every stored row, and the old meaning is gone.
- Why does sorting by the encoded column rarely give the order the domain wants?The database sorts the stored form. Names sort alphabetically, positions sort in declaration order, and neither is a workflow order such as draft, submitted, approved unless someone deliberately made the codes sort that way. If the domain's order matters, store an explicit sort key or express the order in the query rather than relying on the encoding.
saying these in an interview costs you the question
- Says positions are safe because nobody reorders constants
- Thinks inserting a constant in the middle is harmless
- Assumes the name encoding survives a refactoring rename
- Expects sorting by the encoded column to give domain order
- Treats swapping the converter as a mapping change, not a migration
- Has no answer for a stored value the code no longer defines