Website Product Specification Narrow Down URL List

I am looking for a simpler or elegant solution in object oriented (C#) or functional (F#) programming language to solve the following problem.

Given many thousands of products with specifications on an eCommerce website, consider the permutations of specification narrow down. Specifically, what is the smallest set of specifications that yield different sets of products that a customer may be interested.

Users are given the opportunity to shop by any specification. For example take bolts. They have Drill Size, Finish (Plain, Zinc, etc.), Head Type, Head Height, Grade, Length, Package Quantity, etc.

In an average product category, say there is 1000 products and 10 specifications. If each specification can be added to the URL, what are the possible pages. However, I am not just interested in all the permutations. Consider the following points: - by selecting one specification, many others are also selected because they are shared by the remaining set of products, and can be shown to the website user as also shared among the listed products - once there is only one product listed all remaining specifications are naturally selected - b开发者_运维知识库y placing the specification in a strict order (such as alphabetical) on the URL, this reduces unnecessary repetition

First, the problem was tackled with recursive SQL, (CTE syntax) and this became unwieldy, yielding millions of combinations per category before being able to start clearing out duplications.

Now, I am researching C# that takes on category at a time. 1) specifications types are ordered alphabetically 2) all products sets are compared with all others with the following code so as auto-select/auto-define additional specifications:

public bool DefinesThisValue(CategoryValue value)
{
    if (this.ProductIds.Count > value.ProductIds.Count) return false;

    var intersect = this.ProductIds.Intersect(value.ProductIds);
    return intersect.Count() == this.ProductIds.Count;
}

2) next it attempts to traverse all selection sequences, respecting the above, after each selection the an addition path node is addition to a collection and path objects of copy/split if there are multiple additional selectable specifications

My current solution is requiring much debugging and has become overly complex. Perhaps someone familiar with set theory and F# could propose something awesome. Thanks for your consideration.

In repsonse to Daniel, here is a SQL layout of data.

DATA STRUCTURES: TABLES:

    CREATE TABLE Category
(
    Id int identity not null,
    PRIMARY KEY CLUSTERED (Id)
)

CREATE TABLE Spec
(
    Id int identity not null,
    CategoryId int not null REFERENCES Category(Id),
    Name varchar(100) not null,
    PRIMARY KEY CLUSTERED (Id)
)

CREATE TABLE SpecValue
(
    Id int identity not null,
    SpecId int not null REFERENCES Spec(Id),
    Value varchar(200) not null,
    PRIMARY KEY CLUSTERED (Id)
)

CREATE TABLE Product
(
    Id int identity not null,
    PRIMARY KEY CLUSTERED (Id)
)

CREATE TABLE SpecValueProduct
(
    Id int identity not null,
    SpecValueId int not null REFERENCES SpecValue(Id),
    ProductId int not null REFERENCES Product(Id),
    PRIMARY KEY NONCLUSTERED (Id)
)

A simple example might include the following example data:

Product 1: Coffee Maker A, Color: Black, Cups: 2
Product 2: Coffee Maker B, Color: Black, Cups: 4
Product 3: Coffee Maker C, Color: Grey, Cups: 4

In this example, then if Color: Grey is selected, then Cups: 4 is also defined. But, if Color: Black is selected, then user has option to select Cup: 2 or Cups: 4.

A url might look like: 'site.com\coffee-makers?Color=Black&Cups=4

---

Okay, let's start simple. The first thing you need to do, I presume, is show the list of available specs and their values. Easy enough...

select s.Name, v.Id, v.Value 
from SpecValue v 
  inner join Spec s on s.Id = v.SpecId
where s.CategoryId = @CategoryId

If you want, you can also join it to SpecValueProduct to limit the specs/values to those currently in use.

Next, when spec values are selected, you'll need to run a similar query, but limiting it to relevant values, i.e., those applied to a product where the selected spec/values are present.

Given the parameter:

declare @SelectedSpecValue table (SpecValueId int)

You might do something like

select distinct s.Name, v.Id, v.Value, 
  cast(case when n.SpecValueId is null then 0 else 1 end as bit) as IsSelected
from SpecValue v 
  inner join Spec s on s.Id = v.SpecId
  inner join SpecValueProduct p on p.SpecValueId = v.Id
  inner join
  (
    select distinct y.ProductId
    from SpecValueProduct y
      inner join @SelectedSpecValue z on z.SpecValueId = y.SpecValueId

  ) x on x.ProductId = p.ProductId
  left join @SelectedSpecValue n on n.SpecValueId = v.Id
where s.CategoryId = @CategoryId

If I've written that correctly it should constrain the available spec values by those already selected. If not, it might do something entirely different. :-)

Am I oversimplifying this? Does it answer your question?