Home
Schema - Do you use separate lookup tables rather than one large lookup table for your lookup data?
  v2.0 Posted at 8/11/2019 7:42 AM by Tiago Araujo

​Advantage: Simplifies ORM Mapping​​​

We prefer multiple lookup tables so they make more sense in ORM tools. E.g. you could have either:

1. OrderType

Or

2. LookupTable

But when you are obtaining the OrderType for an order, you would have

Either

Order.OrderType.OrderTypeID (Good)

Or

Order.LookupTable.Value (Not great as it is not clear what the nature of the lookup table is). If you have multiple lookups to the one table, you would need to do your mappings manually rather than using a tool.

Advantage: Maintains Complete Referential Integrity without the need for triggers Advantage: Maintains Complete Referential Integrity without the need for triggers

The other advantage of having separate lookup tables rather than one large one is that referential integrity is maintained.

One issue with having one large table is that you can still enter invalid values in the Order.OrderTypeID column. E.g. if Order TypeIDs range from 1-3 and CustomerTypeIDs range from 4 to 10.

If I put OrderTypeID = 10, then I will not get referential integrity errors (even though I should) because I have entered a value which exists in the lookup table (even though it is for the wrong type).

If I want to enforce referential integrity so I can only enter the correct type for my lookup table, then I would need to resort to triggers or a (fallible) coded data tier.

Advantage: You can add new columns specific to each lookup table

For example, if a Lookup table (e.g. CustomerType) has an associated value (e.g. the field MaximumDebtAmount), we don't need to add a field that is irrelevant to all the other lookup tables. We can just add it to the individual lookup table.

Disadvantage: Multiple tables make maintenance slightly more difficult, especially when making changes directly via Management Studio.

It is simpler to Administer one table than multiple tables, but you can reduce this problem with a good Generic Administration Page UI.​

Related rules

    Do you feel this rule needs an update?

    If you want to be notified when this rule is updated, please enter your email address:

    Comments: