Database Structure for a Faceted Search

Question

I am creating an eCommerce site that needs to have a faceted search tool so that customers can narrow down a product search via categories and classifications in the same style as ebuyer.com and Newegg.com (See left hand menus).

I initially dived straight into designing a database that ended up similar to an EAV structure (I didn't know what this was at the time), this initially seemed ideal as I could create unlimited categories, subcategories and other product classifications (i.e. Colour, Size, Recipient) that customers could use to find specific products. However, when I started trying to create SQL queries using AND conditions, I realised how normal simple queries became much longer and complex to write.

After spending a few hours reading through various posts on SO and articles on Google, I've come to realise the nightmare that would lye ahead if I continued with this method.

Question

How have sites like ebuyer.com and Newegg.com designed their faceted search?

Have I missed an alternative method or have they simply gone ahead with an EAV structure? I'm looking to avoid enterprise solutions like Lucene/Solr.

Answer 1

I don't know how they do it but you can achieve this by doing:

CREATE TABLE  product_facets (
  product_id INTEGER NOT NULL,
  facet VARCHAR(100) NOT NULL,
  facet_value varchar(255) NOT NULL,
  PRIMARY KEY (product_id,facet,facet_value),
  KEY (facet,facet_value)
);

INSERT INTO product_facets VALUES (1, 'COLOR', 'Red');
INSERT INTO product_facets VALUES (1, 'PRICE_RANGE', 'Less than 200');

INSERT INTO product_facets VALUES (2, 'COLOR', 'Green');
INSERT INTO product_facets VALUES (2, 'PRICE_RANGE', 'From $200 to $500');

INSERT INTO product_facets VALUES (2, 'COLOR', 'Blue');
INSERT INTO product_facets VALUES (3, 'PRICE_RANGE', 'More than $1000');

SELECT facet, facet_value, count(*)
FROM   product_facets f
INNER  JOIN products p ON p.product_id = f.product_id
GROUP BY facet, facet_value;

facet doesn't have to be VARCHAR. It can be a simple INTEGER as your application knows what it means.

Answer 2

http://wiki.apache.org/solr/PublicServers

Ebuyer uses solr and for good reason, you risk reinventing the wheel.

Answer 3

Well why do you call Lucene/Solr entreprise wise solution... seems to fit perfectly your need in my opinion.

Answer 4

I think you're mixing different concepts (which in turn may make it more difficult to find a solution).

Faceted search means basically filtering by a specific quality of the "item". That quality or property may be a category it is in, or it may be something else.

You could have a faceted search of users where you filter by their age, for example

[    User   ]
| name char |
| age  int  |

How you adjust Solr (or Sphinx) to get the end result might vary, but it has no influence in your data model.

I would start by conceptualizing/modeling what you're trying to represent/store in the database. How you display it or search it comes later.

Without more details, your question about how other sites designed their faceted search is too broad, and at the same time very simple: you just need to generate the different facets based on the different properties of the products; but you also seem to want to know how they have modeled their database to store the information.