0

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.

DBSchema

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

1

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:

LocationLookup table:

  • PK: LocationLookupID
  • UK: LocationIDSales, LocationIDShip, LocationIDSend

ItemLookup table:

  • PK: ItemLookupID
  • UK: LocationLookupID, ItemID

LevelLookup table:

  • PK: LevelLookupID
  • UK: ItemLookupID, LevelID

enter image description here Look how much simpler life becomes when you do this!

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.

  • Excellent answer, thank you Thomas! I was leaning towards having the surrogate keys as dragging the keys through the schema seems unwieldy, but it's very difficult to find anything outside of the most basic 'this is how lookup tables work'. Would you happen to know what the "official" term for this type of design pattern is? – Nick Oct 27 '19 at 19:29
  • Happy to help. I actually don't know the "official" name of either of these design patterns. I have also found it difficult to find information about this stuff. I have learned mainly through years of using most of the approaches and learning (sometimes painfully) the pros and cons of each. I know there are a couple books out there that might be on point: "Refactoring Databases: Evolutionary Database Design" by Scott W. Ambler and Pramod J. Sadalage, and "Data Model Patterns" by David C. Hay - but I don't know how well they address this particular topic. – Thomas Phaneuf Oct 27 '19 at 21:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.