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.

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

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?

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

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?

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

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

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?

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.

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.