Jump to content

Archived

This topic is now archived and is closed to further replies.

play_

Setting up a database

Recommended Posts

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)

Share this post


Link to post
Share on other sites
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 :)

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
Alright i get it.
Thanks. I think this will work out fine

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.