Introduction
This article provides a simple and scalable solution for the problem of managing different product options (e.g., size, color etc.) in a product catalog.
Data Model
In the picture below, you can see the LINQ DBML preview. Each product can be associated with any number of options (size, color, etc.), and each option is associated with any number of values (Red, Blue, small, medium, etc.). The tables Combination and CombinationDetail are used for storing the prices of different combinations of option values.
The SQL script provided in the downloadable zip file will create database, tables, relations, and fill sample data in it.
In our sample database, the product "Test Product" has three attributes (Color, Size, and Warranty). Suppose the combination Red-Small-WithWarranty has price = 200. This means we will have a line in the Combination table with price = 200 and three lines, one for each value in the table CombinationDetail.
This data structure allows to assign any number of options to a product, and allows each option to have any number of values.
Windows Client Application
The downloadable zip file contains a Windows Forms project too. This project allows a user to generate all possible combinations for a certain set of options-values, and to manage the different combinations' prices.
Please adjust the connection string by yourself in the app.config file.
After generating combinations and once all different prices have been defined, it's possible to choose a different set of values and check its price.
The code
There are two interesting pieces of code in the Windows client application. The class CombinationBuilder is used to generate all possible combinations of a set of options-values:
public class CombinationBuilder
{
private Dictionary<int,> _counters;
private List<string> _combinationList;
public CombinationBuilder(Dictionary<int,> counters)
{
_counters = counters;
}
public List<string> CombinationList
{
get
{
return _combinationList;
}
set
{
_combinationList = value;
}
}
public void BuildCombinationString()
{
CombinationList = new List<string>();
recursion(-1, "");
}
private void recursion(int level, string previousString)
{
level++;
for (int i = 0; i < _counters[level]; i++)
{
string currentString =
previousString + i.ToString() + ",";
if (level+1 < _counters.Count)
{
recursion(level, currentString);
}
else
{
if (currentString.Length > 0)
currentString =
currentString.Remove(currentString.Length - 1);
CombinationList.Add(currentString);
}
}
}
}
This class simply generates a list of strings like the following:
0,0,0
0,0,1
0,0,2
1,0,0
....
3,2,2
This list is used as a schema to generate data in the database.
The other interesting code is the query that gets the price of a given combination of values. The query has been made using LINQ to SQL and the PredicateBuilder class (http://www.albahari.com/nutshell/predicatebuilder.aspx).
private void LoadPrice()
{
int optionCount = 0;
var predicate = PredicateBuilder.False<combinationdetail>();
foreach (var control in grpOptions.Controls)
{
if (control.GetType() == typeof(SingleOption))
{
int optionValueId = (control as SingleOption).SelectedOptionValueId;
predicate = predicate.Or(p => p.OptionValueId == optionValueId);
optionCount++;
}
}
ProductOptionsDataContext productOptionsDataContext =
new ProductOptionsDataContext();
var combination = productOptionsDataContext
.CombinationDetails
.Where(predicate)
.GroupBy(p=> p.Combination)
.Where(p=> p.Count()==optionCount)
.Select(p=>p.Key);
decimal price = 0;
if (combination.Count() == 1)
{
price = combination.Single().Price;
}
else
{
price = SelectedProduct.Price;
}
lblPrice.Text = string.Format("Price = {0}", price.ToString());
}
Note
A big number of options and option values can generate a huge number of different combinations. Maybe in this case, it's not useful to define a different price for any combination, so it's better not to generate all combinations on the database, but only the ones with a specific price. This solution is not provided in the sample code but it's quite easy to implement.
