database 什么是查找表？

What is a lookup table?

提问by Abe Miessler

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

我刚刚给我们的首席数据库人员提供了我创建的数据库的数据库图表，她在上面放了一堆注释，建议我重命名某些表，以便很明显它们是查找表（在表的开头添加“lu”姓名）。

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):

我工作的数据库人员建议我将几个表重命名为查找表，这些表只是将一个表映射到另一个表的 ID。示例（Location_QuadMap 下面）：

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?

假设她误读了图表是否安全，或者还有其他我不知道的定义吗？

回答by Mark Byers

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.

但我从未见过用于此目的的术语“查找表”。

回答by OMG Ponies

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.

如果这是其他数据库的命名约定，那么我不会碰运气。

回答by Patrick Marchand

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.

查找表通常是一个表，用作某物的“主列表”，您可以使用它来查找业务键值（如“Make”）以换取它的标识符（如 id 列）以供其他用途表的外键列。

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.

另一方面，location_quadmap 是一个桥接表，正如其他人已经说过的那样，当您在两个实体之间存在多对多关系时使用该表。如果您将其称为查找表，那么我会说任何表都可以称为查找表。这些表只包含其他表的标识符，所以你必须首先在一个表上查找 id，在桥表中查找匹配的 id，然后在桥表中查找匹配的行第三桌？似乎把这个词说得太远了。

回答by Justin Niessner

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

有些人使用术语查找表作为位于多对多关系中间的表。

回答by Cervo

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

Mark Byers 对该表有正确的定义。基本上是一个相交表。请参阅任何数据库教科书。

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.

但实际上，我曾与许多 DBA/架构师合作过，并且大多数人都发明了自己的做事风格，并且不愿意听到其他任何事情。诸如缩进规则、SQL 语句的大小写、表的命名约定（甚至非常糟糕的命名约定）、归档策略等……如果它们控制数据库，您基本上别无选择。你可以提到它是一个相交表，指向适当的文献，但最后如果她想称它为 MyStupidlyLongAndPointlessPrefixForTablesBecauseICan_Lookup_Location_Quadmap 并坚持那么你无能为力。

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

我只是想到了别的东西。查找表（我们的定义）通常也称为代码表。所以她可能会调用相交表查找表和查找表代码表。在这种情况下，您可能必须学习说她的语言...

回答by user3059225

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.

查找表是仅包含某些主题/对象/事物的 ID、名称和描述的表。ID 是主键和 auto_increment。没有其他的。

回答by Blaise

One use of lookup table is to store otherwise enumvalues.

查找表的一种用途是存储其他enum值。

Say, we have a Statusenum.

说，我们有一个Status枚举。

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

我们没有在数据库中的每条记录中保存“未开始”、“进行中”、“已完成”、“发回”……，而是只保存整数 1、2、……。

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

在编程方面，像实体框架这样的 ORM 可以轻松地将底层整数转换为 Enum 类型。

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

这种方式的缺点是无法从数据库端读取整数值。在解决这个问题时，我们添加了一个查找表，如

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.

这样我们的 DBA 就可以有一个字典来“查找”，通过加入这个查找表来显示状态文本。