Product Options

• Can have an unlimited number of options for the product.
• Can keep track of the stock, image, price and cost of each unique combination of options for the product.
• Has global options rather than ones tied exclusively to the product.

SELECT DISTINCT 
	o.option_id, 
	o.name as `option`, 
	ov.option_value_id, 
	ov.name as `option_value` 
FROM `option` o 
	LEFT JOIN `option_value` ov ON (o.option_id = ov.option_id) 
	LEFT JOIN `sku_to_option_value` s2ov ON (ov.option_value_id = s2ov.option_value_id) 
	LEFT JOIN `sku` s ON (s2ov.sku_id = s.sku_id) 
WHERE s.product_id = '1' 
ORDER BY o.option_id, ov.option_value_id;

option_id  option_name  option_value_id  option_name
---------  -----------  ---------------  -----------
1          Size         1                Large
1          Size         2                X-Large
1          Size         3                XX-Large
2          Color        4                White
2          Color        5                Blue

Xsecrets wrote:from what I can see you've simply added alot of complexity and no functionality.

Xsecrets wrote:dependent options in this scope would be you have to choose a size first then you can chose the color and at that point you will only be shown the colors that are in stock in that size it kind of goes hand in hand with the combinations.

SELECT DISTINCT 
   o.option_id, 
   o.name as `option`, 
   ov.option_value_id, 
   ov.name as `option_value` 
FROM `option` o 
   LEFT JOIN `option_value` ov ON (o.option_id = ov.option_id) 
   LEFT JOIN `sku_to_option_value` s2ov ON (ov.option_value_id = s2ov.option_value_id) 
   LEFT JOIN `sku` s ON (s2ov.sku_id = s.sku_id) 
WHERE s.product_id = '1' 
ORDER BY o.option_id, ov.option_value_id;

option_id  option_name  option_value_id  option_name
---------  -----------  ---------------  -----------
1          Size         1                Large
1          Size         2                X-Large
1          Size         3                XX-Large
2          Color        4                White
2          Color        5                Blue

SELECT DISTINCT 
	s.sku_id, 
	s.sku, 
	s.stock, 
	s.price, 
	(SELECT GROUP_CONCAT(CAST(ov.option_value_id AS CHAR(11))) 
		FROM option_value ov 
		LEFT JOIN sku_to_option_value s2ov ON (ov.option_value_id = s2ov.option_value_id) 
		LEFT JOIN sku s2 ON (s2ov.sku_id = s2.sku_id) 
	WHERE s2.sku_id = s.sku_id) AS option_value_ids 
FROM sku s 
WHERE s.stock > 0;

sku_id  sku            stock  price  option_value_ids
------  -------------  -----  -----  ----------------
1       WHITE-LARGE    99     5.99   1,4
2       WHITE-XLARGE   99     5.99   2,4
3       WHITE-XXLARGE  99     6.99   3,4
4       BLUE-LARGE     99     5.99   1,5
5       BLUE-XLARGE    99     5.99   2,5
6       BLUE-XXLARGE   99     6.99   3,5

d7a7z7e7d wrote:

Xsecrets wrote:from what I can see you've simply added alot of complexity and no functionality.

While it may appear that way because it is more complex than how options are currently done, I can think of no simpler solution that provides all of the requirements of unlimited options with stock, price, cost and image settings. With this schema you will be able to do everything that you requested, it just requires the proper queries.

Xsecrets wrote:dependent options in this scope would be you have to choose a size first then you can chose the color and at that point you will only be shown the colors that are in stock in that size it kind of goes hand in hand with the combinations.

This is rather simple and can be done with two queries. The first query would be the one I originally posted, this would be the query that populates the selection boxes with the appropriate product options and values:

Code: Select all

SELECT DISTINCT 
   o.option_id, 
   o.name as `option`, 
   ov.option_value_id, 
   ov.name as `option_value` 
FROM `option` o 
   LEFT JOIN `option_value` ov ON (o.option_id = ov.option_id) 
   LEFT JOIN `sku_to_option_value` s2ov ON (ov.option_value_id = s2ov.option_value_id) 
   LEFT JOIN `sku` s ON (s2ov.sku_id = s.sku_id) 
WHERE s.product_id = '1' 
ORDER BY o.option_id, ov.option_value_id;

Returns:

Code: Select all

option_id  option_name  option_value_id  option_name
---------  -----------  ---------------  -----------
1          Size         1                Large
1          Size         2                X-Large
1          Size         3                XX-Large
2          Color        4                White
2          Color        5                Blue

The second query would load the necessary stock, sku, price and image data:

Code: Select all

SELECT DISTINCT 
	s.sku_id, 
	s.sku, 
	s.stock, 
	s.price, 
	(SELECT GROUP_CONCAT(CAST(ov.option_value_id AS CHAR(11))) 
		FROM option_value ov 
		LEFT JOIN sku_to_option_value s2ov ON (ov.option_value_id = s2ov.option_value_id) 
		LEFT JOIN sku s2 ON (s2ov.sku_id = s2.sku_id) 
	WHERE s2.sku_id = s.sku_id) AS option_value_ids 
FROM sku s 
WHERE s.stock > 0;

Returns:

Code: Select all

sku_id  sku            stock  price  option_value_ids
------  -------------  -----  -----  ----------------
1       WHITE-LARGE    99     5.99   1,4
2       WHITE-XLARGE   99     5.99   2,4
3       WHITE-XXLARGE  99     6.99   3,4
4       BLUE-LARGE     99     5.99   1,5
5       BLUE-XLARGE    99     5.99   2,5
6       BLUE-XXLARGE   99     6.99   3,5

You now have all of the data necessary for the user interface to dynamically enable or disable the appropriate options based on stock. As you can see from the results of the 2nd query, you have the option_value_ids for each sku. So with a little bit of javascript in conjunction with the results of the first query, you should be able figure out which options need to be toggled.

Once again, I believe that with this schema you should be able to do just about anything you would want to as far as product options are concerned. Although, any other suggestions you might have to solve this problem are always welcome.

mystifier wrote:I have had another big problem with product options regarding tax. The website that I did sells school uniforms. VAT is only applied from nominal age 14 on children's clothes. The last thing they want to do is duplicate everything to show junior (no VAT) and senior (Standard Rate VAT) versions of everything.

An extra 'apply_tax' field is really needed, aside from tax rate. For items that are always subject to VAT (non-clothing items in this case), 'apply_tax' is set to true. In other cases it is set to false but can be changed to true when particular option values are set (which therefore also need an 'apply_tax' field).

It may well be that this isn't considered 'core' behaviour but it does apply to many business, not just with children's clothes but many areas, for example, tax exemption for certain disabilities.

Xsecrets wrote:You Europeans have to complicate everything don't you?

mystifier wrote:armies of accountants, solicitors, councillors, estate agents etc., all servicing one another.

mystifier wrote:I have had another big problem with product options regarding tax. The website that I did sells school uniforms. VAT is only applied from nominal age 14 on children's clothes. The last thing they want to do is duplicate everything to show junior (no VAT) and senior (Standard Rate VAT) versions of everything.

An extra 'apply_tax' field is really needed, aside from tax rate. For items that are always subject to VAT (non-clothing items in this case), 'apply_tax' is set to true. In other cases it is set to false but can be changed to true when particular option values are set (which therefore also need an 'apply_tax' field).

It may well be that this isn't considered 'core' behaviour but it does apply to many business, not just with children's clothes but many areas, for example, tax exemption for certain disabilities.

Xsecrets wrote:All you've done is create a sku table for no good reason. all the data you are currently storing in the sku table can just as easily and more simply be stored either in the option or option_value tables without the added complexity of a sku table, now if you actually put a combination field in there instead of an actual option_value_id you might be getting close to a workable solution.

Johnathan wrote:In that case, wouldn't relating stock level to just the option itself mess this up? If someone is buying product A with the option of "White XL" and someone else is buying product B with the option of "White XL", I don't want the option stock to be decremented twice. I want product A's option decremented once, and product B's option decremented once.

Xsecrets wrote:yes sure you can pull out some fictional numbers that are meaningless, by using a subquery, but once someone makes a purchase what stock are you going to decrement? are you going to reduce large or green? I suppose you could decrease both, but that is not a good solution, because someone buys a large blue shirt and you now have 98 large and 98 blue so the next person buys a large green shirt so now you have 97 large 98 blue and 98 green which is not correct. Any workable solution is going to have to have some sort of combination/hash field so that the inv etc is only stored on the combination.

SapporoGuy wrote:Actually, I think that my solution might just work

Xsecrets wrote:ok my concern is give me the query that you would use to update the stock after a successful purchase of a large white tshirt given your original data, and the fact that you do know know the sku cause at this point in the process you would not know the sku because the customer simply chose two different options values one from size and one from color.

UPDATE sku SET stock = stock - <quantity> WHERE sku_id = <sku_id>;

option_id  option_name  option_value_id  option_name
---------  -----------  ---------------  -----------
1          Size         1                Large
1          Size         2                X-Large
1          Size         3                XX-Large
2          Color        4                White
2          Color        5                Blue

<p>Size:</p>
<select name="option_1">
	<option value="0">Choose A Size</option>
	<option value="1">Large</option>
	<option value="2">X-Large</option>
	<option value="3">XX-Large</option>
</select>

<p>Color:</p>
<select name="option_2">
	<option value="0">Choose A Color</option>
	<option value="4">White</option>
	<option value="5">Blue</option>
</select>

sku_id  sku            stock  price  option_value_ids
------  -------------  -----  -----  ----------------
1       WHITE-LARGE    99     5.99   1,4
2       WHITE-XLARGE   99     5.99   2,4
3       WHITE-XXLARGE  99     6.99   3,4
4       BLUE-LARGE     99     5.99   1,5
5       BLUE-XLARGE    99     5.99   2,5
6       BLUE-XXLARGE   99     6.99   3,5

<script type="text/javascript">

var optionData = new Array(
	{
		sku_id:'1',
		sku:'WHITE-LARGE',
		stock:'99',
		price:'5.99',
		option_value_ids:new Array(1,4)
	},{
		sku_id:'2',
		sku:'WHITE-XLARGE',
		stock:'99',
		price:'5.99',
		option_value_ids:new Array(2,4)
	},{
		sku_id:'3',
		sku:'WHITE-XXLARGE',
		stock:'99',
		price:'6.99',
		option_value_ids:new Array(3,4)
	},{
		sku_id:'4',
		sku:'BLACK-LARGE',
		stock:'99',
		price:'5.99',
		option_value_ids:new Array(1,5)
	},{
		sku_id:'5',
		sku:'BLACK-XLARGE',
		stock:'99',
		price:'5.99',
		option_value_ids:new Array(2,5)
	},{
		sku_id:'6',
		sku:'BLACK-XXLARGE',
		stock:'99',
		price:'6.99',
		option_value_ids:new Array(3,5)
	}
);

</script>