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