Re: Database Design Question - any suggestion?

From: -CELKO- <>
Date: 7 Dec 2004 06:37:00 -0800
Message-ID: <>

Bernard Peek wrote:
> In message <>,
> <> 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
> >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
> >three part vector code: <width in cm><material><diameter in
> >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
> 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
> industry sector in all of its internal databases, all of them set up
> the same contractor moving from one company to another. The key
> 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
> >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
> to number our BASIC statements 10, 20, 30 etc so that we could insert

> new statements between the existing lines. That worked until we
> to add more than 9 lines between existing statements. At that point
> 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
> are going to do that then why not just use a text field and let
> write "T-shirt, bottle grn, S"?
> --
> Bernard Peek
> London, UK. DBA, Manager, Trainer & Author. Will work for money.
Received on Tue Dec 07 2004 - 15:37:00 CET

Original text of this message