close ad
WARNING PC USERS: Do Not Install the DREAMWEAVER CC 2017 Update »
open ad
View Menu

Technical Support Forums

Free, outstanding support from WebAssist and your colleagues

rating

Product Options

Thread began 6/24/2009 12:18 pm by Nigel | Last modified 6/28/2009 12:35 am by Nigel | 3328 views | 12 replies |

Nigel

Product Options

I got my products tables and product options working. The logic was each product would have a SKU and then options (color and sizes) would be added from a drop down, but those would be on the same SKU as the product. Now we have to have separate SKUs for each product option, so a blue chair will have a different SKU to a red chair, they will also have different prices.

My question is, what's the best way to handle this? Treat each product variation as a separate product? Or is there a way to be able to enter each product once and still use options drop down which change the SKU and price accordingly? That's seems like it will be very complicated. On the other hand, entering 9 products for a single chair that comes in 3 sizes and 3 colors is a lot of work too!

One other question: What's the best way to handle accessories? Certain products come with things like head rests. They only apply to specific products, but they have their own SKU and price. Should these just be handled like a product, e.g. entered through the admin as a separate product? And is there an easy way to tie them to their primary product?

Thanks.

Sign in to reply to this post

Ray BorduinWebAssist

If you have only one set of options per product... like size or color, and not two or more... then you can store that information with the options in a single options table.

Have an options table that has the productID as well as the optionName, optionSKU, and optionPrice.

If products have more than one option per then you need an intermediate table. If you are doing inventory control, then you need a separate product for each unique option combination.

Sign in to reply to this post

Nigel

Thanks for the info Ray. It will be more than one option per product, there will be sizes and colors for most products. Price will vary with size. There will be no inventory control. Given these parameters, can you elaborate on your answer? I'm not sure what you mean by an "intermediate table". Right now I have products, color, size, tables, but price and SKU is stored in the products table, that's not going to work.

Thanks.

Sign in to reply to this post

Nigel

Ray,

I'm trying to get a handle on this. If product typically have two options (color and size) are we talking one options table (with both color and size in there) with another lookup table (the intermediate table you mentioned)? Or two options tables, one for each type of option, one for color and one for size? I'm thinking the latter, so on the product page I could filter the options by product ID to display only the applicable colors and sizes in two select lists. So DB would look like below, right?

PRODUCTS
productID, productName, productDescription....
E.g. Chair, A durable shower chair.

OPTIONCOLOR
optionID, productID, optionName, optionSKU
E.g. Red, SKU0301

OPTIONSIZE
optionID, productID, optionName, optionSKU, optionPrice
E.g. Small, SKU0032, 725.00

Thanks.

Sign in to reply to this post

Ray BorduinWebAssist

Just a few more questions and I might be able to help.

Do all products have both Colors and Sizes? Would you ever have something with just a color or just a size or neither? No other options besides size and color?

Sign in to reply to this post

Nigel

Ray, here's the additional info I've gathered.

1. Size and color are only options outside of available accessories for
each product.

2. Not all products have color and size options. Currently we have just have an entry in the options table "One Size" or "No Color Options" for products without the options available.

3. Price is determined by size (where the product has sizes available).

4. SKU is unique to particular color and size options. So a chair model X in red has different SKU to the same chair model X in blue.

I'm also wondering whether to have a separate table for accessories or to use the products table. When the visitor pulls up a particular product there will often be accessories that are needed to use the product. Should I create an accessories table similar to the products table, then have a lookup table that relates a product to one or more accessories? Or perhaps I could put the accessories in the products table and have an additional field like "accessory" so I can filter accessories and products.

Your advice much appreciated!

Sign in to reply to this post

Ray BorduinWebAssist

Based on your current specifications, I would probably go with something like:

PRODUCTS
productID, productName, productDescription, productPrice...

PRODUCTOPTIONS
prodOptID, ProductID, SizeID, ColorID, SKU

OPTIONCOLOR
ColorID, ColorName

OPTIONSIZE
SizeID, sizeName, addPrice

Where addPrice is the additional price for that size option, which would be added to the productPrice for the total.

Sign in to reply to this post

Nigel

Many thanks Ray! That's not far off what I have now. I'm wondering though whether AddPrice should go in the PRODUCTOPTIONS table, as it will depend on the product and the size. Many products will have small, med, large sizes but their price will be different depending on the item.

How would you handle accessories? I'm thinking I could add them to the PRODUCTS table but add an accessory field (1/0) to separate standalone products and accessories. Or would you put them in their own table? It would be very similar to the PRODUCTS table which is why I'm wondering if they should just go in there. However we don't want them showing up as products, only on their "parent" product page under the Accessories tab. E.g.:

item-detail.php?ItemID=4

Thanks again - great info.

Sign in to reply to this post

Ray BorduinWebAssist

That makes sense... I guess you should add it to the ProductOptions table... another solution would be to just have multiple Small, Medium, and Large entries so that each could have its own unique addPrice... really either way would work.

There are often several database designs that will work depending on the front and back end you use to access it. That is one of the most difficult challenges we have at webassist is that a lot of the complexity is in the database design and writing the proper queries... and we haven't figured out a way to help much with that since there are so many possible applications and scenarios that exist.

I would probably add them to the products table and add an accessoryParent field which is the ID of the product for this accessory rather than just a (0/1) field. That way I can filter out products that have a value in this field for the catelog and filter using the productID to get the related accessories using the same field.

Sign in to reply to this post

Stan

The productoptions table is really just inventory. One row for each combination. I would probably not even use addprice, just make it productoptionprice, no math involved.

Can a single accessory be used with multiple products? Is a different accessory necessary for different product options? For example a red leg rest for a red wheel chair or whatever.

I think Ray is right in that accessories are just products and belong in the product table they just have a different producttype=accessory. You could go back to the productoptions table if you need to associate one accessory with several products.

Sign in to reply to this post
loading

Build websites with a little help from your friends

Your friends over here at WebAssist! These Dreamweaver extensions will assist you in building unlimited, custom websites.

Build websites from already-built web applications

These out-of-the-box solutions provide you proven, tested applications that can be up and running now.  Build a store, a gallery, or a web-based email solution.

Want your website pre-built and hosted?

Close Windowclose

Rate your experience or provide feedback on this page

Account or customer service questions?
Please user our contact form.

Need technical support?
Please visit support to ask a question

Content

rating

Layout

rating

Ease of use

rating

security code refresh image

We do not respond to comments submitted from this page directly, but we do read and analyze any feedback and will use it to help make your experience better in the future.

Close Windowclose

We were unable to retrieve the attached file

Close Windowclose

Attach and remove files

add attachmentAdd attachment
Close Windowclose

Enter the URL you would like to link to in your post

Close Windowclose

This is how you use right click RTF editing

Enable right click RTF editing option allows you to add html markup into your tutorial such as images, bulleted lists, files and more...

-- click to close --

Uploading file...