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.

lookup_table_design.sql 
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 ()) 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 ()) 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;
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.