Ask a Jedi: Creating a list of product options
Andrew asks:
I have an application im programming and am stuck on this one section. I have system for product options. i let the user create different options like this:
Color:green,red,brown
Size:S.M,L,XL,XXLNow I'm able to read each as a list and loop through to get the options and save each to database. I want to now go ahead and create a list of all possible combinations.
s,green
m,green
l,geen
You get the idea. Now each has an ID number so I could use combo numbers like:
1,2
1,3
1,4
Have any how this could be done in coldfusion? I have tried arrays and havent got a solution yet.
There are probably multiple ways to skin this cat, but lets take a simple approach. As long as we know for sure what our options are, we can simply do a loop with a loop. Consider the following simple code:
<cfset colorList = "green,red,brown,blue,yellow,purple,white">
<cfset sizeList = "S,M,L,Xl,XXL,XXXL">
<cfset comboList = "">
<cfloop index="c" list="#colorList#">
<cfloop index="s" list="#sizeList#">
<cfset comboList = listAppend(comboList, c & " " & s)>
</cfloop>
</cfloop>
<cfdump var="#listToArray(comboList)#">
This code simply loops over the color list and the size list. For every instance of the color list we loop over the complete size list. I store the result in a list and just dump the value at the end. You can see how it looks below.
If your color and sizes are from database tables, then I could have stored the IDs values as well. You could imagine storing X,Y as the value, and on display, simply converting each primary key to their proper value.
As long as you know the combinations you support, this is probably the best solution (and since I said that, someone is going to prove me wrong!). If you have a dynamic set of combinations, then then the logic gets more complex.
Comments
Wow, that is a lot simpler. ;) Even if you don't have queries at first it may be worthwhile to make them by hand from your data. I used http://www.cflib.org/udf/querysim on the data and the join worked just fine.
# Posted By Raymond Camden
| 8/20/08 6:49 AM
Dang! Finally one I could have helped with and you guys beat me to the cartesian join comments! Gotta be quicker! :-)
I like your approach with the loops but how does this scale when combining more variations? Suppose I was buiding an eCommerce app and I needed to create all possible product variations of color, size, fabric and price. How would the loop approach work in this case or any other for that matter? I hope this is not too extreme of a question, just really curious is all.
# Posted By Hussein Grant
| 8/20/08 8:54 AM
Well using either my loops, or a query, it's going to get expensive fast as the number of groups increases. However, you could, and should, easily just cache this. Stuff like shirt sizes will NOT be changing often, so you could create your uber list on start up and cache it throughout the application.
# Posted By Raymond Camden
| 8/20/08 8:56 AM
Well I respectfully disagree with everyone here which means that I'm probably wrong. Why not just make a two column SizeColor table that has a composite primary key of SizeCode, ColorCode where the SizeCode is a foreign key to a Size table and the ColorCode is a foreign key to a Color table. This is a properly structured solution if you're following EF Codd's rules for data normalization. If you're tracking inventory (qty. on hand) by size and color it would be way easy to do a select on this new table and subquery your inventory ledger table by size and color to get a count. You'll also eliminate the need for messing around with the Cartesian join or any looping at all. You could have bound cfselects by allowing someone to pick a size and then showing the colors that are within that size in the new table via a remote cfc.
# Posted By Andy Sandefer
| 8/20/08 9:11 AM
@Andy
I'd only use a SizeColor table if the DB needs to know something else about the relationship (i.e. "some Size/Color combos are not possible," "some Size/Color combos have different prices," etc).
Otherwise I'd add SizeID and/or ColorID as foreign keys into my OrderLineItem (or whatever) table(s), as needed.
Seems like questionable DB design to have a SizeColor table that *only* exists to represent all combinations of Size and Color.
I'd only use a SizeColor table if the DB needs to know something else about the relationship (i.e. "some Size/Color combos are not possible," "some Size/Color combos have different prices," etc).
Otherwise I'd add SizeID and/or ColorID as foreign keys into my OrderLineItem (or whatever) table(s), as needed.
Seems like questionable DB design to have a SizeColor table that *only* exists to represent all combinations of Size and Color.
Thanks Raymond for the extremely quick response. Caching the results would be a prudent thing to do but what I was really trying to get at was how you would scale the actual loop code to accommodate more than two lists. Suppose I had a structure of four list groups (and this could increase) to process? I can actually envision a handy little function to accomplish this. Something like this could really cut down on data entry. Suppose you had 50 variants of a product you could select the necessary lists (colors, sizes, fabric), generate and insert the results into your products table and later update the pricing accordingly.
@Andy
I think your points are valid but it depends on whether you are dynamically creating these attributes or if they are hard and set as with your example. I've found that when developing product inventory systems, there is always that late and unpredictable requirement for additional fields on varying product types and to be honest the normalize approach don't always cut it. I guess that is why some turn to the Entity Attribute Value (EAV) model. Anyway I am going off topic a bit.
@Andy
I think your points are valid but it depends on whether you are dynamically creating these attributes or if they are hard and set as with your example. I've found that when developing product inventory systems, there is always that late and unpredictable requirement for additional fields on varying product types and to be honest the normalize approach don't always cut it. I guess that is why some turn to the Entity Attribute Value (EAV) model. Anyway I am going off topic a bit.
# Posted By Hussein Grant
| 8/20/08 10:04 AM
@Bill
That's a good point. Although I do know from being the type of guy who could benefit from losing a few pounds as well as an online shopper that larger sizes (usually 2X or greater) actually cost more. The real answer to this question has to do with business rules. If they will ALWAYS provide every option for every inventory item then the create every possible combination is the way to go. This sounds far fetched to me though.
That's a good point. Although I do know from being the type of guy who could benefit from losing a few pounds as well as an online shopper that larger sizes (usually 2X or greater) actually cost more. The real answer to this question has to do with business rules. If they will ALWAYS provide every option for every inventory item then the create every possible combination is the way to go. This sounds far fetched to me though.
# Posted By Andy Sandefer
| 8/20/08 10:05 AM
Well to draw on my past experience as an ERP developer I would have to say that if the these similar items aren't going to be separate records in the Item table and they're going to have different pricing and be tracked by basis of differing attributes then an ItemVariants table is in order here. This table will probably link out to many other types of subsidiary attribute tables - some with mandatory participation of the parent record and some not. Anyway, the next time someone opens up a can of worms this size we should try to muddle this discussion to an even greater extent by throwing in Customer Item, Cumulative Sales Order and Item Group discounts on top! LOL
At any rate, I'm glad to see that other folks are using CF to build business applications! We just signed a few new deals and one of them is for developing a highly customized warehouse management system. CF is awesome like that, you can use it to make fluffybunnies.com or a manufacturing system!
At any rate, I'm glad to see that other folks are using CF to build business applications! We just signed a few new deals and one of them is for developing a highly customized warehouse management system. CF is awesome like that, you can use it to make fluffybunnies.com or a manufacturing system!
# Posted By Andy Sandefer
| 8/20/08 10:18 AM
Andy you are absolutely correct about needing an "ItemVariants" link table. I should have been more explicit, otherwise it wouldn't work correctly. :)
# Posted By Hussein Grant
| 8/20/08 10:34 AM
With respect to handling more than two lists, I could see a few different ways to handle that dynamically. The easiest might be to do a Cartesian product on everything so that the DB does the work. (In the case mentioned above, it seems that the lists do get written to the database first.)
If your lists aren't coming from a database, you could use implicit or explicit recursion to build your results, but in those cases especially, you should watch your lists carefully ... it would only take one decent-sized list to expand your set of variations well beyond what you'd hoped to return.
For cases like those I think we've all seen where you don't have a completely populated matrix, one approach might be to break the complete matrix into matrices that are completely populated, wherever possible. You'd definitely want something like a DB table or a CF struct to hold the results.
What I mean is something like this. Say that you have the lists from above:
colorList = "green,red,brown,blue,yellow,purple,white"
sizeList = "S,M,L,Xl,XXL,XXXL"
But green is only available in L and XL, and XXXLs are available only in yellow and white. You could do three Cartesians:
1. "red,brown,blue,purple" x "S,M,L,XL,XXL"
2. "green" x "L,XL"
3. "yellow,white" x "S,M,L,XL,XXL,XXXL"
This would probably be easier to understand given a size/color chart.
If your lists aren't coming from a database, you could use implicit or explicit recursion to build your results, but in those cases especially, you should watch your lists carefully ... it would only take one decent-sized list to expand your set of variations well beyond what you'd hoped to return.
For cases like those I think we've all seen where you don't have a completely populated matrix, one approach might be to break the complete matrix into matrices that are completely populated, wherever possible. You'd definitely want something like a DB table or a CF struct to hold the results.
What I mean is something like this. Say that you have the lists from above:
colorList = "green,red,brown,blue,yellow,purple,white"
sizeList = "S,M,L,Xl,XXL,XXXL"
But green is only available in L and XL, and XXXLs are available only in yellow and white. You could do three Cartesians:
1. "red,brown,blue,purple" x "S,M,L,XL,XXL"
2. "green" x "L,XL"
3. "yellow,white" x "S,M,L,XL,XXL,XXXL"
This would probably be easier to understand given a size/color chart.
# Posted By Dave DuPlantis
| 8/20/08 3:36 PM
Should I mention that it is bad form to use a cartesian join in production. If you have 100 items in table A and 100 items in table B, you just returned 10,000 records. I've never met a DBA that that allows a cartesian join, except in testing.
I guess Ben Forta hasn't seen this post yet.
I guess Ben Forta hasn't seen this post yet.
@GF: I don't think anyone would argue that doing the cartesian join is good - but - it DOES solve the problem, does it not? Whether you do the query or the loops, its going to be slow. You could save the results though and never generate them again until you have to.
# Posted By Raymond Camden
| 8/20/08 9:18 PM
Gary, it's a good job we're only talking about 7 and 6 items. If it was indeed 10000 rows, doing it in a query is significantly faster than using ListAppend.
ListAppend : 30703ms
Query of Queries : 172ms
I'll post the code I used for this later
ListAppend : 30703ms
Query of Queries : 172ms
I'll post the code I used for this later
Thanks for the feedback on my question Dave. Some very interesting points there especially on the topic of matrices. Surely got me thinking.
# Posted By Hussein Grant
| 8/21/08 8:29 AM
Let me try to be the voice of reason here and ask why this is a debate? Have you ever seen a high end packaged solution like SAP or a mid range packaged solution like Great Plains or Navision that handled inventory like this? I haven't and I've worked with more than one of these solutions over the past 12 years or so. Some of these software companies have been evolving their respective code bases which handle integrated financial, warehouse management, manufacturing, sales and purchasing tasks since long before ColdFusion existed. Are all of these Accounting, Warehouse Mgmt. and Mfg. software vendors from various corners of the world wrong? If so then how is it that they are all wrong in very similar ways and how do they all seem to be handling this type of issue in similar fashions? If we assume that they're all wrong and that everyone on this forum is correct in their handling of this inventory/sales issue then how have these companies survived and prospered for well over 20 years?
The art of handling these types of logic issues should revolve around business decisions and rules. This is a fun question but I'm starting to feel like everyone has packed their bags and arrived in theory land and is totally ready to look at this issue like it is a math problem or an excuse to see who can write the coolest piece of code to handle this instead of seeing it for what it truly is - a business rule.
When defining variations on for sale inventory items, production materials and in some cases even service labor or production labor it is standard practice that the variations are pre-defined in the database before they're used in sales and inventory transactions. Yes that means making end users login and say this shirt type is available in green, black or red in small, medium, large or 2x - ahead of time - before the item is ready to be put in a catalog and sold. This type of thought process is also applicable to units of measure, item variants and all sorts of other common scenarios.
Why on earth would I go through this type of programming exercise when this data should be setup ahead of time and merely selected from out of a table?
And after you've all explained it to me and helped me understand it I would ask that you please forward the wisdom onto the folks who built PeopleSoft, Navision, Great Plains, SAP, J.D. Edwards and a whole lot of others out there.
There is an excellent book that's probably in 2nd or 3rd edition by now called Database Design for Mere Mortals authored by Michael J. Hernandez where he specifically solves issues like these and helps developers, data architects and business analysts learn how to resolve everything from multivalued fields of data to avoiding many to many relationships. This book actually refers to the type of table that I described in my reply yesterday as a "linking table". Somebody nearly chopped my head off for bringing it up as an idea but believe it or not these little tables that consist of two foreign keys linking to the primary keys of two other entities are quite common in many types of systems.
The art of handling these types of logic issues should revolve around business decisions and rules. This is a fun question but I'm starting to feel like everyone has packed their bags and arrived in theory land and is totally ready to look at this issue like it is a math problem or an excuse to see who can write the coolest piece of code to handle this instead of seeing it for what it truly is - a business rule.
When defining variations on for sale inventory items, production materials and in some cases even service labor or production labor it is standard practice that the variations are pre-defined in the database before they're used in sales and inventory transactions. Yes that means making end users login and say this shirt type is available in green, black or red in small, medium, large or 2x - ahead of time - before the item is ready to be put in a catalog and sold. This type of thought process is also applicable to units of measure, item variants and all sorts of other common scenarios.
Why on earth would I go through this type of programming exercise when this data should be setup ahead of time and merely selected from out of a table?
And after you've all explained it to me and helped me understand it I would ask that you please forward the wisdom onto the folks who built PeopleSoft, Navision, Great Plains, SAP, J.D. Edwards and a whole lot of others out there.
There is an excellent book that's probably in 2nd or 3rd edition by now called Database Design for Mere Mortals authored by Michael J. Hernandez where he specifically solves issues like these and helps developers, data architects and business analysts learn how to resolve everything from multivalued fields of data to avoiding many to many relationships. This book actually refers to the type of table that I described in my reply yesterday as a "linking table". Somebody nearly chopped my head off for bringing it up as an idea but believe it or not these little tables that consist of two foreign keys linking to the primary keys of two other entities are quite common in many types of systems.
# Posted By Andy Sandefer
| 8/21/08 10:39 AM
@Andy
"these little tables that consist of two foreign keys linking to the primary keys of two other entities are quite common in many types of systems."
Let's say you have tables Size, Color, and SizeColor. Are you saying that every time you insert into Size, for example, you also automatically insert x rows into SizeColor (where x is the number of rows in Color)? So, literally, SizeColor *only* exists to represent all combinations of Size and Color, without saying anything else about the combinations?
I'm not chopping heads...I'm actually curious as to why one would do this.
"these little tables that consist of two foreign keys linking to the primary keys of two other entities are quite common in many types of systems."
Let's say you have tables Size, Color, and SizeColor. Are you saying that every time you insert into Size, for example, you also automatically insert x rows into SizeColor (where x is the number of rows in Color)? So, literally, SizeColor *only* exists to represent all combinations of Size and Color, without saying anything else about the combinations?
I'm not chopping heads...I'm actually curious as to why one would do this.
@Bliss
It depends as to whether or not participation to that entity is mandatory. If they sold an adjustable (one size fits all) Indianapolis Colts ball cap then they would not have to have participation in that size entity. If they sold a Darth Vader poster they might not have to either. What's going to happen in your code when multiple sizes are available but not colors? My code would call a cfc and if size size records related to that item existed it would display a size selector on the page. If color, or any other type of variant entities for that matter, existed then it would probably display those as well. The approach that everyone has laid out is essentially rendering every possible combination whether it is warranted or not. You're programming around data instead of objects.
It depends as to whether or not participation to that entity is mandatory. If they sold an adjustable (one size fits all) Indianapolis Colts ball cap then they would not have to have participation in that size entity. If they sold a Darth Vader poster they might not have to either. What's going to happen in your code when multiple sizes are available but not colors? My code would call a cfc and if size size records related to that item existed it would display a size selector on the page. If color, or any other type of variant entities for that matter, existed then it would probably display those as well. The approach that everyone has laid out is essentially rendering every possible combination whether it is warranted or not. You're programming around data instead of objects.
# Posted By Andy Sandefer
| 8/21/08 11:10 AM
@Andy
Thanks for clarifying. I certainly agree.
When this thread began, I was only thinking about Andrew's specific issue as he described it ("...create a list of all possible combinations.") I think Duncan and Ray were too.
Thanks for clarifying. I certainly agree.
When this thread began, I was only thinking about Andrew's specific issue as he described it ("...create a list of all possible combinations.") I think Duncan and Ray were too.
@Bliss and Everyone Else
I appreciate the healthy discussion. Now I'm just waiting for Ray to put on another CF Biggest Loser competition so that I can move down from a 2X to a 1X when I buy funny shirts online. The irony is that there are many funny fat people out there and it seems like you have to be a 1X or smaller to buy a funny shirt. Now where is the justice in that! LOL
I did recently add to my collection when I found a gamer shirt that reads "Your Skill in Reading has Increased by 1pt!". At any rate, Adobe has the right idea. They know that many developers and graphic designers could stand to lose a few pounds and that must be why the 1X shirts that I get at user group meetings seem to fit me just fine! Keep up the under-sizing Adobe and I'll keep buying CF, FlexBuilder and Dreamweaver! LOL
I appreciate the healthy discussion. Now I'm just waiting for Ray to put on another CF Biggest Loser competition so that I can move down from a 2X to a 1X when I buy funny shirts online. The irony is that there are many funny fat people out there and it seems like you have to be a 1X or smaller to buy a funny shirt. Now where is the justice in that! LOL
I did recently add to my collection when I found a gamer shirt that reads "Your Skill in Reading has Increased by 1pt!". At any rate, Adobe has the right idea. They know that many developers and graphic designers could stand to lose a few pounds and that must be why the 1X shirts that I get at user group meetings seem to fit me just fine! Keep up the under-sizing Adobe and I'll keep buying CF, FlexBuilder and Dreamweaver! LOL
# Posted By Andy Sandefer
| 8/21/08 11:31 AM
the code i used to compare ListAppend against the cartesian join, and some further discussion:
http://duncan99.wordpress.com/2008/08/21/cartesian...
http://duncan99.wordpress.com/2008/08/21/cartesian...
Hey Andy, this discussion had me thinking in less traditional ways and I don’t think anything is wrong with that, even if it was only momentarily. It surely doesn’t mean I would literally take this and implement in a real system as per the earlier discussions, LOL, that would be ludicrous but it may help down the road when a problem shows its ugly head and the right solution is on this thread.
# Posted By Hussein Grant
| 8/21/08 6:24 PM
@ray
All good points Ray. My point is, there are hundreds, if not thousands, of new developers that read this blog and take what you, and others, write as gospel (and with very good reason) and that is 'good practice.'
All good points Ray. My point is, there are hundreds, if not thousands, of new developers that read this blog and take what you, and others, write as gospel (and with very good reason) and that is 'good practice.'
@gary - Well, if a person can't be bothered to peruse the comments, I'd be a bit concerned about the developer. ;) In many of my posts I recommend X and ask folks to add their thoughts. I don't think this entry is any different. Do you?
# Posted By Raymond Camden
| 8/22/08 8:28 AM
@Gary
Lots of people buy Advil too and it works. But it says on the bottle use as directed so most folks don't take the whole bottle at once if they've thrown their back out or have a splitting headache.
If people are using this, or any other blog, for anything other than ideas then they have bigger problems than we can solve.
Ray, and others, are consistently right on the money and have solved many problems for me and countless other developers out there. He even stated in the initial post that their were many ways to skin the cat on this one so don't get too caught up on the cartesian join, listappend flux capacitor or any of the other ideas that were thrown out on the table here.
At any rate, as soon as Ray posts a few more blogs this will be back page news - LOL. Personally I can't wait for the new blogs. This is my CNN, my Nancy Grace - Ray keep doing what you're doing man, and don't forget to throw in the occasional corny joke while you're at it!
Lots of people buy Advil too and it works. But it says on the bottle use as directed so most folks don't take the whole bottle at once if they've thrown their back out or have a splitting headache.
If people are using this, or any other blog, for anything other than ideas then they have bigger problems than we can solve.
Ray, and others, are consistently right on the money and have solved many problems for me and countless other developers out there. He even stated in the initial post that their were many ways to skin the cat on this one so don't get too caught up on the cartesian join, listappend flux capacitor or any of the other ideas that were thrown out on the table here.
At any rate, as soon as Ray posts a few more blogs this will be back page news - LOL. Personally I can't wait for the new blogs. This is my CNN, my Nancy Grace - Ray keep doing what you're doing man, and don't forget to throw in the occasional corny joke while you're at it!
# Posted By Andy Sandefer
| 8/22/08 8:31 AM
@ ray - I agree. I do not think it's any idfferent. That's why I added the first comment.
Here is what I have come to on this:
I looked at both options cartesian join and listappend method. My options are all stored in the database
and are dynamic user entered. I allow user/admin
to enter a set of options like this:
Color:red,green,blue,yellow
Size:s,m,l,xl,xxl
And i pull this apart and enter into a table with
several fields, option name, option value, option id, and the option set id in one table.
Being dynamic i never know how many different options in a set so I then i do this for 1,2,3,4,5,6,7,8 options.
I have never heard of a normal product having 8 options
other then a car. I couldnt think of a better way to do this with one table and dynamic options.
<cfif listlen(namelist) IS 3>
<cfloop index="a" list="#evaluate('#listgetat(namelist,1,",")#')#">
<cfloop index="b" list="#evaluate('#listgetat(namelist,2,",")#')#">
<cfloop index="c" list="#evaluate('#listgetat(namelist,3,",")#')#">
<cfset comboList = listAppend(comboList, a & " " & b & " " & c)>
</cfloop>
</cfloop>
</cfloop>
</cfif>
Is there a better way which is true dynamic?
this is limited to the number I code.
I looked at both options cartesian join and listappend method. My options are all stored in the database
and are dynamic user entered. I allow user/admin
to enter a set of options like this:
Color:red,green,blue,yellow
Size:s,m,l,xl,xxl
And i pull this apart and enter into a table with
several fields, option name, option value, option id, and the option set id in one table.
Being dynamic i never know how many different options in a set so I then i do this for 1,2,3,4,5,6,7,8 options.
I have never heard of a normal product having 8 options
other then a car. I couldnt think of a better way to do this with one table and dynamic options.
<cfif listlen(namelist) IS 3>
<cfloop index="a" list="#evaluate('#listgetat(namelist,1,",")#')#">
<cfloop index="b" list="#evaluate('#listgetat(namelist,2,",")#')#">
<cfloop index="c" list="#evaluate('#listgetat(namelist,3,",")#')#">
<cfset comboList = listAppend(comboList, a & " " & b & " " & c)>
</cfloop>
</cfloop>
</cfloop>
</cfif>
Is there a better way which is true dynamic?
this is limited to the number I code.
# Posted By Andrew
| 8/24/08 8:38 AM
This does NOT answer your q Andrew, it is just a quick aside. You do not need those evaluates. If namelist is a list of variables you can get the values like so:
#variables[listGetAt(namelist,1)]#
Note to I dropped "," since the comm is the default delimiter.
#variables[listGetAt(namelist,1)]#
Note to I dropped "," since the comm is the default delimiter.
# Posted By Raymond Camden
| 8/24/08 10:03 AM
That small a change made the page load wayyy faster
amazing variables is faster then evaluates.
I really need to start reading and learn some more.
Thanks.
amazing variables is faster then evaluates.
I really need to start reading and learn some more.
Thanks.
# Posted By Andrew
| 8/24/08 10:31 AM
@ Andrew:
try replacing the ListAppend with ArrayAppend inside your loop. Then just use ArrayToList later on when you need to access it as a list. Read this blog which gives details of how much faster ArrayAppend (and other methods) are over ListAppend or just string concatenation.
http://www.aliaspooryorik.com/blog/index.cfm/e/pos...
try replacing the ListAppend with ArrayAppend inside your loop. Then just use ArrayToList later on when you need to access it as a list. Read this blog which gives details of how much faster ArrayAppend (and other methods) are over ListAppend or just string concatenation.
http://www.aliaspooryorik.com/blog/index.cfm/e/pos...
SELECT C.Colour, S.Size
FROM Colours C, Sizes S
If wanting to limit it to just those colours and sizes already in our lists, add:
WHERE C.Colour IN ( <cfqueryparam value="#colorList#" list="yes"> )
AND S.Size IN ( <cfqueryparam value="#sizeList#" list="yes"> )