Lookup Table

A lookup table (also called a reference table or code table) stores a controlled set of valid values for a categorical attribute, replacing inline enumerations with a Foreign Key reference. This is the relational way of implementing enums.

Example

Instead of storing status as a free-text string:

-- Without lookup table (error-prone, inconsistent)
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    status VARCHAR(20)  -- 'Active', 'active', 'ACTIVE', 'Actve'...
);
 
-- With lookup table (enforced, consistent)
CREATE TABLE order_status (
    id SERIAL PRIMARY KEY,
    name VARCHAR(20) UNIQUE NOT NULL  -- 'Active', 'Shipped', 'Cancelled'
);
 
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    status_id INT REFERENCES order_status(id)
);

Benefits

• Data integrity — Only valid values allowed (enforced by Foreign Key)
• Storage efficiency — Integer FK vs repeated strings
• Performance — Integer comparisons and indexed joins are faster than string matching
• Easy modification — Change a label in one place, applies everywhere
• Searchability — Can add descriptions, sort orders, active flags to the lookup table

When to Use

• Any attribute with a fixed, enumerable set of values (status, category, country, type)
• When migrating from conceptual model to logical model — categorical attributes become lookup tables
• When multiple tables reference the same set of values