I've some items that goes towards the some category.
Each category might have different qualities.
- category cars has qualities color, energy, ...
- category pets have qualities weight, age, ...
Quantity of groups is all about 10-15. Quantity of qualities in every category is 3-15. Quantity of items is extremely large.
Primary requirement of this application is excellent search. We'll choose category, and enter criteria for every property within this category.
Need to design database with this scenario. (SQL Server 2005)
The classic design approach could be (the star denotes the main 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're able to accept the truth that every property value visits the DB like a string and kind conversion info is saved within the Property table, this layout could be enough.
The query would go something similar to 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 greater WHERE conditions you supply (conjunctively, e.g. using AND), the faster the query is going to be. For those who have correctly indexed your tables, that's.
Because it is, the answer isn't well suited for a complete text indexing situation. One more table that stores all of the text connected having a ProductId inside a more denormalized way may help here. This table would want upgrading through triggers that listen for alterations in the ProductProperty table.
When the user from the application has to choose a category before they are able to search, I'd separate your items into different database tables by category. This option would be also indicated because the groups themselves have so very little in keeping. Breaking it lower by category will even make each search considerably faster, since time will not be squandered searching through cars whenever your user is searching for a dog.
After you have the items separate directly into groups, it ought to be simple to produce the tables while using common qualities from the items in every category. The interface of the application ought to be dynamic (I am considering an internet form), for the reason that the qualities the consumer can decide on should change once the user chooses a category.
Please be aware that for those who have items that you would like indexed by multiple groups, this solution can lead to duplicate data inside your tables. There's a trade-off between speed and normalization when creating a database. Should you don't have items that suit in multiple groups, i quickly think this is the quickest solution (when it comes to search speed).
You might like to consider an Entity-Attribute-Value kind of arrangement, where one can "tag" each product with arbitrary title/value pairs of characteristics.