What is best practice for this problem (different properties for different categories)?

Question

I have some products that belongs to the some category.

Each category can have different properties.

For example,

• category cars has properties color, power, ...
• category pets have properties weight, age, ...

Number of categories is about 10-15. Number of properties in each category is 3-15. Number of products is very big.

Main requirement for this app is very good search. We will select category, and enter criteria for each property in this category.

Have to design database for this scenario. (SQL Server 2005)

Answer 1

The classic design approach would be (the star denotes the primary key column):

Product
  ProductId*
  CategoryId: FK to Category.CategroyId
  Name

Category
  CategoryId*
  Name

Property
  PropertyId*
  Name
  Type

CategoryProperty
  CategoryId*: FK to Category.CategoryId
  PropertyId*: FK to Property.PropertyId

ProductProperty
  ProductId*: FK to Product.ProductId
  PropertyId*: FK to Property.PropertyId
  ValueAsString

If you can live with the fact that every property value would go to the DB as a string and type conversion info is stored in the Property table, this layout would be enough.

The query would go something like this:

SELECT
   Product.ProductId,
   Product.Name AS ProductName,
   Category.CategoryId,
   Category.Name AS CategoryName,
   Property.PropertyId,
   Property.Name AS PropertyName,
   Property.Type AS PropertyType,
   ProductProperty.ValueAsString
FROM
   Product 
   INNER JOIN Category         ON Category.CategoryId = Product.CategoryId
   INENR JOIN CategoryProperty ON CategoryProperty.CategoryId = Category.CategoryId
   INNER JOIN Property         ON Property.PropertyId = CategoryProperty.PropertyId
   INNER JOIN ProductProperty  ON ProductProperty.PropertyId = Property.PropertyId
                                  AND ProductProperty.ProductId = Product.ProductId
WHERE
   Product.ProductId = 1

The more WHERE conditions you supply (conjunctively, e.g. using AND), the faster the query will be. If you have properly indexed your tables, that is.

As it is, the solution is not ideal for a full text indexing situation. An additional table that stores all the text associated with a ProductId in a more denormalized way could help here. This table would need updating through triggers that listen for changes in the ProductProperty table.