Relational engines now offer array and document (JSON) column types that hold a whole collection in one cell. Does using one violate first normal form, and when is it nonetheless a defensible design decision?

## The formal answer First normal form requires each attribute value to be a single value of the attribute's domain. An array of tags, or a JSON object with nested keys the application reads individually, is a collection with internal structure the schema depends on. That is not atomic, so such a table is not in 1NF. The fact that the engine provides operators for reaching inside does not change the classification — it just makes the violation comfortable. Be able to say this crisply, because interviewers ask it to see whether you will defend a fashionable choice on principle rather than on trade-offs. Some writers argue the opposite: that a domain can be array-valued, so a column of type `text[]` holds one value of the array domain and is therefore atomic. Acknowledge the argument, then dismiss it on engineering grounds — the moment your queries decompose the value, the database is doing relational work on sub-parts that it cannot constrain or estimate, and every practical consequence of a 1NF violation follows. ## What you actually lose **Referential integrity — the decisive loss.** A foreign key constrains a column value. There is no way to declare that every element of `tag_ids` must exist in `tag(id)`. Nothing stops ids of deleted rows lingering forever, and `ON DELETE` has nothing to act on. If elements reference other entities, this alone usually settles the argument in favour of a junction table. **Per-element constraints.** "Every line item has a positive quantity", "at most one address is primary", "skus are unique within the document" — all become application-level rules, enforced by the last developer who remembered. In a child table they are `CHECK`, `UNIQUE` and partial unique indexes that the database enforces for everyone including the ad-hoc script someone runs at midnight. **Optimizer statistics.** The engine collects statistics per column. For a document column those statistics describe the whole value, so the selectivity of "documents whose status field is ACTIVE" is a guess. Bad estimates produce bad join orders, and the failure shows up as a query that was fast with 10,000 rows and pathological at 10 million. **Write behaviour.** In most implementations a document is stored as one value; changing one element rewrites the whole thing. Consequences: write amplification on large documents, more work for the write-ahead log and replication, potential out-of-line storage churn, and the return of read-modify-write lost updates when two writers each modify a different element. A child table turns those into two independent single-row writes. **Unbounded growth.** A collection inside a row has no natural limit, so one pathological parent (an order with 40,000 line items) produces a single enormous row that hurts every operation touching it. Rows in a child table spread that cost naturally. **Query and tooling friction.** Filtering on elements needs engine-specific syntax and specialised index types; reporting tools, migrations and downstream consumers must learn the document's implicit schema, which no catalog describes and nothing validates. ## What you legitimately gain Against a hand-rolled delimited string, a native type is strictly better: types are preserved, the engine can validate and index, and no application invents its own parser. Against a normalized child table, the genuine wins are: - **Fetch locality** — the whole aggregate arrives with its parent row, no join, no N+1. - **Schema flexibility** for genuinely heterogeneous or third-party-defined data whose shape you do not control and should not model. - **Fewer tables** for data that is only ever consumed whole. ## The decision test Use a document or array column only when *all* of these hold: 1. The collection is always read and written **as a whole** with its parent row. 2. No query filters, joins or aggregates on individual elements — or if a rare one does, a full scan is acceptable. 3. No element needs a foreign key or a database-enforced constraint. 4. The collection is **bounded and small**. 5. Its shape is genuinely variable, or genuinely owned by an external system. If any of these fail, normalize. Classic good fits: user preference blobs, a captured webhook payload kept for audit, feature flags, a rendered snapshot for display. Classic bad fits: order line items (you will report on them), tags (you will search by them), permissions (they need integrity), anything with a foreign key inside it. ## Hybrid patterns - **Normalized source of truth plus a document column as a cached read model**, refreshed on write. You keep integrity and pay one denormalized copy for read speed. - **Promoted columns**: keep the document, but extract the two or three fields you actually filter on into real, indexed, constrained columns — often as generated/computed columns so they cannot drift from the document. - **Start normalized.** Going from tables to a document is a mechanical aggregation; going from a document back to tables means backfilling data that has been accumulating unvalidated for two years, and discovering that the implicit schema had six variants. ## What to say in an interview "Strictly it is not 1NF, and the concrete price is referential integrity, per-element constraints and cardinality estimates. I use it when the value is opaque to the database — small, bounded, always read whole, never joined or filtered per element, no integrity rules — and I normalize otherwise, sometimes promoting a couple of fields into real columns to keep the queries I need indexable."