What is a lookup table?

user_226897 0
Viewed 101099

I just gave a database diagram for a DB I created to our head database person, and she put a bunch of notes on it suggesting that I rename certain tables so it is clear they are lookup tables (add "lu" to the beginning of the table name).

My problem is that these don't fit the definition of what I consider a lookup table to be. I have always considered a lookup table to basically be a set of options that don't define any relationships. Example:

luCarMake
-----------
id    Make
--    ---------
1     Audi
2     Chevy
3     Ford

The database person at my work is suggesting that I rename several tables that are just IDs mapping one table to another as lookup tables. Example (Location_QuadMap below):

Location
----------
LocationId
name
description

Location_QuadMap <-- suggesting I rename this to luLocationQuad
----------------
QuadMapId
LocationId

luQuadMap
---------
QuadMapId
QuadMapName

Is it safe to assume that she misread the diagram, or is there another definition that I am not aware of?

database-design terminology database
8 Answers

One use of lookup table is to store otherwise enum values.

Say, we have a Status enum.

Instead of saving "Not Started", "In Progress", "Completed", "Sent Back"... in every record in the database, we are saving integer 1, 2, ... only.

In the programming side, the ORM like Entity Framework can easily convert an underlying integer into an Enum Type.

In this way, the drawback is the integer value is not readable from the database side. In solving this problem, we add a Lookup Table like

Id   Status
1    Not Started
2    In Progress
...

So that our DBA can have a dictionary to "lookup", showing the status text by joining with this lookup table.

user_8636370

I'd like iterate on the answer @Blaise gave.

This adresses mostly the title of this question and not the actual question itself, but I still want to point out what I know as a "lookup table".

In the company where I work at we develop a web app with lots of "enum" values. Imagine that in almost every HTML form there is at least one dropdown with +10 options to pick from. In other forms there may be dozens of checkboxes you can tick to enable or disable some aspects of our system.

Historically I have hardcoded this type of data into my program because I didn't see it as "live" data and therefore not worthy of saving in a relational db. But then a collegue of mine suggested doing so and I quickly adopted that.

An example of such a lookup table (incl. data):

feature
-------
id int (PK): 1, 2, ...
key varchar: "fulltextSearch", "apiDebugger"
name: "Use fulltext search", "Enable API debugging console"

Tables like these would have dozens of rows representing system settings, feature flags, etc.

This gets interesting and useful when developing a system with multitenancy in mind. In such a system it could be possible to manage settings / features per tenant with a n-m relation table:

tenant
______
id int (PK): 1, 2, ...
name varchar: "Your Company", "My Company"

tenant_feature
______________
tenant_id int (foreign key to tenant.id)
feature_id int (foreign key to feature.id)
feature_config varchar: {...}, {...}

tenant_feature now serves not only as the table linking tenants to features but also contains added information like configuration metadata for a given feature.

I haven't used this pattern on a lot of applications, but in my current project this allows me a couple of things to do:

  • If business requires new features / settings / etc. to be disabled or rolled out step by step for only some systems, I can simply add a active column to the lookup table and use that instead of building separate applications, one with a feature enabled, one with it disabled
  • If enum values are added, changed and removed frequently, this allows me to quickly build an API arround this table to enable key users to do that for me.
user_36477820
Related