Re: Database Design Question - any suggestion?
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
CHECK (sku SIMILAR TO '[0-9][0-9][0-9]-(grn|blu|wht)-(s|m|l)'),
(sku CHAR(9) NOT NULL PRIMARY KEY
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