play_ Posted March 25, 2006 Share Posted March 25, 2006 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_shirtsLet'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_idnamecategorycolorsmall_quantitymedium_quantitylarge_quantityxtralarge_quantityLet me explain:shirtid :: self explanatoryname :: the name for the shirt (ex: donkey kong, power up, old skool, etc)category :: games, geek, humor.color :: white, black, pink, greensmall_quantity :: number of t-shirts sized "S" i orderedmedium_quantity :: number of t-shirts sized "M" i orderedlarge_quantity :: number of t-shirts sized "L" i orderedxtralarge_quantity :: number of t-shirts sized "XL" i orderedRemember, 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_bluesmall_quantity_white,small_quantity_pink ....small_quantity_(keep doing this for every color)andmedium_quantity_bluemedium_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) Quote Link to comment Share on other sites More sharing options...
Hooker Posted March 25, 2006 Share Posted March 25, 2006 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 explanatoryname = the name for the shirt (ex: donkey kong, power up, old skool, etc)category = games, geek, humortimestamp = cant go wrong with timestamping when the shirt was addedit'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 | quantityshirt_id = same shirt_id as the first table (so you can tell which shirt type it is talking about)sex = Male/Female shirtcolor = self explanatorysize = self explanatoryquantity = self explanatorybasicaly 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 :) Quote Link to comment Share on other sites More sharing options...
play_ Posted March 25, 2006 Author Share Posted March 25, 2006 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 = 3then 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. Quote Link to comment Share on other sites More sharing options...
Hooker Posted March 25, 2006 Share Posted March 25, 2006 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 Quote Link to comment Share on other sites More sharing options...
play_ Posted March 25, 2006 Author Share Posted March 25, 2006 Alright i get it.Thanks. I think this will work out fine Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.