Setting up a database
Posted 25 March 2006 - 05:00 AM
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 each shirt comes in ONE and only one size. 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:
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_(keep doing this for every color)
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)
Posted 25 March 2006 - 06:37 AM
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):
[!--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 |
[!--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 |
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
Posted 25 March 2006 - 07:02 AM
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.
Posted 25 March 2006 - 07:09 AM
[!--quoteo--][div class=\'quotetop\']QUOTE[/div][div class=\'quotemain\'][!--quotec--]| shirt_id | detail_id | sex | color | size | quantity |[/quote]
and index detail_id
0 user(s) are reading this topic
0 members, 0 guests, 0 anonymous users