PHP & MySQL Building a Dynamic Filter Architecture for an Internet Directory

Question

PHP

Good afternoon!
I have a need to write an Internet directory module.
And it needs to take into account the possibility of the presence of dynamic fields, for example, there may be a clothing catalog and it will have dynamically created fields (gender, size) or, for example, a catalog of cars with dynamic fields (year of manufacture, mileage, engine size), the number of fields is also dynamic . And you need to create for this good the possibility of filtering and sorting.
Maybe someone faced such a problem?
Tell me how you organized the database architecture so that it would be possible to sort and filter data by dynamic fields?

Answer 1

In my opinion what you need is called entity-attribute-value. Pros, cons, criticism and implementation features can be searched for this phrase - a lot has been written.
Unfortunately no personal experience.

Answer 2

Create an attribute table with the fields attribute_id, attribute_name
This table contains only textual information, that is, the actual “Year of manufacture”, “Mileage”, “Engine size” and so on
Create an attribute_to_product table with the fields
id, product_id, attribute_id, attribute_value
And join the table with joins products with attributes

Answer 3

As an option, you can consider PgSQL + hstore.
The system allows you to store unstructured data such as "hashes" in the database and, most importantly, to search indexed (!) on them.
I did a trial version, searching the database for about 100K lines of random data takes about 100 ms.
It is unlikely that there will be more articles of dresses or car models.

Answer 4

I myself have the task of writing something similar for an online store, I didn’t find a competent manual on this topic, I have to invent my own.

Answer 5

If the values ​​are discrete, you can do this.
First, denormalize everything all the way.
Product groups
— ID
— Product name
:
— ID — Property
name — ID — Product group — Name Property values ​​— ID — Property ID — Value Product property values ​​— ID — Product ID — Property ID — Property value ID It is already possible to add, edit, search as you like and show. True requests will be terrible - complex and slow. Therefore, at this stage it is advisable to get a prototype, debug it, and move on to normalization.
— add serialized data to products {Property: Property value}
— add serialization processing during actions: adding, editing, displaying
At this stage, it will already work much faster.
Now, if you need to make a highly loaded service, add caching indexes. Such special tables:
- query
- serialized result of the last selection
- selection relevance flag
And add a method that recalculates the selection relevance flag when adding and editing.
If the interface is fancy, there should be as many indexes as there are bells and whistles.
Move consistently, use SCRUM or at least UML - then you will do it during the day.