Designing a database problem

[drhowarddrfine]

Someone normally does this stuff for me but I want to do this one. I'm having trouble visualizing how a table should be laid out where you would have 6 categories of shirts. Each shirt may have different sizes and different names for those sizes. For example, Cat1 may have most shirts small, med, large but some have xlarge. Cat2 shirts have the same sizes but are called regular, macho but some also have supermacho.

Prices are different for each size and the prices in each size may not be the same in each category. iow, A small shirt in Cat1 may not be the same price as another small Cat1 or Cat2 shirt.

I have a feeling I'm trying to simplify the tables more than possible so I don't have repeating columns or repeating data in most of the columns. Like this:
shirt_id | size1 | size2 | size3
------------------------------------------
43 | small | medium | large
63 | macho | super | supermacho
67 | small | medium | large

Can someone get me started?

[coolsti]

Try to formalize in words what you have before you. Then look at those words, and the tables can be formed from the nouns, and the columns can be formed from the adjectives.

You have shirts. What describing features or "adjectives " of shirts are common and can be placed in a main table? Let us say it is the name of the shirt style, let us call it shirtstyle. Let us say each shirt style can different sizes, and each shirt style can have different colors. Each shirt style has its own set of colors, and own set of sizes, meaning that style "style1" can be in S, M, L while style "style2" can be in M,L and XL. And "style1" can be in red or white while style "style2" can be in green or pink.

With this, you make 3 tables.

The main "shirts" table with primary key "idshirt" as an integer index, and "shirtstyle " as perhaps a varchar.

Then you make another table "shirt_has_size s" for the possible sizes, with "idshirt" as foreign key to the "shirts" table, and "size" as, for example, varchar.

Then you make a third table "shirt_has_colo rs" for the possible colors, with "idshirt" as foreign key to the "shirts" table, and "color" as, for example, varchar.

Some entries:

Table shirts:
1, "cowboy buttoned"
2, "evening dress"

Table shirt_has_sizes
1, "L"
1, "M"
2, "S"
2, "L"
2, "XL"

Table shirt_has_color s
1, "red"
1, "blue"
2, "green"
2, "blue"
2, "white"

In the above, shirt style 1 comes in two sizes L, M while shirt style 2 comes in 3 sizes S, L, XL.

Now where to put the price of the shirts? Well, if the shirt price does not depend on the size or color of the shirt, then you put the price in the main table "shirts". If, however, the price is different only depending on size, then you can put it in the shirt_has_sizes table. If it depends on both size and color, then things are more complicated, but I cannot imagine this would be the case.

The above table structure looks "wasteful", as there becomes so many rows in the "has" tables. However, this is the normalized structure that enables queries to be made to search using various combinations of size, color and other attributes as selection filters and ordering criteria.

[drhowarddrfine]

Thank you. I have to re-read what you wrote but I think you verify what I was thinking I had to do. Since I never do this, I just didn't have the mindset, or confidence, to do it properly. I may have a few questions after I read your post again.

In fact, I can add now that my biggest concern was the "wastefulne ss" you mention above. That is why I thought I was, perhaps, doing it wrong.

[Atli]

What Coolist suggested would work fine, but there is still one thing I would consider a flaw.

What he did was create one base table and two 1:N (one-to-many) related tables, where the N tables would contain a single row for each option given to each entry in the base table.

Given that a great majority of your shirts will share sizes with other shirts, the two N tables will most likely be filled with duplicate entries.
That is to say, if you had 10 shirts all with N, M and L sizes, the size table would have 30 rows, each containing the size name. Of these 30 rows, 27 would contain duplicate text data.

I wold suggest using a N:M (many-to-many) relationship.
That is to say:

Shirt
-----
ShirtID INT PK
ShirtName Char(100)
-----

Size
-----
SizeID INT PK
SizeName Char(100)
-----

ShirtSize
-----
ShirtID INT PK FK (Shirt.ShirtID)
SizeID INT PK FK (Size.SizeID)
-----

Where each size would have it's own entry in the "Size" table, and could then be connected to the shirts via the "ShirtSize" table.
This way, you would only ever have to store the name of each Size once and then it could be reused for any other shirts that share that size.

Having the intermediary table containing only the PK's of each the other two tables should also dramatically increase the speed of your search queries, as the table contains only integers, rather than text.

[coolsti]

I agree this would be a good way to do it (Atli's post).

I was not thinking of the fact that the size might be given a varchar field as name. Yes, this would be wasteful the way I did it. I was thinking that the size and color could be described in the database with enumeration fields. Or, I would have used an integer and then made another table to give the relationship of idsize and idcolor to respectively size name and color name. And I think this becomes then Atli's structure.