Technical Support Forums

Free, outstanding support from WebAssist and your colleagues

rating

Product Options

Thread began 7/05/2011 2:58 pm by admin394099 | Last modified 4/16/2012 4:32 pm by Jason Byrnes | 5291 views | 30 replies |

admin394099

Product Options

I am unsure how to properly (at all) enter product options for my products using the provided MySQL Database. Firstly, I have entered the more simple product data in the Products table, for instance a doormat with a ProductID of CMC-02x03 at a price of 23.99 etc. How do I specify that this doormat is available in Red, Green, Blue etc...

as a guess, should I go to the database table Options & then enter CMC-02x03 for the OptionID, then enter Red Green Blue in the field for OptionName?

Is there a document other than the Realtionship Diagram which provides general information on how to properly structure data in this multi-part database? Maybe a few examples of typical setups? I am honestly very confused by all of the Columns & Tables.

Sign in to reply to this post

Jason ByrnesWebAssist

Well, in the eCommerce database, the ProductID should be a numeric value that is set by the database, you should not be setting the ProductID value. the productID column is the primary key column of the database, let the database set the productID value, you can set the ProductSKU column to "CMC-02x03" but that is something completely different.


When working with relational tables, you work with the primary key columns, so if your products record looks like this:

+-----------+------------+-------------+
| ProductID | ProductSKU | ProductName |
+-----------+------------+-------------+
| 1 | CMC-02x03 | Doormat |
+-----------+------------+-------------+




the value that will be used to draw the relations in the productoptions table is the productID.

first you need to define the optiongroup in the options table, for example:

+---------------+------------------+
| OptionGroupID | OptionsGroupName |
+---------------+------------------+
| 1 | size |
+---------------+------------------+
| 2 | color |
+---------------+------------------+




Now, you can define the options that belong to each group in the options table:

+----------+---------------+------------+
| OptionID | OptionGroupID | OptionName |
+----------+---------------+------------+
| 1 | 1 | small |
+----------+---------------+------------+
| 2 | 1 | medium |
+----------+---------------+------------+
| 3 | 1 | large |
+----------+---------------+------------+
| 4 | 2 | red |
+----------+---------------+------------+
| 5 | 2 | yellow |
+----------+---------------+------------+
| 6 | 2 | green |
+----------+---------------+------------+
| 7 | 2 | blue |
+----------+---------------+------------+



now, you can use the productoptions table to relate the product to the options.

using the sample data, the product ID is 1, the optiongroupid for the color option group is 2 and the option ID for the red yeelow blue colors are 4, 5, and 7

we need to following records to create a red yellow and blue option for our sample product:

+-----------------+-----------+----------+---------------+----------------------+
| ProductOptionID | ProductID | OptionID | OptionGroupID | OptionPriceIncrement |
+-----------------+-----------+----------+---------------+----------------------+
| 1 | 1 | 4 | 2 | |
+-----------------+-----------+----------+---------------+----------------------+
| 2 | 1 | 5 | 2 | |
+-----------------+-----------+----------+---------------+----------------------+
| 3 | 1 | 7 | 2 | |
+-----------------+-----------+----------+---------------+----------------------+
Sign in to reply to this post

admin394099

Firstly could you clarify, you wrote "the value that will be used to draw the relations in the productoptions table is." - it seems like a part of the sentence was cut off...

So if I sell 12 mats each with 5 or more unique colors each I would define:

+---------------+------------------+
| OptionGroupID | OptionsGroupName |
+---------------+------------------+
| 1 | Pro DoorMat-01 size |
+---------------+------------------+
| 2 | Pro Doormat 01 color |
+---------------+------------------+
| 3 | Cheap DoorMat-02 size |
+---------------+------------------+
| 4 | Cheap DoorMat-02 color |
+---------------+------------------+
etc....

Is that correct?

Sign in to reply to this post

Jason ByrnesWebAssist

sorry, that sentence should have been:

The value that will be used to draw the relations in the productoptions table is the productID.





no, the options and options groups only need tyo be defined once. you do not need to create an option group for each product, they will also use the same option group and options defined in those tables.

the options groups table defines the option groups that can be used by any product.

the options table defines the options that belong to the groups, again they can be used by any product.

the productoptions table is the glue that creates the relation between the product and the options that are available for it.


if you have the three products, lets say there are the 2 products:
pro doormat
cheap doormat:

+-----------+------------+----------------+
| ProductID | ProductSKU | ProductName |
+-----------+------------+----------------+
| 1 | CMC-02x03 | Pro Doormat |
+-----------+------------+----------------+
| 1 | CMC-01x02 | Cheep Doormat |
+-----------+------------+----------------+



the productoptions table is used to set which options each product is available in, for example, taking the sample data for the options table from my previous reply, could make the pro dormat available in:
Color: red, yellow blue
Size: small, medium and large,

and the cheep doormat in:
Color: red, green
Size: small and large:

+-----------------+-----------+----------+---------------+----------------------+
| ProductOptionID | ProductID | OptionID | OptionGroupID | OptionPriceIncrement |
+-----------------+-----------+----------+---------------+----------------------+
| 1 | 1 | 4 | 2 | |
+-----------------+-----------+----------+---------------+----------------------+
| 2 | 1 | 5 | 2 | |
+-----------------+-----------+----------+---------------+----------------------+
| 3 | 1 | 7 | 2 | |
+-----------------+-----------+----------+---------------+----------------------+
| 4 | 1 | 1 | 1 | |
+-----------------+-----------+----------+---------------+----------------------+
| 5 | 1 | 2 | 1 | |
+-----------------+-----------+----------+---------------+----------------------+
| 6 | 1 | 3 | 1 | |
+-----------------+-----------+----------+---------------+----------------------+
| 7 | 2 | 4 | 2 | |
+-----------------+-----------+----------+---------------+----------------------+
| 8 | 2 | 6 | 2 | |
+-----------------+-----------+----------+---------------+----------------------+
| 9 | 2 | 1 | 1 | |
+-----------------+-----------+----------+---------------+----------------------+
| 10 | 2 | 3 | 1 | |
+-----------------+-----------+----------+---------------+----------------------+




keep in mind:

1) productoptions.ProductID creates the relationship to the products.ProductID
2) productoptions.OptionsID creates the reelationship to the options.OptionsID
3) productoptions.OptionGroupID creates the relation to the optiongroups.OptionGroupID

Sign in to reply to this post

bill3786

I am developing a very similar system to the one described above but cant get the optiongroupID to display the correct value as shown in your last table. I am trying to achieve this at step 4 of the MRT but with no success.

The optionGroupID column will only display 1 for all rows irrespective of the value of the optionID.

This problem is also described in an older posting here from 2009 showthread.php?t=7368

Can anybody help?

Sign in to reply to this post

Jason ByrnesWebAssist

send a copy of your page please and screen shots showing how you have configured the Manage Relational Table behavior.

Sign in to reply to this post

bill3786

Attached copy of page and MRT screenshots. Shot 4 doesn't show a binding for the optionGroupID as I tried a number of combinations without success and then left it blank.

A couple of other problems which may or may not be related

1. I have a couple of notices of undefined variables on line 139 and 160
2. I have a slideshow in the page header which displays correctly in all my other pages except for the dataassist generated pages where the slideshow is missing.
3. The main navigation in the header displays correctly in all browsers for all pages (ordinary and dataassist) except for IE9 where the dataassist generated pages show the menu as a simple horizontal list with no spaces.

I think I'm nearly there but need to solve the issues above.

Thanks

Attached Files
products_Insert.zip
Sign in to reply to this post

bill3786

This is an extra screenshot (limit of uploads reached on previous post) showing the option checkboxes. Is there a way to increase the size of the checkboxes to make it easier to enter data, the current size is easily missed with the mouse pointer and could lead to data entry errors?

Sign in to reply to this post

Jason ByrnesWebAssist

I have created a support ticket so we can look into this issue further.

To view and edit your support ticket, please log into your support history:
supporthistory.php

If anyone else is experiencing this same issue, please append to this thread.

Sign in to reply to this post

bill3786

Ok, thanks Jason.

In the meantime can you say what value was assigned to optioGroupID in step 4 of the MRT to produce the records in your productoptions table in your example.

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