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, ...
Get MySQL Cookbook now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.