Jump to content


Photo

Setting up a database


  • Please log in to reply
4 replies to this topic

#1 play_

play_
  • Staff Alumni
  • Advanced Member
  • 717 posts

Posted 25 March 2006 - 05:00 AM

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 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:

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)
regex.kat.sh --- regex library
u.kat.sh ---- url shortener
tabbit.org ---- tabbed pastebin

#2 Hooker

Hooker
  • Members
  • PipPipPip
  • Advanced Member
  • 193 posts
  • LocationWales, UK

Posted 25 March 2006 - 06:37 AM

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):
t-shirts
[!--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]

t-shirts-details
[!--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 :)

#3 play_

play_
  • Staff Alumni
  • Advanced Member
  • 717 posts

Posted 25 March 2006 - 07:02 AM

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.


regex.kat.sh --- regex library
u.kat.sh ---- url shortener
tabbit.org ---- tabbed pastebin

#4 Hooker

Hooker
  • Members
  • PipPipPip
  • Advanced Member
  • 193 posts
  • LocationWales, UK

Posted 25 March 2006 - 07:09 AM

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

#5 play_

play_
  • Staff Alumni
  • Advanced Member
  • 717 posts

Posted 25 March 2006 - 05:07 PM

Alright i get it.
Thanks. I think this will work out fine
regex.kat.sh --- regex library
u.kat.sh ---- url shortener
tabbit.org ---- tabbed pastebin




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users