OpenCart Community

Posted: **Fri Nov 19, 2010 2:59 pm**

Since there is somewhat of a debate about how product options should and could be handled in 1.5 and beyond, I would like to offer my suggestions. The goal is to create a schema that:

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.

Here is what I have come up with:

You would query for a product's options using something like:

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;

Which would return:

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

Thoughts?

Posted: **Fri Nov 19, 2010 3:28 pm**

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

The two things being discussed are option combinations and dependent options.

option combinations would be for say a clothing store like your example.
you have a size option and a color option, but you don't really have 99 large shirts and 99 blue shirts you have 50 large-blue shirts and 75 medium green shirts.

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. There is a deeper level to the dependent options, but I think we'll just leave that for later. It would be what many carts call configurable products you have a computer product and your first option is CPU AMD/Intel if you choose AMD then you can only choose AMD compatible motherboards in the next option.

Posted: **Fri Nov 19, 2010 4:51 pm**

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.

Posted: **Fri Nov 19, 2010 5:06 pm**

I am not sure if Product, Options, Values can be left as they are (globalised in 1.5.0) with a new field in [Product] being used to code all options.

For example, a product has four options, each with four values.
`product_options` = 02000103 (Option1-Value2, Option2-Default, Option3-Value1, Option4-Value3)

To decrement stock:
Update Product set quantity = quantity -1 where product_options = '02000103';

To check stock:
Select * from Product where product_options = '02000103';

Posted: **Fri Nov 19, 2010 5:23 pm**

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.

Posted: **Fri Nov 19, 2010 9:48 pm**

Isn't the idea of global options so that you can reuse them on a number of products? 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.

I don't know if that's too much functionality to ask for, but it seems necessary to me if you want to keep track of stock levels.

Posted: **Fri Nov 19, 2010 9:57 pm**

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.

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.

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.

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.

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.

Posted: **Fri Nov 19, 2010 9:58 pm**

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.

You Europeans have to complicate everything don't you?

Posted: **Fri Nov 19, 2010 10:14 pm**

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

Since we don't produce or manufacture anything any more, it is essential to make the most of our service industries by making sure that everything is complicated enough to warrant the use of specialists.

Unemployment is kept low by having armies of accountants, solicitors, councillors, estate agents etc., all servicing one another. What could go wrong?!

Posted: **Fri Nov 19, 2010 10:25 pm**

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

..... Qphoria pulls mind out of the gutter .....

...just....can't.... do.... it....

I've seen a movie like that once.... when no one else was around..... ziiiiip...

Posted: **Fri Nov 19, 2010 10:54 pm**

Posted: **Fri Nov 19, 2010 11:17 pm**

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.

Just want to say, it's true - I was looking for this too (though we abandoned the idea) - eg

Option A = Taxable product (CD-ROM)
Option B = Non-taxable product (booklet)

- but X, Q, mystifier - you're so right......why is it so complicated??

Posted: **Sat Nov 20, 2010 2:26 am**

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.

On the contrary, it is the sku table that actually provides the solution to the problem. It is the glue that joins the options together, making them an actual variation of a product that can be purchased. With it, you now know exactly how many Large White T-Shirts you have in stock.

How do you intend on tracking the stock for a combination of options without it? And you definitely don't want to store the combination field anywhere as this would be bad practice and make querying a nightmare.

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.

This is exactly what the sku table provides. It relates the options and forms one "subproduct" from them so that stock can be correctly tracked and decremented for that specific variation of the product.

In the example of using clothing, it makes absolutely no sense to track stock on a per-option (i.e Color or Size) level. Not just because this would effectively make the options non-global, but also because the product only makes sense when both options are applied. I carry a Large, White T-Shirt and I need to track the stock of that specifically. It is useless for me to track how many Large Shirts and White Shirts I have independantly, although with the sku table I can still do that if I wanted to.

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.

The numbers are neither fictional nor meaningless. In fact I'd say they are the exact opposite and none of this can come together without them.

However, you have raised some valid concerns regarding customizable computer systems and I believe I now understand what it is you are trying to get across.

In my schemas current form, it only works well for configurable products. The reason is due mainly to the way stock is tracked. For a configurable product such as a T-Shirt, you want to track stock on the sku level. However, for a customizable or "made-to-order" computer system it would make sense to track the stock on a per-option basis rather than on the sku level. Afterall, we don't want to have to create a bunch of SKUs for every possible combination of computers we can make, right?

So how do we solve this? Maybe we need to specify what type of product we are adding, configurable or customizable? Perhaps there is a reason why Magento has different types of products such as Configurable and Bundled?

Configurable products are where customers select the variant that they want by choosing options, as in my T-Shirt example. The actual variant chosen gets its stock decremented. Bundled products, also known as "kit" products in other software, are ideal for circumstances where the user may need to select a number of options and upon purchase, each one of those options get its stock decremented. This would include products like Customizable Computer Systems or Customizable Tuxedos/Suits.

Since both have different requirements for how the options are tracked and handled, it might sense for us to implement some sort of product_type or option_type definition. Thoughts?

Posted: **Sat Nov 20, 2010 3:19 am**

A large, blue, T-shirt is a specific (unseparable) product. Though selected using independent colour and size options, it would be nice to be able to calculate by some means, how many more large, blue, T-shirts were left in stock.

A 3Gb RAM, 1Tb disk, 17in. screen, computer is kitted to order. I would only want to know if I had enough individual components to fulfil an order but I would have no interest in combinations because options are mutually exclusive.

Posted: **Sat Nov 20, 2010 3:34 am**

Awesome! ideas going on and I think I'm going to love how this will turn out.

An interesting idea came to mind,
But, before I provide a possible solution, let's think about what is going on here and possibly why this hard to come up with a simple solution.

Here's something that is a bit whacky but ...

Why are you all trying to do something with making everything global and universal to make it useful but yet make it so difficult because by making it universal and useful you have to make it difficult. ....

Basically, why are you trying to pound a square peg into a round hole.
In other words, why are you taking a white shirt and trying to make it into a XL, green, long sleeved, VAT included item?

Probably, just because the top product you are using for your options is a white shirt, right?
- This might work if you were selling cars because they start off with no options, no interior and only primer.

How does that work with a T-shirt?
It could if they were all long sleeved, XXXL and white which you cut chop off the arms, have them tailored and then dyed. A bit of job ...
Why not just get some green material and have the made in the style and quantity you want?
Or, how about taking another approach and produce a XL green with long sleeves?

commercial break for a movie:
Anyone? Anyone? Anyone? ......

So, let's use a little voodo economics, actually I'm thinking Ford.
Cars are made from parts, paint and labor using an assembly line. ( <--- if you get it, pat yourself on your back, if not don't worry and just keep reading

)

However, what most people forget is that you start with a blue print when building something.
The blueprint then dictates which parts and what not you use.
Even though the Audio R8 is basically hand built it still uses parts that have been built somewhere else.

Shut already, and say what you want to say

Why not create a table that contain all the options you want and then build your products with those options?

A little more visual:
OPTIONS: size, color, material, tax, etc ...
PRODUCT: price, image, amount, etc ...

So basically, you have the options table which is dynamically built by the shop owner.
But don't call the options for the catalog during the sale but rather call the options when you are building the product in the admin.

The only problem with this is that you will need to populate the product table with options from the start. And this will also require cod e changes in the admin and catalog.

However, this will allow products to be dynamic since they are being built rather than modified when they are sold.
AND, you can have as many options that you can possibly dream of!

Sure you have an increase in total product count but you can still keep the column count down by keeping the options within their own tables.

Posted: **Sat Nov 20, 2010 3:34 am**

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.

Also like I said I'm not too worried about configurable products like computers we can tackle that later. Most carts actually use products for this so you have a product marked as configurable the computer then you have seperate products for ram hard drive monitor case etc. and you build some relationships from there. I don't see this as being an overly common configuration, and I think it would be a separate solution to the options anyways. I would say that if we can simply get the option combinations down and possibly even have the js stuff to make them seemingly dependent on the frontend it would be a very good thing. Of course coming up with a good simple way to handle the input will be the next challenge once you get a good db schema going.

Posted: **Sat Nov 20, 2010 3:45 am**

Actually, I think that my solution might just work

Posted: **Sat Nov 20, 2010 3:54 am**

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

well basically what you are proposing is the way Magento handles it. Each product is simply a conglomeration of attributes. Every single piece is an attribute including name price etc. It is very flexible, but very complicated as well.

Posted: **Sat Nov 20, 2010 4:31 am**

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.

You DO know the sku. That was loaded in the second query, remember? The query to update stock is simply:

Code: Select all

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

Imagine that the form elements are built from the first query so that this:

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

Becomes this:

Code: Select all

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

Note that the names of the select boxes contain the option_id of the option it is representing. The options within the select boxes contain the option_value_id of the option_value that it is representing.

Now, also imagine that a javascript array was created from the second query so that this:

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

Becomes this:

Code: Select all

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

Then onchange of the select box you trigger a function that will determine which sku is selected and even disable/enable other options based on what is in stock.

Making sense now?

Posted: **Sat Nov 20, 2010 4:35 am**

lolo, didn't know that is how Magneto or any other cart did it.

You don't need to make all the columns an attribute. Just the options essentially.
The only thing I can see being complicated is only when you put together the product in the admin.

But is the magneto way that much more complicated than what you are all trying to figure out?

OpenCart Community

Product Options

Product Options

Re: Product Options

Re: Product Options

Re: Product Options

Re: Product Options

Re: Product Options

Re: Product Options

Re: Product Options

Re: Product Options

Re: Product Options

Re: Product Options

Re: Product Options

Re: Product Options

Re: Product Options

Re: Product Options

Re: Product Options

Re: Product Options

Re: Product Options

Re: Product Options

Re: Product Options