Setting up a database

[play_]

Hello,
I've decided to go ahead and sell T-shirts online. Ill order blank shirts, print designs on them and sell on my site.

I was making the site and setting up a good/efficient DB boggled me.

K here's how my plan:

2 tables :: men_shirts | women_shirts

Let's say [i]each shirt comes in ONE and only one size[/i]. So for example, i make a shirt called "donkey kong", all sizes would be Yellow. Here's how i have a table set up for this:

men_shirts
------------------------------
shirt_id
name
category
color
small_quantity
medium_quantity
large_quantity
xtralarge_quantity


Let me explain:
shirtid :: self explanatory
name :: the name for the shirt (ex: donkey kong, power up, old skool, etc)
category :: games, geek, humor.
color :: white, black, pink, green
small_quantity :: number of t-shirts sized "S" i ordered
medium_quantity :: number of t-shirts sized "M" i ordered
large_quantity :: number of t-shirts sized "L" i ordered
xtralarge_quantity :: number of t-shirts sized "XL" i ordered

Remember, this is if shirt gets only 1 color.
So on the "add product" page, which will add the shirt to the database to be displayed on the site, id have this:

[a href=\"http://ficti0n.com/work/shirts/upload.php\" target=\"_blank\"]http://ficti0n.com/work/shirts/upload.php[/a]

Ok. Notice how I can select only 1 color, but different quantities for the sizes.


Now, the confusing part:
What if id like to get different colors, for different sizes?
I could add these columns to the table:

small_quantity_blue
small_quantity_white,
small_quantity_pink ....
small_quantity_(keep doing this for every color)

and

medium_quantity_blue
medium_quantity_white,
medium_quantity_pink ....
smallmedium_quantity_(keep doing this for every color)

And same thing for Large and Xtra large.

Then what if i add more sizes and colors? Thatd be more stuff on the table.
Is this the right thing to do if i plan on giving each size different colors?

Or should i make a new table or what?

Then of course, i'd hve a women_shirts table, which would be just like the men's.


Sorry this was rather long.
Thanks for reading! (if anyone actually did)

[Hooker]

You could always try something like this:

One table named t-shirts or whatever that looks like this:

shirt_id | name | category | timestamp

shirt_id = self explanatory
name = the name for the shirt (ex: donkey kong, power up, old skool, etc)
category = games, geek, humor
timestamp = cant go wrong with timestamping when the shirt was added

it'd be a sort of refference to look at another table called t-shirts-details or something then in that table just have something like:

shirt_id | sex | color | size | quantity

shirt_id = same shirt_id as the first table (so you can tell which shirt type it is talking about)
sex = Male/Female shirt
color = self explanatory
size = self explanatory
quantity = self explanatory

basicaly your tables should look something like this (these will look poor, hopefully youl understand them):
[b]t-shirts[/b]
[!--quoteo--][div class=\'quotetop\']QUOTE[/div][div class=\'quotemain\'][!--quotec--]shirt_id | name | category | timestamp |
---------+---------------+-----------+-------------|
01 | donkey kong | games | 01-04-2006 |
02 | old skool | humor | 01-04-2006 |
[/quote]

[b]t-shirts-details[/b]
[!--quoteo--][div class=\'quotetop\']QUOTE[/div][div class=\'quotemain\'][!--quotec--]shirt_id | sex | color | size | quantity |
--------+------+---------+-----+----------|
01 | Male | blue | 12 | 1000 |
01 | Male | green | 13 | 400 |
01 | Female | pink | 5 | 20 |
02 | Female | pink | 4 | 2000 |
[/quote]
basicaly the first table acts as an index for all the variations of the shirt type held in the second table, i hope that made sense :)

[play_]

Hey. Thanks.
I understand what you are saying, and it does make sense.
One thing, on "t-shirt details" table, what would the index key be? Should i give it another index? (detailed_shirt_id)

So let's say i wanna show all the colors and sizes available for "donkey kong" which has the id of lets say, 3.

select * from detailed_shirt_id where shirt_id = 3

then i could order by size and color.

that seems like it'd work, right?


So far, the sites ive made uses 1 to 2 tables at the most, and they were fairly small. so this is a first time approach to a not-so-small database.

[Hooker]

shirt_id should be fine for the index key unless you want to add a second id feild which would probably be best so the table would look like this:

[!--quoteo--][div class=\'quotetop\']QUOTE[/div][div class=\'quotemain\'][!--quotec--]| shirt_id | detail_id | sex | color | size | quantity |[/quote]

and index detail_id

[play_]

Alright i get it.
Thanks. I think this will work out fine