O'Reilly logo

Joe Celko's Thinking in Sets: Auxiliary, Temporal, and Virtual Tables in SQL by Joe Celko

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

4.4. OTLT or MUCK Table Problems

I think that Paul Keister was the first person to coin the phrase “one true lookup table” (OTLT) for a common SQL programming technique that is popular with newbies. Don Peterson (www.SQLServerCentral.com) gave the same technique the name “massively unified code-key” or MUCK tables in one of his articles.

The technique crops up time and time again, but I will give Paul Keister credit as the first writer to give it a name. Simply put, the idea is to have one table to do all of the code lookups in the schema. It usually looks like this:

CREATE TABLE Look-ups (code_type CHAR(10) NOT NULL, code_value VARCHAR(255) NOT NULL, -- notice size! code_description VARCHAR(255) NOT NULL, -- notice size! PRIMARY KEY (code_value, ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required