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.
Use one of the related-table update techniques described in Recipe 12.19.
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
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, ...