0

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.

Improve this question

1 Answer 1

Reset to default
3

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

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

For my data it is bad for the description to change for the same EmplType. In other words, I need to keep historical data, I need to know that at one point1 was FullTime and at a different time it was Contractor. So do you think this table should be a Slowly Chanding Diminetion instead? I should just add a PK and a DateStart and DateEnd flag to it?
I'd say it depends. If you're only interested in history, that is that it has changed from FullTime to Contractor, I'd simply make a history table with a timestamp and a previous value (EmpType, Timestamp, Type). If you need some of the associated tables to have different values (some FullTime, some Contractor), I'd properly make an additional EmpType (4, Contractor), or if all FullTime are not Contractors - you're properly looking at a second table. But it depends on usage, data model and requirements etc of your specific situation.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.