I've some items that goes towards the some category.

Each category might have different qualities.

For instance,

  • 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.