I'm trying to think about design for some hybrid E-commerce/car Dealer Database.
All is okay, but one problem that I can't manage to solve, or better say - solved, but can be better than this?
I have categories that don't have same properties. Cars have their Car Make and Car Model respective to that Make. But parts and some other categories for selling have Make but User when Adding post will type Model. Some categories have Year, Drive... But other have their totally different properties.
All I want to say - Categories have different properties. Post will be searchable by Category.
When user choose Cars - Search form is opened with Make, Model, Year, Drive... But other Categories will give other properties to choose for search.
Post Table - is Single Post(Car, Part, Product...) It will keep common properties for every category.
Post_Value Table - is Property Value
Possible_Values Table - Is all possible Values for that property if it has one. Like "Make" Property that will keep possible values of Audi, BMW, Renault...
Input_type Table - Will keep what is input type used for that Property (Text, Dropdown, Checkbox, Radio...) for Front End, used for dynamic add of properties in Form when user adding Post.
I thought of something like this. But "problem" is that every property will be stored in varchar type. And for 100 Posts with 40 Properties Table will be filled with 4000 rows.
That is bad for searching - I will get list of properties and I need to compare that list with list of properties for every post. And every item from query list must be matched with items from property list from Post_Value table.
I will use Entity framework with predicates for this to match all items with all other list items. I will Index Value and Name of property in Property and Post_Value table.
But speed of this design is really worry me. Any other suggestion or tip to improve this if this is a good way to think about?