Is it possible to do without Entity-Attribute-Value?

Question

SQL

You need to do something similar to an online store. The problem is choosing a way to store information about products, since there are more than a hundred types of products and each of them has its own set of attributes.

The same Magento Commerce uses the EAV structure for these purposes, due to which select queries become very complicated and do not work very fast. So far, the only thought is to periodically convert EAV into a normal table, from which selections will go. Is there a more efficient way within the MySQL or PostgreSQL framework?

Also, in the process of searching for an answer to this question, I repeatedly met the opinion that in this case it is worth abandoning the relational database and switching to noSQL. How true is this and will it work faster?

Answer 1

The problem with EAV is that each field is independent of the object. Those. fetching 10 objects from 10 fields will make about 100 random seeks. Solutions:
1. Memory tables - fast random seek, limited size and the need to synchronize with real data.
2. Sphinx and the like. Additionally, we store data in a format more convenient for faceted search.
3. nosql - easier to scale, data is often stored in memory, no schema, but with the ability to create indexes by fields.

Answer 2

You can try the option with an entity that has: an identifier, common fields (such as cost and availability), a search field (concatenated attribute values ​​through a wild delimiter) and an XML field (or any other format) in which all information will be stored.