Lookup tables are a critical feature of good database design: lookup tables store information that other tables can refer to (or look up). These other tables are typically transaction tables that store things that happen, while the data stored in lookup tables is more static. Even for those new to database, lookups are typically a familiar tool: in Excel, VLOOKUP, HLOOKUP and XLOOKUP are frequently used functions, highlighting the importance of lookups in general.
Let's explore lookup tables, how they are used, and why they matter.
What are Lookup Tables?
Lookup tables are tables that store static data that is referred to by other, transactional tables. Simple lookup tables consist of key-value pairs.
Lookup Tables: A Simple Example
For example, let's say you have a database table with customer information: every day your business adds new customers to the database, and the customer table keeps growing and growing. This is your transactional table.
Let's further assume that the customer table contains a field for "Country" where customers select the country they are from: AU for Australia, US for the United States, or FR for France (these two-letter country codes are called Alpha-2 codes).
This is where the lookup table comes in. The lookup table contains (static) information about countries: the full country name (instead of the Alpha-2 code), the continent, the country code, the sales territory it belongs to, and its manager.
What's the advantage of having a transactional and a lookup table?
It's simple: by designing your database with a lookup table, you avoid repeating the same information inside your customer table. This makes your database leaner, more efficient, and faster to query.
Other typical examples of lookup tables are:
Orders (a transactional table) and Product Catalogue tables (a lookup table), where one order can contain many products. The relationship between these two tables is typically established through an SKU (Stock Keeping Unit).
Tickets (a transactional table) and Error Codes (a lookup table): tickets are raised by customers who encounter an error or fault, and the code is used to lookup its cause, remedies, or other related information.
Companies (a transactional table) and Industry Classifications (a lookup table): companies are added to a B2B database, and classified as members of a certain industry by referring to the Standard Industrial Classification (SIC) code.
These are just some examples of lookup tables in the real-world.
Let's continue by understanding how to create a lookup table: we start by creating a lookup table in Excel, and then move on to creating a lookup in SQL.
Creating a Lookup Table in Excel
To create a lookup table in Excel, open up Excel and create a blank workbook. Rename Sheet 1 into "Transactions" and Sheet 2 into "Lookup".
Start by filling in some dummy data into Transactions:
CustomerID | Name | Country |
1 | Bill Gates | US |
2 | Drake | CA |
3 | Lionel Messi | AR |
Continue by filling in data into Lookup:
Country Alpha-2 Code | Country | Continent |
US | United States | North America |
CA | Canada | North America |
AR | Argentina | South America |
This is all we need in terms of data to understand the principle of lookup tables.
Now, let's perform a simple XLOOKUP, which uses this syntax:
= XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
By using XLOOKUP, we can lookup the Alpha-2 Country Code from our Transactions table in the Lookup table and retrieve the continent for each transaction.
To do so, add this formula to cell D2 of your transactions table:
=XLOOKUP(C2, Lookup!$A$2:$A$4, Lookup!$C$2:$C$4)
This formula looks up "US" in C2 inside column A of our lookup table and returns the value stored in column C of our lookup table. The result: United States.
By using XLOOKUP we have linked two tables to each other, and we don't have to constantly repeat information about continents.
Creating a Lookup Table in SQL
To create a lookup in SQL, create two tables, just like we did in Excel. However, instead of writing a XLOOKUP formula, we elegantly relate the transactional and lookup tables to each other through the use of a foreign key.
In an SQL database, a record's primary key uniquely identifies said record. By inserting one table's primary key into another table, we establish a relationship between these two tables (learn more about one-to-many and many-to-many relationships here).
In our example, we can make the country's Alpha-2 code our unique identifier for countries, and use it as a foreign key inside our lookup table. SQL will now understand that these two tables are joined through the use of the country key (the Alpha-2 code).
We can now write powerful SQL queries to answer questions such as how many customers live in each continent. If our customer or country table contained more information, such as order volume or other numeric data, we could easily carry out further analysis of our customers by country or continent in just a few lines of SQL.
SQL databases are a great way to understand and build lookup tables: once you get the hang of it, they give you an easy way to create highly-performant, beautifully designed databases that allow for granular analysis.
Lookup Tables Explained
"Don't look up" - not in Excel or SQL! Lookups are an essential part of designing databases, tables and relating data to each other. Through the use of lookups, transactional tables can be kept lean, only storing the most critical and unique transactional data.
Lookup tables, on other hand, can serve as a "dictionary" that is referred to when static information is used in queries or analysis for example. In our case, continents is a static data point and a prime example for data best stored in a lookup table.
SQL is a more efficient way to use lookups than Excel, as it only takes a relationship between two tables to perform lookups. In Excel, however, a formula is required.