Creating Product Database for Dynamic website php/mysql

Products options vary(need advice on db structure and product.php script

[Birdman]

Hello everyone! I'm working on a e-commerce site(4th redesign) and I'm going dynamic for easier updating.

I've got the website structure built up until the point of showing individual products. My next step is building the product database.

My question is:
How should I deal with products that have different options available?
Example:

• Some come in different sizes
  
• Some different colors
  
• Some can be personalised
  

The question pertains to both the mysql db structure and the product page itself. I'm thinking I need to have each type of item in it's own table and the product page will have different functions() for each type of item, as to display the options properly.

Does this sound right? I appreciate any input.

[Birdman]

So far, I have a database that contains two tables.

• One holds content for the main sections
  
• One holds content for the sub-sections
  

I'm pretty sure that I'll need separate tables for the different types of products, since the options vary so much. I just wonder if I should create a new database or put them in the existing db.

I will go ahead and start a new one for now. If that turns out to be the wrong route, I can just export the tables back to the original db(I think?).

[lorax]

Birdman,
Your asking a lot for a theoretical question. ;) DB design is an artform and providing you an answer is next to impossible without seeing the actual scope of products and variants. I can however, use a theoretical example - and perhaps I'll be close enough for you to get the gist. For example.

I have "Widget A" and "Widget B". Both widgets come in 4 sizes and the colors depend upon which size you choose.

If there are a lot of widgets and the colors are the same then my approach would be to develop one table. In that table I would have fields for all the identifying data. I would create fields for the sizes since they tend not to change much (e.g. s, m, l, xl) and then develop a color coding system for each size field. For example: small widget A comes in black, red and blue.

In my field for 's' I'd have the color code 'blk,red,blu'. When I queried this table for colors I would use the explode function on the data from this field and create an array of the colors then simply use an output loop to display the array of colors for each widget. If there were no colors in the field then that size is not available for this widget.

The trick is to look for patterns. You have to use your judgement and weigh the consistency against the limitations of table structure you build. There will always be some limitations but the trick is to provide for scalability. To plan for these, I play out 'what if' scenerio's and incorporate those into my db designs.

HTH Gregg

[Birdman]

Thanks lorax.

>>asking a lot for a theoretical question

You're right. The whole thing is boggling my mind. I suppose I'll create a new database for products and then a new table for each specific(w/ different options) type of item.

Then I'll make a php script with a function for each type of item that will render the product info page properly(with the right options). It seems like the way to go, just wanted to double check with the pros before I do it all wrong(again);)

[lorax]

Re: new database - really not necessary. Just create a new table. New dbs mean you have to open a new connection - which translates into additional server resources being kept open.

[Birdman]

Thanks again. I was just about to start. So I'll continue in my original db. Appreciate the advice. I love this forum:)

[CoryZ]

In terms of multiple options, etc...

An options table along the lines of this one would do the job:

Key Item Option
--------- ------------ -------------------------------------
1 111111 Black
2 111111 Zippered
3 111111 Snaps
4 222222 Opaque
5 333333 Supersize
6 111111 XL Only
7 222222 Personalize

I am not a pro DB designer, but this allows you have multiple options for each product ID without adding another table all the time, or always having to redo your current ones. A simple JOIN (probably a LEFT one), using Item to link the tables, will pull the data into your results and and then you just deal with the rows. Of course, you could break things out a bit more if desired and have tables for sizes, etc, but this is how I'd go about solving the multiple options issue.

[ukgimp]

Birdman

Have a look at the following. It is an ASP/Dreamweaver UD tutorial but it may help you conceptualise the DB structure.

princeton.edu/~rcurtis/ultradev/ecommdatabase.html

With D design spend a long time getting it right. Making a mistake early on and then subsequently finding out you should have done something else as thats what your customers want is no fun.

"Data Base Design For Mere Mortals" is a good start also.

Get a big white board if you can and go over it again and again until you find that your DB is scaleable and with no redundant/duplicate data.

Get a friend to look at it and explain your thought processes to them. When they say "what about when this happens?" and you realise that that would have been a stumbling block, you will have to alter the structure. Hence the white board.

Keep talking yourself through every process until you are happy.

Then you are ready to crack open PHP mySQL

Cheers and good luck

[jamesa]

Assuming the price is the same for all sizes and colors you could do three tables like this:

Product table:
- product_ID
- product_Name
- product_Desc
- product_Price

Colors table:
- product_ID
- color

Sizes table:
- product_ID
- size

so...

Products table 
------------------------------------------------ 
1111 ¦ Specialty Widget ¦ blah blah 
1112 ¦ Standard Widget ¦ blah blah 

Colors table: 
------------------ 
1111 ¦ red 
1111 ¦ blue 
1111 ¦ green 
1112 ¦ red 
1112 ¦ yellow 

Sizes table: 
----------------- 
1111 ¦ X 
1111 ¦ XXL 
1112 ¦ S 
1112 ¦ M 
1112 ¦ L 
1112 ¦ XL 

[Birdman]

Thanks for all the suggestions, everyone. Welcome to WW CoryZ.

So, a table for each item(type) isn't the way to go?

I actually thought about using the product_table/option_table route. I would still have to figure out how the page lays out depending on the options. That's why I was leaning towards a table for each item type. Then, I could trigger a function based on the item type.

I'll go check the link from ukgimp now. Thanks!

[scratch]

or combine both ideas into a single table

your options database
1 111111 Black
2 111111 Zippered
3 111111 Snaps
4 222222 Opaque
5 333333 Supersize
6 111111 XL Only
7 222222 Personalize

your products database

1 111111 pants
2 222222 shirt
3 333333 shoes
4 111111 pants that have option like 1st pants

this is really the same as having multiple databases in that it allows multple products to use option set 111111 as well as anything else you want it to do. The hard part is making an interface that can make this work.

[Birdman]

Well, I went with a separate table for each type of product. Could have been a mistake, time will tell.

Each table has:

prod_id
hits(so I can list by popularity)
descrip
cost

If the item has options, they are fields also. I made options fields TINY INT(1), and use 1 or 0 to determine if it is available in that option.

The hard part is making an interface that can make this work.

You got that right! Actually, it's working and online now. On my product detail page, I use a php switch() function to determine what item type is being displayed and extract the proper info from the database and display radio buttons, select fields, or no form fields, depending on the item type. It seems to be working good.

Thanks for all the advice!