First Normal Form: Atomic Values

Why atomic values are the floor for every query

Relational operations (JOIN, SUM, GROUP BY, WHERE) and the indexes that speed them up all work on whole column values. The moment one cell hides several facts, those operations stop working. You cannot SUM a quantity trapped inside the string 'mouse:2, keyboard:1', and you cannot JOIN on a product that is buried next to another product in the same row. First Normal Form (1NF) is the rule that keeps every value reachable: one value per cell, and one row per fact.

What 1NF requires

Two conditions:

• Every cell holds a single, atomic value. No comma-packed lists, no key:value blobs.
• There are no repeating groups. A table must not store the same kind of fact in parallel columns like product_a, product_b, product_c.

Both failures are the same mistake in different clothes: a one-to-many relationship (one order has many line items) crammed into one row. 1NF fixes it by moving the "many" side into its own rows. More rows, never more columns.

The grain of the table changes when you fix it. raw_order has one row per order. The 1NF table has one row per line item, so order_id alone now repeats and can no longer identify a row. You declare a composite key (order_id, product): the pair is unique even though neither column is on its own.

Unpacking parallel slots

raw_order stores two slots per order:

Unpack one slot per INSERT ... SELECT, and skip empty slots so they do not become phantom rows:

INSERT INTO order_item (order_id, product, qty)
SELECT order_id, product_a, qty_a FROM raw_order;

INSERT INTO order_item (order_id, product, qty)
SELECT order_id, product_b, qty_b FROM raw_order
WHERE product_b IS NOT NULL;

Result (one row per line item):

Row order in a table is not meaningful; only ORDER BY at read time defines it.

Pitfall: the empty second slot

Order 2 has no slot B, so its product_b and qty_b are NULL. Without WHERE product_b IS NOT NULL, the second INSERT would add a row like (2, NULL, NULL): a line item for nothing. That row is meaningless, and a NULL in a key column defeats the composite key you are trying to build. Filtering empty slots is not optional cleanup; it is what keeps the grain honest.

The mirror pitfall is "fixing" a multi-valued column by adding more slot columns. That is still a repeating group, and it hard-caps you at however many slots you defined. The answer is always more rows.

Interview nuance: "atomic" is defined relative to how the domain queries the value, not by whether the string looks divisible. A full name in one name column is fine until you need to filter or join by last name, at which point it is a 1NF violation for that domain. A DATE is atomic even though it contains a year, month, and day, because you query it as one value. Interviewers probe this: atomicity is a modeling decision about access patterns, not a syntactic property of the data.