First Normal Form (1NF)

Normalization starts with a table in the strict sense: every cell holds 1 value, and every row has the same columns. Spreadsheet and JSON exports break this rule constantly, and the result looks convenient until a query has to look inside a cell. Here are the same store's orders, exported that way.

One row is 1 order, and product_ids holds the basket as a comma-separated string, with a matching string of quantities beside it. The pairing is positional, and nothing enforces that the 2 lists have the same length. Counting the orders that contained a Desk Lamp means looking inside a string.

SELECT
  COUNT(*) AS orders_with_p7
FROM orders_flat
WHERE product_ids LIKE '%P7%'

The query counts orders 1001 and 1002, which is right today. Once product P71 launches, its orders count as Desk Lamp orders too, because the string P71 contains P7. Summing quantities is worse: split both strings, walk them in lockstep, convert the pieces to numbers, and a stray space in 1 cell breaks the conversion. That parsing logic gets written again in every report that touches the column.

The same mistake laid out sideways

Numbered columns remove the parsing and add a wall: at most 3 products per order, so a fourth item has nowhere to go. Counting Desk Lamp orders means checking 3 columns with OR, and a report written for 3 slots silently ignores a fourth slot added later. Columns like these are called a repeating group, and they hold a list spread sideways across the row.

One value per cell, one row per item

For both shapes, the fix is to turn the list into rows. A row now stands for 1 item in 1 basket, keyed by order_id together with product_id, and an order can have 1 item or 40. Each quantity sits on the same row as the product it counts, so the table enforces the pairing. The Desk Lamp question becomes a filter and a sum.

SELECT
  SUM(qty) AS desk_lamps_sold
FROM order_lines
WHERE product_id = 'P7'

The query needs no string parsing, cannot match P71 by accident, and reads the same for 1 line or 100. The split did cost something: order_date and customer_id are now written once per line, so order 1001's date appears twice. First normal form allows that; the next section deals with it.

Do

• Store a list as rows in a table keyed by the owner and the item
• Keep a quantity on the same row as the item it counts
• Ask what 1 row means before asking anything else about a table

Avoid

• Store comma-separated ids and promise to parse them later
• Add item_4 when item_3 runs out
• Match ids with LIKE, which finds P71 when you asked for P7

A playlists table has 1 row per playlist with a track_ids column holding values like T15,T2,T9. Which change puts the table in first normal form?

• Store the track ids as a JSON array string instead, so the list structure is explicit and parsers agree on it.
• Split the list into track_1, track_2 and track_3 columns so that each cell holds exactly 1 id.
• Sort the ids inside the string so that LIKE matches are consistent and T2 can be found before T15.
• Create a playlist_tracks table with 1 row per playlist and track, keyed by (playlist_id, track_id).

About this lesson

This section is part of the Normalization lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.