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.

repeating_group_split.sql 
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 ()) 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 ()) SELECT order_id, item FROM order_items WHERE order_id = (SELECT wanted_order FROM params) ORDER BY item;
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.