Product Search Engine Design with Sphinx and Faceted Search

I think there may be something wrong with your model. Firstly I think you need
Category
has_many :products
has_many :attribute_types

Product
belongs_to :category
has_many :attributes

AttributeType
belongs_to :category
has_many :attributes

Attribute
belongs_to :product
belongs_to :attribute_type

Is there a problem with the above? If one has a product
then product.category.attribute types will give one a collection of
attribute types relevant to that model. One could also use product.attributes to get a
collection of attributes, but since each attribute has an associated
attribute type one can also get the attribute types for a product by
this route. I am no database expert, so I may be wrong, but I suspect
that it is not a good idea to have two separate relationship routes between
models like this.

My other problem is that I am not sure what you are trying to achieve. Can you explain in a couple of sentences what data you wish to extract when the user clicks on the Book category for example?

Colin

Hi thank you for your answer

The models you wrote are ok, I just simplified them for illustration
purposes.

My problem is with search facets (filtering) using Thinking Sphinx.
Suppose that I had a book model with the fields author, publisher and
year. When a customer select the book category, he will be presented
with a list of books and on the left side, he will have the "facets"
like that:

authors
-author 1 (203)
-author 2 (125)
-author 3 (99)
-author 4 (38)
...

publishers
-publisher1 (199)
-publisher1 (21)
-publisher1 (408)
-publisher1 (134)
...

years
-2009 (109)
-2008 (33)
-2007 (12)
-2006 (500)
...

This would be very easy if I had a book model. Thinking Sphinx requires
a facet to be defined like this on the "book" model:

define_index do
  indexes author, :facet => true
  indexes publisher, :facet => true
  indexes year, :facet => true
end

THE PROBLEM IS THAT I DO NOT HAVE THIS MODEL AND THIS ATTRIBUTES
DEFINED. I don't have this defined because I would have to know all the
product types and all the possible attributes for each product type and
create a different model for each product type.

I understand. Sorry, I do not know enough about how the inner workings of Sphinx to be able to help.

Can anyone else help?

Colin