What is a lookup table?

问题:

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 look up table to be. I have always considered a look up 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 maping 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?

回答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.

回答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.

回答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.

回答4:

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

回答5:

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...

回答6:

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.

回答7:

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.