Reference Tables

1. Features of Reference Tables

A reference table is frequently also called a lookup table. Any database is bound to have at least a few of these types of tables. Reference tables generally have at least two columns set up as key-value pairs, where the first column is the key, and the second column (or more columns) have the value(s). These reference tables are used when there is no need to store the history of the reference data. This is often the case when you have data that is not going to change, or will rarely change.

Examples of this kind of data could include:

• product codes
• stock exchange symbols
• international currency codes
• country abbreviations
• US state code abbreviations

Reference tables and lookup tables are very similar to one another. There is no standard difference between them, but the accepted difference is that a reference table is meant to have a long value that is stored outside of the main table, like a customerID. A lookup table generally has a list of allowable values for a certain field.

think about it

The primary key of the reference or lookup table is either an auto-increment value or an abbreviation, while the value column has the full reference. By using a lookup table, you can ensure that the values that are being referenced come only from a set number of items. You can add to the set number of items if needed, but this will be rare.

EXAMPLE

For example, in your movie database, you have the Genre table as a reference table. The GenreName can contain values like Action, Adventure, Comedy, Drama, Horror, Romance, Science Fiction, and so forth. If the genre field were to be free form, without a lookup table, a user could enter in anything they wanted like “Action”, “action”, “Action Film”, ”Action Genre”, etc. There could potentially be a large number of entries that would make it quite difficult to manage, so using a lookup table helps to eliminate a lot of data redundancy and results in storage savings. Having a lookup table, and using foreign keys, can ensure that the set of values will only come from a certain list. This can also help to increase the performance of some queries, since there are fewer values to search.

term to know

Lookup Table

A reference table is frequently also called a lookup table. There is no standard difference between them, but the accepted difference is that a reference table is meant to have a long value that is stored outside of the main table. A lookup table generally has a list of allowable values for a certain field.

2. Reference Table Examples

In looking at your tables in PostgreSQL, you can see a lookup table is used for the genre:

You can also see a lookup table for media_type:

In both tables, you have a set number of rows that won’t change. These lookup tables could also be called reference tables, as the media_type_id and genre_id are used in the track table in place of the full text in the name for each of the tables.

reflect

One of the benefits of using these values is the flexibility for data modification. If you needed to update a media type name, rather than change it in every referenced value, it can be done in the media_type table. There may be some questions about what types of genres you offer. Rather than querying the track table to find out, it’s much faster to go directly to the lookup table to find those values. This approach helps to centralize the data that is about the key.