I have a scenario where we want to build an inventory description system and I am trying to design the schema but need a little guidance to make sure i’m not veering off the track.
The basic premise is: A combination of three locations (Selling location, Shipping Location and Destination Location) along with the Item being sent, is used to determine how something should be packaged and sent. It gets a little more complex with breaking this down into levels (Level1: Put item in box. Level2: Tape box up etc…) but still manageable.
By the time we get to the Packing level, we’re dragging all the fields in to make a PK using five fields to make a unique record.
My question is, is there a reasonable limit on key field length. Does it make sense to provide a surrogate key for each group and then just just that going forwards?
So the UniqueLocationLookup would have a single field used as an identifier in the ItemLookup Table.
The Combination of UniqueLocationID and Item would then become another unique key used by LevelLookup and so on…
The stacked keys approach (my name for it), where you carry the combination of keys through your tables, tends to pile up very quickly and become unwieldy. I think you are beginning to see this with the example in your question.
So, if you don’t want to have to drag every key around for all your relations, then then the answer to the surrogate key question is “yes“.
There is a caveat, however. You want to make sure that within each of your lookup tables that you enforce uniqueness on the combinations of lookup table items (in SQL Server, use a UNIQUE KEY). For instance, from the example diagram below:
- PK: LocationLookupID
- UK: LocationIDSales, LocationIDShip, LocationIDSend
- PK: ItemLookupID
- UK: LocationLookupID, ItemID
- PK: LevelLookupID
- UK: ItemLookupID, LevelID
Note: When using surrogate keys, I think it is always a good idea for every table to also have at least one unique key constraint.