How to create a faceted search with SQL Server

Question

I have an application which I will be accessing SQL server to return data which has been filtered by selections from the application as any common faceted search. I did see some out the box solutions, but these are expensive and I prefer building out something custom, but just don't know where to start.

The database structure is like this:

The data from the PRODUCT table would be searched by tags from the TAG table. Values which would be found in the TAG table would be something like this:

 ID      NAME
 ----------------------
 1       Blue
 2       Green
 3       Small
 4       Large
 5       Red

They would be related to products through the ProductTag table.

I would need to return two groups of data from this setup:

1. The Products that are only related to the Tags selected, whether single or multiple
2. The Remaining tags that are also available to select for the products which have already been refined by single or multiple selected tags.

I would like this to be all with-in SQL server if possible, 2 seperate as stored procedures.

Most websites have this feature built into it these days, ie: http://www.gnc.com/family/index.jsp?categoryId=2108294&cp=3593186.3593187 (They've called it 'Narrow By')

I have been searching for a while how to do this, and I'm taking a wild guess that if a stored procedure has to be created in this nature, that there would need to be 1 param that accepts CSV values, like this:

 [dbo].[GetFacetedProducts] @Tags_Selected = '1,3,5'
 [dbo].[GetFacetedTags] @Tags_Selected = '1,3,5'

So with this architecture, does anyone know what types of queries need to be written for these stored procedures, or is the architecture flawed in any way? Has anyone created a faceted search before that was like this? If so, what types of queries would be needed to make something like this? I guess I'm just having trouble wrap my head around it, and there isn't much out there that shows someone how to make something like this.

Answer 1

A RDBMS for being used for faceted searching is the wrong tool for the job at hand. Faceted searching is a multidimensional search, which is difficult to express in the set-based SQL language. Using a data-cube or the like might give you some of the desired functionality, but would be quite a bit of work to build.

When we were faced with similar requirements we ultimately decided to utilize the Apache Solr search engine, which supports faceting as well as many other search-oriented functions and features.