Normalization by Example

Lookup Tables

Store Labels Once

Repeated labels are easier to manage when the label lives in one lookup table and rows store the lookup key.

Program

Play the script to choose a category label and see the products that reference it.

wanted_label
lookup_table_design.sql
Replay: real traced execution (multi-file project)
CREATE TABLE products_raw (sku TEXT, name TEXT, category_label TEXT);
INSERT INTO products_raw VALUES ('P1', 'hammer', 'Tools'), ('P2', 'pen', 'Office'), ('P3', 'saw', 'Tools');
CREATE TABLE categories (category_id INTEGER, label TEXT);
INSERT INTO categories VALUES (1, 'Tools'), (2, 'Office');
CREATE TABLE products (sku TEXT, name TEXT, category_id INTEGER);
INSERT INTO products SELECT products_raw.sku, products_raw.name, categories.category_id FROM products_raw JOIN categories ON categories.label = products_raw.category_label;
WITH params(wanted_label) AS (VALUES ('Tools')) SELECT products.sku, products.name, categories.label FROM products JOIN categories ON categories.category_id = products.category_id WHERE categories.label = (SELECT wanted_label FROM params) ORDER BY products.sku;

CREATE TABLE products_raw (sku TEXT, name TEXT, category_label TEXT);
INSERT INTO products_raw VALUES ('P1', 'hammer', 'Tools'), ('P2', 'pen', 'Office'), ('P3', 'saw', 'Tools');
CREATE TABLE categories (category_id INTEGER, label TEXT);
INSERT INTO categories VALUES (1, 'Tools'), (2, 'Office');
CREATE TABLE products (sku TEXT, name TEXT, category_id INTEGER);
INSERT INTO products SELECT products_raw.sku, products_raw.name, categories.category_id FROM products_raw JOIN categories ON categories.label = products_raw.category_label;
WITH params(wanted_label) AS (VALUES ('Office')) SELECT products.sku, products.name, categories.label FROM products JOIN categories ON categories.category_id = products.category_id WHERE categories.label = (SELECT wanted_label FROM params) ORDER BY products.sku;

  1. tables ← 1 row

    1CREATE TABLE products_raw (sku TEXT, name TEXT, category_label TEXT);2INSERT INTO products_raw VALUES ('P1', 'hammer', 'Tools'), ('P2', 'pen', 'Office'), ('P3', 'saw', 'Tools');
    values this step1 rowtables

  2. products_raw ← 3 rows

    1CREATE TABLE products_raw (sku TEXT, name TEXT, category_label TEXT);2INSERT INTO products_raw VALUES ('P1', 'hammer', 'Tools'), ('P2', 'pen', 'Office'), ('P3', 'saw', 'Tools');3CREATE TABLE categories (category_id INTEGER, label TEXT);
    values this step3 rowsproducts_raw

  3. categories ← 2 rows

    3CREATE TABLE categories (category_id INTEGER, label TEXT);4INSERT INTO categories VALUES (1, 'Tools'), (2, 'Office');5CREATE TABLE products (sku TEXT, name TEXT, category_id INTEGER);
    values this step2 rowscategories

  4. products ← 3 rows

    5CREATE TABLE products (sku TEXT, name TEXT, category_id INTEGER);6INSERT INTO products SELECT products_raw.sku, products_raw.name, categories.category_id FROM products_raw JOIN categories ON categories.label = products_raw.category_label;7WITH params(wanted_label) AS (VALUES ('Tools')) SELECT products.sku, products.name, categories.label FROM products JOIN categories ON categories.category_id = products.category_id WHERE categories.label = (SELECT wanted_label FROM params) ORDER BY products.sku;
    values this step3 rowsproducts

  5. result ← 2 rows

    6INSERT INTO products SELECT products_raw.sku, products_raw.name, categories.category_id FROM products_raw JOIN categories ON categories.label = products_raw.category_label;7WITH params(wanted_label) AS (VALUES ('Tools')) SELECT products.sku, products.name, categories.label FROM products JOIN categories ON categories.category_id = products.category_id WHERE categories.label = (SELECT wanted_label FROM params) ORDER BY products.sku;
    values this step2 rowsresult

  1. tables ← 1 row

    1CREATE TABLE products_raw (sku TEXT, name TEXT, category_label TEXT);2INSERT INTO products_raw VALUES ('P1', 'hammer', 'Tools'), ('P2', 'pen', 'Office'), ('P3', 'saw', 'Tools');
    values this step1 rowtables

  2. products_raw ← 3 rows

    1CREATE TABLE products_raw (sku TEXT, name TEXT, category_label TEXT);2INSERT INTO products_raw VALUES ('P1', 'hammer', 'Tools'), ('P2', 'pen', 'Office'), ('P3', 'saw', 'Tools');3CREATE TABLE categories (category_id INTEGER, label TEXT);
    values this step3 rowsproducts_raw

  3. categories ← 2 rows

    3CREATE TABLE categories (category_id INTEGER, label TEXT);4INSERT INTO categories VALUES (1, 'Tools'), (2, 'Office');5CREATE TABLE products (sku TEXT, name TEXT, category_id INTEGER);
    values this step2 rowscategories

  4. products ← 3 rows

    5CREATE TABLE products (sku TEXT, name TEXT, category_id INTEGER);6INSERT INTO products SELECT products_raw.sku, products_raw.name, categories.category_id FROM products_raw JOIN categories ON categories.label = products_raw.category_label;7WITH params(wanted_label) AS (VALUES ('Office')) SELECT products.sku, products.name, categories.label FROM products JOIN categories ON categories.category_id = products.category_id WHERE categories.label = (SELECT wanted_label FROM params) ORDER BY products.sku;
    values this step3 rowsproducts

  5. result ← 1 row

    6INSERT INTO products SELECT products_raw.sku, products_raw.name, categories.category_id FROM products_raw JOIN categories ON categories.label = products_raw.category_label;7WITH params(wanted_label) AS (VALUES ('Office')) SELECT products.sku, products.name, categories.label FROM products JOIN categories ON categories.category_id = products.category_id WHERE categories.label = (SELECT wanted_label FROM params) ORDER BY products.sku;
    values this step1 rowresult
lookup table A lookup table stores shared labels once and gives each one a key.
foreign-key-shaped value `products.category_id` points at the category row.
label query The join recovers the readable label when a query needs it.