Table Design help...

[Ariez]

Table Design help ... Please....

StyleNum
Color
Size

these 3 fields are a combined primary key.
The combinaison of the 3 form a SKU number which uniquely identifies a product unit.

Now heres the trick.
Some attributes need to be defined at color level (like price) or at style level like puchase orders or at size level like inventory and sales.

Im lost..
does anyone have any info on this kind of design?

 

[quwiltw]

"Now heres the trick."
I dont understand the trick or exactly what youre asking. Maybe a sampling of data would help us understand.

 

[Heiko]

primary keys are allowed to span multiple fields. thats not unusual.

 

[Hamburger1984]

...and if it doesnt work with this type of key why dont you just insert an extra unique key for internal use only - your customer/user doesnt have to see it and you wouldnt have these problems....

 

[Ariez]

Im thinking of this type of logic (see the diag)

The inventoryOnHand bothers me because its linear.
I used to offer fixed sizing so I had colSize1, colSize2 etc...exposing size inventory.
Whats more efficient, having 20 fixed colSize and probably use only 7 or 8 of them? Having the number of records droping cosiderably.
or a linear type like in my table InventoryOnHand?

I would like to see more example of how color and sizing attributes are handled in other systems...
If anyone could put me in the direction, i would really appreciate...