Re: Database Design Question - any suggestion?

"--CELKO--" <jcelko212_at_earthlink.net> wrote in message news:18c7b3c2.0412051704.7e144ecb_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. <<
>
> 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.
>
> 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
>
> Then you need a size code, but you showed jeans as being S/M/L instead
> of waist/inseam. That makes no sense to me, but that's what you said.
>
> Then you need a color code. Land color numbers or gif and jpg codes
> are very precise. A 2-3 letter alphabetic abbreviation might do as
> well for this application. So ou might have something like:
>
> '211-grn-s' = a small green pair of row rise jeans.
>
> Then you need a validation check; does your SQL have a version of
> grep() or SIMILAR TO predicsates?
>
> CREATE TABLE Inventory
> (sku CHAR(9) NOT NULL PRIMARY KEY
> CHECK (sku SIMILAR TO '[0-9][0-9][0-9]-(grn|blu|wht)-(s|m|l)'),
> ..);
>
> And as you have already noticed, your client is insane not to allow
> all of the attributes in the database in proper columns.
>
> >> .. what is a new item comes into stock in a new colour? <<
>
> You need to change the validation constraint on the Inventory table to
> allow for a new color code.

I believe there are existing standard industry codes for clothing. I know, f or example, that the year and season is coded into the numbering system somehow. If not an insustry standard, then perhaps manufacturer specific, which open another can of worms. Received on Mon Dec 06 2004 - 05:08:31 CET