Re: Database Design Question - any suggestion?

"Rob" <robburne_at_hotmail.com> wrote in message news:39ad5c28.0412050841.2f7572a9_at_posting.google.com...
> 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.

The words in your requirements above lead me to believe there is a unique article number for each size, but the same article number for each color. Your example leads me to believe that each color has a unique article number, but multiple sizes. I'll put those together and figure that a single article number has both multiple colors and multiple sizes.

You could use a relation that contains attributes of ArticleNumber (of type character), Description (of type character), and Details (of type Relation)

Your Details nested relation would then include Size and Color as attributes

If you don't like to nest relations, then you can normalize this so that you have a table with ArticleNumber, Size, and Color as a candidate key, then another with just ArticleNumber that includes attributes independent of Size and Color, such as Description (in your example).

So, either one relation that includes a nested relation or two relations where one is keyed by the ArticleNumber and another that has one row for each color/size combination for each article number.

There are others on this list who provide table specs in SQL, so I'll leave that to them since it no longer flows from my fingertips handily. Cheers! --dawn Received on Sun Dec 05 2004 - 20:13:18 CET