Have I normalized too much?

[Guru2B]

Hi,

I have a database which keeps track of articles of clothing. Each article of clothing may come in different 'size groups' ie Adult, Youth, Child. Further each group could be broken into small, medium, large. Yet further, small could contain a number of measurements...say waist and inseam if referring to pants. These measurements will be stored along with a descriptor somewhere ie Waist and inseam.

My question: is there an easy way to do this? After looking at this for a while it appears that I will need three tables and enough SQL to choke a small dog just to get one record.

Eagerly awaiting a response,
Guru2B

 

[TTThio]

Hi,

I'm not sure if I understand correctly, however, if I am, then, actually you can just use one table containing of :
ProductName, ProductID, Category, Size, Waist, Inseam

Then to get a record, you can query it with for exp.
"SELECT ProductID FROM TableCloth WHERE ([Category] = "Adult&quot AND ([Size] = "Small&quot AND ([Waist] = 32) AND ([Inseam] = X))"

Is this what you mean?

TT

 

[Guru2B]

Lets see...okay..an item can have many sizes, each size can have many sub sizes, each subsize can have many measurements. So:

Item
Adult
Small
Medium
Large
Child
Small
Medium
Large

Now because this database holds all types of clothing from socks to shirts, not every article will have inseam or waist Although they will have other measurement points.

Does that clear things up a tad?

Guru2B

 

[BNPMike]

Guru

Don't think you can get round that easily. What you are doing is mixing trousers and socks. Maybe you want to put them into separate tables. Then you can have your measurements in the size table rather than lots of measurement records. If that's not good for your application then probably I still collapse the measurements but have

Id, Type, Class, Size,... waist,....shoe size,...

123 Sock Adult Medium null 20

etc

Note it's still normalised (?) mike.stephens@bnpparibas.com

 

[mymou]

Hi all

I have a very odd idea that probably breaks all the rules.

Id, Type, size
123, 1, 20

Why not have type and size define the rest?

If Type and Size are Foreign Keys to another table - we have the solution. The idea is that if you know you have shoes at size 20 - you can figure out the rest.

Reference table:
TypeID, Size, Size1, Size2, Size3
1 , 20, Medium, Adult, Null
1, 10, Large, Child, Null

??

Stew