Relational Databases for Archaeology Main Menu

Lookup Tables

4
The one-to-many relationship has a special use case that offers another way to control your data integrity called lookup tables.  A lookup table is a special table used only to maintain a list of the options that you can select to fill a field in a main table.  Lookup tables are particularly useful for protecting the data entered into a character varying text field.  In the following example, we are interested in limiting the choices a user can make when entering a context type in their contexts table.  Therefore we have setup a contexts_type_lookup table that contains only one field.  That field, called context_type, simply lists all of the possible text options for filling in the context_type field in the contexts table.  In this way we control the data and make it easier to compare contexts with each other since they use a standard vocabulary.  In our contexts table we have only entered two of the possible types currently, but we are free to add or modify rows to use any of the four values currently listed in the lookup table.  We can also add new options to the lookup table later.
The lookup table works by establishing a one-to-many relationship from the contexts table to the contexts_type_lookup table.  Therefore, we add a foreign key in the contexts table to control the values in the contexts table's context_type field, based on values in the contexts_type_lookup table's context_type.  Note also that the context_type field of the contexts_type_lookup table is the unique primary key for that table.  This is also logical since it is the only field in the table.  Obviously, within the contexts table, the context_type field does not need to be a key or have any other constraint, since multiple rows can have the same value.
In this screencast, we setup the lookup table and populate values in that table.  Any character varying field in your database that has a limited set of options should have a lookup table.  Sometimes you may want to restrict other types of fields, such as a numeric that stores a four digit year.

 

Exercise: Setup Lookup Tables in your Database

We have a number of tables now in our database, and many of them contain character varying fields that should have limited options.  For example, in our containers table, the container_type and perhaps the shelf_location should probably have lookup tables.  Go through all of the tables you have constructed thus far and setup lookup tables for any field that would require a restricted set of options.
 

This page has paths:

  1. Relationships Peter Cobb

This page references: