What is best practice for this problem (different properties for different categories)?
The classic design approach would be (the star denotes the primary key column):
Product ProductId* CategoryId: FK to Category.CategroyId NameCategory CategoryId* NameProperty PropertyId* Name TypeCategoryProperty CategoryId*: FK to Category.CategoryId PropertyId*: FK to Property.PropertyIdProductProperty ProductId*: FK to Product.ProductId PropertyId*: FK to Property.PropertyId ValueAsStringIf 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.ValueAsStringFROM 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.ProductIdWHERE Product.ProductId = 1The 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.
If the user of the application has to select a category before they can search, I would separate your products into different database tables by category. This solution is also indicated by the fact that the categories themselves have so little in common. Breaking it down by category will also make each search much faster, since time won't be wasted searching through cars when your user is looking for a pet.
Once you have the products split up in to categories, it should be easy to create the tables using the common properties of the products in each category. The user interface of your application should be dynamic (I'm thinking of a web form), in that the properties the user can choose from should change when the user selects a category.
Please note that if you have products that you want listed in multiple categories, this solution will result in duplicate data in your tables. There is a trade-off between speed and normalization when designing a database. If you don't have products that fit in multiple categories, then I think this will be the fastest solution (in terms of search speed).
Most people are advising to use variations of the Entity-Attribute-Value (EAV) design. This design is overkill for your situation, and it introduces a whole bunch of problems, for example:
- You can't define data type for an attribute; you can enter "banana" for an integer attribute
- You can't declare an attribute as mandatory (i.e. NOT NULL in a conventional table)
- You can't declare a foreign key constraint on an attribute
If you have a small number of categories, it's better to use solution A in Bogdan Maxim's answer. That is, define one table Products with attributes common to all categories, and one additional table for each category, to store the category-specific attributes.
Only if you have an infinite number of categories or if you must potentially support a different set of attributes per row in Products is EAV a good solution. But then you're not using a relational database at all, since EAV violates several rules of normalization.
If you really need that much flexibility, you'd be better off storing your data in XML. In fact, you might look into RDF and semantic web frameworks like Sesame.