Re: Database Design Question - any suggestion?

From: -CELKO- <jcelko212_at_earthlink.net>
Date: 7 Dec 2004 06:37:00 -0800
Message-ID: <1102430220.332142.49160_at_f14g2000cwb.googlegroups.com>


Bernard Peek wrote:
> 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.
Received on Tue Dec 07 2004 - 15:37:00 CET

Original text of this message