MySQL Cookbook

Using a Join to Create a Lookup Table from Descriptive Labels

Problem

A table stores long descriptive labels in an identifier column. You want to convert this column to short ID values and use the labels to create a lookup table that maps IDs to labels.

Solution

Use one of the related-table update techniques described in Recipe 12.19.

Discussion

It’s a common strategy to store ID numbers or codes rather than descriptive strings in a table to save space. It also improves performance, because it’s quicker to index and retrieve numbers than strings. (For queries in which you need to produce the names, join the ID values with an ID-to-name lookup table.) When you’re creating a new table, you can keep this strategy in mind and design the table from the outset to be used with a lookup table. But you may also have an existing table that stores descriptive strings and that could benefit from a conversion to use ID values. This section discusses how to create the lookup table that maps each label to its ID, and how to convert the labels to IDs in the original table. The technique combines ALTER TABLE with a related-table update.

Suppose you collect coins, and you’ve begun to keep track of them in your database using the following table:

CREATE TABLE coin
(
    id      INT UNSIGNED NOT NULL AUTO_INCREMENT,
    date    CHAR(5) NOT NULL,   # 4 digits + mint letter
    denom   CHAR(20) NOT NULL,  # denomination (e.g., Lincoln cent)
    PRIMARY KEY (id)
);

Each coin is assigned an ID automatically as an AUTO_INCREMENT value, ...