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
