Objectives

Upon completion of this lesson, you will be able to:

  • define a lookup tables
  • create lookup tables using SQL
  • use lookup tables to implement enumerated values

Overview

Lookup table, also often called association tables, are a kind of table that associates a value with other values. For example, a simple lookup table for a airline reservation system might associate airline codes with airline names and country of domicile. In such a table, a row might contain AA for the airline code, American Airlines for the airline name, and US for the country of domicile.

Each row in a lookup table is unique and thus the entire row forms a primary key. There is generally no surrogate key defined unless the lookup values are referenced from another table. Often lookup tables are used to populate drop-down menus for user interfaces.

Finally, lookup tables are used to implement enumerated values, i.e., catagorical variables. For example, to ensure that the value of an attribute is one of several predefined values, one would use a lookup table.

Example

The lookup table below associates various measurements. In particular, it maps men’s pants sizes from the German size scheme to the US one. So, to find the equivalent US size for a German size, is a simple “lookup”. For example, if you want to know the US size for the German size 52, you would look up “US Size Waist”and “US Size Inseam” in the row where the “German Size” column has the value “52”.

German Size Waist Size (cm) Length (cm) US Size Waist US Size Inseam
46 81 82 31 32
48 85 83 32 32
50 89 84 34 32
52 93 86 36 34
54 97 86 38 34
56 103 87 40 34
58 109 87 42 34
60 115 88 44 34
23 85 76 32 30
24 89 78 34 30
25 93 80 36 30
26 97 81 38 30
27 101 82 40 32
28 107 82 42 32
29 113 83 44 32
30 119 83 46 32
31 125 84 48 32
98 87 89 34 34
102 91 91 36 36
106 95 92 38 36
110 101 93 40 36

Creating Lookup Tables

Lookup tables are fixed and generally created and populated during the creation of the database. For a relational database, the CREATE TABLE and INSERT statements would appear in a database creation script.

CREATE TABLE IF NOT EXISTS sizeLookup (
  germanSize integer not null,
  waist_cm integer not null,
  length_cm integer not null,
  us_waist_in integer not null,
  us_inseam_in integer not null
)

Once the lookup table is created, it must be populated. For this example, we will only populate the table partially with values, for expediency.

INSERT INTO sizeLookup VALUES
 (46,81,82,31,32),
 (52,93,86,36,34),
 (25,93,80,36,30),
 (110,101,93,40,36)

So, if we need to look up (perhaps because we wish to convert from German to US sizes), what the equivalent US size is for the German size of “25”, we would issue the query:

SELECT us_waist_in, us_inseam_in 
  FROM sizeLookup
 WHERE germanSize = 25;
Table 1: 1 records
us_waist_in us_inseam_in
36 30

Enumerated Values

Aside from associating details or using lookup tables for conversion, lookup tables are also often use for enumerated values for attributes, particularly when a database does not natively support enumerations.

Let’s consider an example: a database contains a table that tracks team members on a project. Each team member has a role on the project and that role must be one of several specific and predefined roles, such as Analyst, Project Manager, Developer, or Tester, for the sake of argument. Once again, a lookup table would be ideal for this use case. Of course, a database might support enumerated types but even then it might be better to use a lookup table for portability reasons.

CREATE TABLE IF NOT EXISTS roles (
  rid integer not null primary key,
  rname text
)
INSERT INTO roles VALUES 
  (1, "Analyst"),
  (2, "Project Manager"),
  (3, "Developer"),
  (4, "Tester")
CREATE TABLE IF NOT EXISTS members (
  mid integer not null,
  mname text not null,
  mrole integer not null,
  primary key (mid),
  foreign key (mrole) references roles (rid)
)
INSERT INTO members VALUES
  (223, "Jeff Lohan", 3),
  (441, "Alison Chu", 1)

The use of a foreign key ensures that the role must be one of the roles defined in the roles table.

A query to list all members and their role would then be:

SELECT m.mname AS 'Name', r.rname AS 'Role'
  FROM members m INNER JOIN roles r ON (m.mrole=r.rid);
Table 2: 2 records
Name Role
Jeff Lohan Developer
Alison Chu Analyst

Many databases support enumerated types through user constraint definition. For example, SQLite allows for a CHECK constraint while other databases directly support an ENUM data type. So, an enumerated type can also be implemented as shown below, but since the levels of the enumerated type cannot be queried directly, it cannot be used to populate drop-down menus or enforcement through application logic.

CREATE TABLE IF NOT EXISTS members (
  mid integer not null,
  mname text not null,
  mrole text not null,
  primary key (mid),
  CHECK (mrole IN ('Analyst','Project Manager','Developer','Tester'))
)

We can realize a similar structure with lookup tables where the label becomes the primary key rather than having a synthetic key.

CREATE TABLE IF NOT EXISTS roles (
  rname text not null primary key
)
INSERT INTO roles VALUES 
  ("Analyst"),
  ("Project Manager"),
  ("Developer"),
  ("Tester")
CREATE TABLE IF NOT EXISTS members (
  mid integer not null,
  mname text not null,
  mrole text not null,
  primary key (mid),
  foreign key (mrole) references roles (rname)
)
INSERT INTO members VALUES
  (223, "Jeff Lohan", "Developer"),
  (441, "Alison Chu", "Analyst")

The use of a foreign key ensures that the role must be one of the roles defined in the roles table.

A query to list all members and their role would then be:

SELECT m.mname AS 'Name', m.mrole AS 'Role'
  FROM members AS m;
Table 3: 2 records
Name Role
Jeff Lohan Developer
Alison Chu Analyst

A join is no longer required as the role name must be one of the labels in the roles table.

So, as is so often the case, there are several ways to accomplish some goal.

Benefits of Lookup Tables

Some of the benefits of using lookup table in a relational database include these:

  • Storage Management: Lookup tables prevents repetition of same data and helps save the storage.

  • Improved Performance: When lookup tables are used for enumerated values or for storing detailed information, they improve performance because the “join” is done using key values.

  • Simple Data Modification: If any changes are to be made to an enumerated value or some details or a conversion, then those changes are made once in the lookup table. There is no need to update the references (i.e., foreign keys).

  • Faster Search: Since lookup table are generally small and have comparatively few rows, it becomes faster to search the data. In addition, since lookup tables are updated infrequently, indexing on all columns is feasible which increases searching even more.

Summary

Lookup tables provide a convenient way for storing associative values. They are useful for details or for conversions. In addition, lookup tables are also commonly used to populate choice controls in user interfaces. Lastly, lookup tables are ideal for implementing enumerated types.

Genesis, History, Use of Lookup Tables

In this perspective, Khoury Boston’s Prof. Feinberg takes a look at the genesis and use of lookup tables. Lookup tables are an important aspect of building fast and efficient databases and despite computational advances are still in common use.

Files & Resources

All Files for Lesson 60.115

References

None.

Errata

None collected yet. Let us know.