SQL lookup and reference table definitions

Question

I need some clarification. What is a lookup table and what is a reference table in SQL?

I was under the impression that a lookup table is a table that has static data that hardly ever changes (e.g. a table that has all 50 states and their capitals) and a reference table is one that contains primary keys and links two other tables. In the example below, Table B would be a reference table. Can someone tell me what Table B is called?

Table A
CustomerID
CustomerName
CustomerAddress

Table B
CustomerID
OrderID

Table C
OrderID
OrderDate

Answer 1

Table B is a Link Table or Junction Table.

Reference and Lookup tables can mean different things for different sources, and I am not aware of a strict definition to differentiate the two.

For me personally I normally use a reference to mean an long value stored out of the main table (like a StoreID), and lookup to mean a list of allowable values (enforced by a FK constraint) for a certain field.

Answer 2

There is no hard-and-fast definition for those terms. It really comes down to whether or not the other person knows what you are talking about.

Among the people I work with table B would be called a "Mapping Table".

Answer 3

• Fork table
• many-to-many relation table
• link table
• map table