Normalization by Example

Repeating Groups

Split Items into Rows

A table with item_1 and item_2 columns is hard to query as the number of items grows. A normalized item table stores one item per row.

Program

Play the script to choose an order and compare the normalized item rows.

wanted_order
repeating_group_split.sql
Replay: real traced execution (multi-file project)
CREATE TABLE raw_orders (order_id INTEGER, customer TEXT, item_1 TEXT, item_2 TEXT);
INSERT INTO raw_orders VALUES (100, 'Ada', 'pen', 'notebook'), (101, 'Lin', 'bag', 'pen');
CREATE TABLE order_items (order_id INTEGER, item TEXT);
INSERT INTO order_items SELECT order_id, item_1 FROM raw_orders UNION ALL SELECT order_id, item_2 FROM raw_orders;
WITH params(wanted_order) AS (VALUES (100)) SELECT order_id, item FROM order_items WHERE order_id = (SELECT wanted_order FROM params) ORDER BY item;

CREATE TABLE raw_orders (order_id INTEGER, customer TEXT, item_1 TEXT, item_2 TEXT);
INSERT INTO raw_orders VALUES (100, 'Ada', 'pen', 'notebook'), (101, 'Lin', 'bag', 'pen');
CREATE TABLE order_items (order_id INTEGER, item TEXT);
INSERT INTO order_items SELECT order_id, item_1 FROM raw_orders UNION ALL SELECT order_id, item_2 FROM raw_orders;
WITH params(wanted_order) AS (VALUES (101)) SELECT order_id, item FROM order_items WHERE order_id = (SELECT wanted_order FROM params) ORDER BY item;

  1. tables ← 1 row

    1CREATE TABLE raw_orders (order_id INTEGER, customer TEXT, item_1 TEXT, item_2 TEXT);2INSERT INTO raw_orders VALUES (100, 'Ada', 'pen', 'notebook'), (101, 'Lin', 'bag', 'pen');
    values this step1 rowtables

  2. raw_orders ← 2 rows

    1CREATE TABLE raw_orders (order_id INTEGER, customer TEXT, item_1 TEXT, item_2 TEXT);2INSERT INTO raw_orders VALUES (100, 'Ada', 'pen', 'notebook'), (101, 'Lin', 'bag', 'pen');3CREATE TABLE order_items (order_id INTEGER, item TEXT);
    values this step2 rowsraw_orders

  3. tables ← 2 rows

    2INSERT INTO raw_orders VALUES (100, 'Ada', 'pen', 'notebook'), (101, 'Lin', 'bag', 'pen');3CREATE TABLE order_items (order_id INTEGER, item TEXT);4INSERT INTO order_items SELECT order_id, item_1 FROM raw_orders UNION ALL SELECT order_id, item_2 FROM raw_orders;
    values this step2 rowstables

  4. order_items ← 4 rows

    3CREATE TABLE order_items (order_id INTEGER, item TEXT);4INSERT INTO order_items SELECT order_id, item_1 FROM raw_orders UNION ALL SELECT order_id, item_2 FROM raw_orders;5WITH params(wanted_order) AS (VALUES (100)) SELECT order_id, item FROM order_items WHERE order_id = (SELECT wanted_order FROM params) ORDER BY item;
    values this step4 rowsorder_items

  5. result ← 2 rows

    4INSERT INTO order_items SELECT order_id, item_1 FROM raw_orders UNION ALL SELECT order_id, item_2 FROM raw_orders;5WITH params(wanted_order) AS (VALUES (100)) SELECT order_id, item FROM order_items WHERE order_id = (SELECT wanted_order FROM params) ORDER BY item;
    values this step2 rowsresult

  1. tables ← 1 row

    1CREATE TABLE raw_orders (order_id INTEGER, customer TEXT, item_1 TEXT, item_2 TEXT);2INSERT INTO raw_orders VALUES (100, 'Ada', 'pen', 'notebook'), (101, 'Lin', 'bag', 'pen');
    values this step1 rowtables

  2. raw_orders ← 2 rows

    1CREATE TABLE raw_orders (order_id INTEGER, customer TEXT, item_1 TEXT, item_2 TEXT);2INSERT INTO raw_orders VALUES (100, 'Ada', 'pen', 'notebook'), (101, 'Lin', 'bag', 'pen');3CREATE TABLE order_items (order_id INTEGER, item TEXT);
    values this step2 rowsraw_orders

  3. tables ← 2 rows

    2INSERT INTO raw_orders VALUES (100, 'Ada', 'pen', 'notebook'), (101, 'Lin', 'bag', 'pen');3CREATE TABLE order_items (order_id INTEGER, item TEXT);4INSERT INTO order_items SELECT order_id, item_1 FROM raw_orders UNION ALL SELECT order_id, item_2 FROM raw_orders;
    values this step2 rowstables

  4. order_items ← 4 rows

    3CREATE TABLE order_items (order_id INTEGER, item TEXT);4INSERT INTO order_items SELECT order_id, item_1 FROM raw_orders UNION ALL SELECT order_id, item_2 FROM raw_orders;5WITH params(wanted_order) AS (VALUES (101)) SELECT order_id, item FROM order_items WHERE order_id = (SELECT wanted_order FROM params) ORDER BY item;
    values this step4 rowsorder_items

  5. result ← 2 rows

    4INSERT INTO order_items SELECT order_id, item_1 FROM raw_orders UNION ALL SELECT order_id, item_2 FROM raw_orders;5WITH params(wanted_order) AS (VALUES (101)) SELECT order_id, item FROM order_items WHERE order_id = (SELECT wanted_order FROM params) ORDER BY item;
    values this step2 rowsresult
repeating group Columns such as `item_1` and `item_2` repeat the same idea inside one row.
first normal form A first-normal-form design stores repeating values as rows instead of numbered columns.
UNION ALL `UNION ALL` can reshape fixed repeated columns into row-shaped data.