Re: Database Design Question - any suggestion?

From: --CELKO-- <jcelko212_at_earthlink.net>
Date: 5 Dec 2004 17:04:28 -0800
Message-ID: <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. Received on Mon Dec 06 2004 - 02:04:28 CET

Original text of this message