What is a lookup table?

Question

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?

Mark Byers · Accepted Answer · 2010-08-05T23:07:18.867

55

What you have there is called a junction table. It is also known as:

cross-reference table
bridge table
join table
map table
intersection table
linking table
link table

But I've never seen the term "lookup table" used for this purpose.

edited Aug 05 '10 at 23:07

answered Aug 05 '10 at 23:06

Mark Byers

811,555
193
1,581
1,452

9

My last manager constantly referred to them as lookup tables. That's the only reason I knew what the table was before I saw the layout given. – Justin Niessner Aug 05 '10 at 23:10
I've also heard this called a 'pass through table'. – siliconrockstar Jun 28 '16 at 02:04
Yep...the first example he gave was a lookup...what he actually has is a junction/join. – steviesama Mar 05 '19 at 23:50

score 25 · Answer 2 · answered Aug 05 '10 at 23:10

25

Pick your battles, but I'd ask for the person to clarify the naming convention seeing that they've suggested using the same convention for one-to-many and many-to-many relationships. Looks like any foreign key relationship means there's a "lookup" table involved.

If that's the naming convention for other databases, then I wouldn't push my luck.

answered Aug 05 '10 at 23:10

OMG Ponies

325,700
82
523
502

2

Well said. There are more important things to dig your heels in about. – Toby Allen Aug 06 '10 at 00:09
3

Couldn't agree more with your "pick your battles" comment. – Abe Miessler Aug 06 '10 at 04:17
2

Although there is some wisdom in this, words mean things and the misuse of "Lookup table" to describe a junction table will be confusing. No doubt I'd have difficulty communicating in the real world if I insisted on calling all cats "rats". I would strive to tactfully educate before acquiescing. Still, "when in Rome... do as the Roman's do". – ScottWelker Sep 26 '19 at 13:11

score 20 · Answer 3 · answered Aug 05 '10 at 23:29

A lookup table is normally a table that acts as a "master list" for something and you use it to look up a business key value (like "Make") in exachange for it's identifier (like the id column) for use in some other table's foreign key column.

Basically, you come in with something to "look up" and exchange it for something else.

The location_quadmap on the otherhand is a bridge table which, as others have already said, is used when you have a many-to-many relationship between two entities. If you call that a lookup table, then I'd say any table could be called a lookup table. Those tables only contain identifiers to other tables so you'd have to first look up the id on the one table, look up the id(s) that match in the bridge table, and then look up the matching row(s) in the 3rd table? Seems to be taking the term a little too far.

Thanks Patrick, i was looking into parent-child naming convention and I always found master-lookup easier to use (for the exact reason you mention lookup is a master list). This old terminology "lookup" reminds me of days of VB and Delphi where the "lookup" table was the property of a visual component used to lookup for values that user could enter. Your description totally fits in, and those older developers might remember times when it was part of IDE. — Peter, Feb 08 '17 at 09:55

score 9 · Answer 4 · answered Feb 01 '18 at 21:05

9

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.

answered Feb 01 '18 at 21:05

Blaise

21,314
28
108
169

3

OP title is one thing ("what is lookup table?") and their actual question another ("is this thing that looks like a through/join/map table also called a lookup table by some people?"). v helpful to have answer to question as stated in title. – Zach Valenta Jun 27 '18 at 16:48
Good answer, and correct comment to it from @ZachValenta – carloswm85 Oct 25 '22 at 17:12

score 6 · Answer 5 · answered Aug 05 '10 at 23:04

6

Some people use the term Lookup Table as the table that sits in the middle of a many to many relationship.

answered Aug 05 '10 at 23:04

Justin Niessner

242,243
40
408
536

I usually hear the many-to-many table as a bridge table. And, Abe's definition for a lookup table. That's interesting. – bobs Aug 05 '10 at 23:08
Good to know. Never heard of that before. – Abe Miessler Aug 06 '10 at 04:44
That would be "misuse" the term. – ScottWelker Sep 26 '19 at 13:13

Cervo · Answer 6 · 2010-08-06T00:13:03.267

Mark Byers has the right definition for that table. Basically an intersect table. See any database textbook.

But in reality I've worked with many DBAs/Architects and most invent their own style for doing things and are not open to hearing anything else. Things like indentation rules, case for SQL statements, naming conventions for tables (even really bad ones), archival strategies, etc... You basically have no choice if they are in control of the database. You can mention it is an intersect table, point to the proper literature, but in the end if she wants to call it MyStupidlyLongAndPointlessPrefixForTablesBecauseICan_Lookup_Location_Quadmap and insists then there is nothing you can do.

So try to point it out to her, but if she doesn't go along with it, don't take it too seriously...

I just thought of something else. Lookup tables (our definition) are commonly called code tables as well. So she may call intersect tables lookup tables and lookup tables code tables. In which case you may have to learn to speak her language...

score 4 · Answer 7 · answered Oct 23 '15 at 17:53

4

Lookup table is the table that contains only ID, name and the description of some subject/object/thing. ID is primary key and auto_increment. Nothing else.

answered Oct 23 '15 at 17:53

user3059225

49
1
1

score 0 · Answer 8 · answered Jul 28 '21 at 07:13

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.

What is a lookup table?

8 Answers8

Linked