Re: Database Design Question - any suggestion?

Rob wrote:

> I am designing a relational database for a client who sells clothing.
>
> He wants an article number for each item of clothing.
>
> However each item of clothing has a size (s/m/l) and a possible range
> of colours. He wants to be able to keep track of the stock for each
> size and colour but he only wants an article number for each style.
>
> For example:
>
> Article No. 001 - Mens T-Shirt (small:white)
> Mens T-Shirt (medium:white)
> Mens T-Shirt (large:white)
>
> Article No. 002 - Mens Jeans (small:blue)
> Mens Jeans (medium:blue)
> Mens T-Shirt (large:blue)
>
> Obviusly the Article No. will be the primary key, but how best to
> store data about each colour and size combination, and what is a new
> item comes into stock in a new colour?
>
> Any pointers or suggestions would be appreciated.
>
> Many thanks,
>
> Rob Burne.

I have worked on only one apparel package, but the key was:

division
style
color
label
dimension

Where division is operating division within a conglomerate, style is usually "short" "pants" etc, color is self-explanatory, label is the label like "izod" or "polo", and dimension is actually a key to a table that contains a family of size buckets.

This application did not produce a single key out of these, which made it extremely complicated to work with an maintain in many ways. On the other hand, the fact that it carried around the components on many tables made some JOINs easier.

For myself, I would make these 5 the candidate key (if you need all five) and have a different unique key, which does not really have to mean much, it could probably even be a number and be just fine. My personal experience leads me away from Mr. Celko's advice that you "cram the key" as I call it.

-- 
Kenneth Downs
<?php $sig_block="Variable scope? What's that?";?>