Re: Database Design Question - any suggestion?

In message <18c7b3c2.0412051704.7e144ecb_at_posting.google.com>, --CELKO-- <jcelko212_at_earthlink.net> writes
>>> 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. <<
>
>Design a vector code. Ever see the ISO tire (tyre) sizes? They are a
>three part vector code: <width in cm><material><diameter in inches>,
>so you can have a 155SR15 tire that we know is a steel belted radial
>tire mesuring 155 centimeters wide on a 15" rim.

That's nothing more than a three element compound key. Compound keys can be bad news if the business rules change or if you haven't understood them properly. I've had to change the primary key used across an entire industry sector in all of its internal databases, all of them set up by the same contractor moving from one company to another. The key worked inside each company but failed when they tried to use it for sharing data. I wouldn't risk that again.

>
>I'd create a hierarchical item component, probably 3 or 4 digits will
>be enough. Ever seen Dewey Decimal Classification in a library?
>
>100's = shirts
> ...
>200's = pants
>210's = jeans
>211 = low rise jean

The Dewey system failed when they ran out of numbers and that's why a lot of libraries have abandoned it. Back in the old days we were taught to number our BASIC statements 10, 20, 30 etc so that we could insert new statements between the existing lines. That worked until we needed to add more than 9 lines between existing statements. At that point we needed to make wholesale changes to our code.

If you are going to try to embed codes like "grn" into your keys then plan someday to make codes for "bottle green" and "sage green." If you are going to do that then why not just use a text field and let someone write "T-shirt, bottle grn, S"?

-- 
Bernard Peek
London, UK. DBA, Manager, Trainer & Author. Will work for money.