I am developing a website for car ads. The search is quite complicated as you can search for cars by up to 100 filters (make, model, engine, hp, color, year, 4X4, ..... ).
I have two models of how I can design the tables
1- Relational
2- Entity attribute value
The entity model seems to be a better fit because:
- It is dynamic (one can add any number of fields without changing schema)
- Faster because we have few indexes
INDEX(key, value, publication_id)andINDEX(publication_id)
However when joining the publication table with key_values table (two tables) it returns N(publcations) * M(KeyValues) even worse if join with another table (Images) it will be N(publcations) * M(KeyValues) * Q(Images) so for 20 publications with 30 key/values and 5 images it will return 2500 rows, isn't this a waste of space and memory?
On the other hand.
The relational model seems better in terms of relational design and joining: 10 publications will each have one row in car_options table multiply this by the number of images, say 5 and we will have 50 rows.
Two big issue with this design are:
- It is not dynamic as the Entity Attribute Value.
- I don't know how an index can be designed for such table so that it can be used for any combination of filter options?
Questions:
If using the relations model, how to design the index to search for any combination of options ?
If using the EAV model, what are the cons of using long table (Key/Value) instead of a relation model, or it is just find for MySQL to handle such case?
