CODEWITHSUNDEEP
sqldatabase-designlookup
user2343837
user2343837

Reputation: 1015

How to implement Lookup Tables SQL

I'm trying to understand how to implement a lookup table for which the values will change. For example: If I have:

EmpType Type
1       FullTime
2       PartTime
3       Casual

So based on the table above, I generate other tables. But at one point the EmpType changes:

EmpType Type
1       Contractor
2       PartTime
3       Casual

Are lookup tables supposed to have a PrimaryKey?

Are they similar to slowly changing dimensions?

Are they supposed to be related to other tables?

If anyone could point me in the right direction of how to use Lookup tables, that would be great. I've found many articles talking about not using MUCK or Generic Lookup tables, but I can't find anything on how to use them properly.

Thanks.

Upvotes: 0

Views: 4435

Answers (1)

Allan S. Hansen
Allan S. Hansen

Reputation: 4081

This is going to be a bit abstract, so I hope I explain myself properly.

The reason to use the Look up table, is that you do not want the (textual/semantic) value spread out on every related table. A lookup table is not much more than a helper to avoid saving longer descriptions/values in many tables. So basically, a space saving tool.

So in your example, if you have 5 tables which all have a link to your EmpType, instead of typing FullTime into those 5 tables, you can now put a 1, thus saving space. This means, however, as you've noticed yourself - that if the "value" of 1 changes from FullTime to Contractor, that all references (now, historical and future) will have the value Contractor.

That is the expected behavior and design point of a look up table. That, if the value changes, it is the intention that the value is allowed to change for all associations. This means you by default have no change history and cannot see that a value has changed from FullTime to Contractor

If that is not intended behavior, then you should look at another pattern.

I cannot see from your data what the wanted behaviour is; meaning why it's "bad" for you that FullTime changes to Contractor.

If it is that not all FullTimes should be Contractors, then you either need an additional Table and separate FullTime/PartTime/Casual from Contractor/Non-Contractor.

Or you can add another EmpType (4) and use that for Contractor and update the FullTime from 1 to 4 where relevant.

If it is that you lack history, you can make a change table to keep track of changes made to the EmpyType having a previous value, and a timestamp.

And so on.....

Upvotes: 3

Related Questions