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?

Solution 1

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.

Solution 2

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.

Solution 3

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.

Solution 4

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.

Solution 5

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

View more solutions

Comments

[bobs]

I usually hear the many-to-many table as a bridge table. And, Abe's definition for a lookup table. That's interesting.

[Justin Niessner]

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.

[Toby Allen]

Well said. There are more important things to dig your heels in about.

[Abe Miessler]

Couldn't agree more with your "pick your battles" comment.

[Abe Miessler]

Good to know. Never heard of that before.

[siliconrockstar]

I've also heard this called a 'pass through table'.

[Peter]

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.

[Zach Valenta]

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.

[steviesama]

Yep...the first example he gave was a lookup...what he actually has is a junction/join.

[ScottWelker]

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]

That would be "misuse" the term.