Can I do without Entity-Attribute-Value?

Asked by guilherme goetze

You need to do something similar to an online store. The problem is in choosing the method of storing information about the goods, as there are more than a hundred types of goods and each of them has its own set of attributes.
The same Magento Commerce for these purposes uses the EAV structure, due to which select requests become very complicated and do not work very quickly. So far, the only thought is to periodically convert EAV into a normal table, from which samples will be drawn. Is there a more efficient way within MySQL or PostgreSQL?

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

Answers

linda woodward
The EAV problem is that each field lies independently of the object. Those. selection of 10 objects from 10 fields will make about 100 random seek. Solution options:
1. Memory tables - fast random seek, limited size and the need to synchronize with real data.
2. Sphinx and others like him. Additionally, we store the data in a format more convenient for faceted search.
3. nosql - easier to scale, data is often stored in memory, the lack of a scheme, but with the ability to create indexes on the fields.
Replies:
Maybe you know, will nosql work faster than EAV? And in general, which of the ways you suggested is faster in search by attributes, at least theoretically? - cinta buku
It all depends on the specific nosql and specific data. In most cases, it will be at least no slower.
If you need guaranteed speed - look towards the facet search using Sphinx or Lucene / Solr. The implementation will not be the most convenient (although perhaps there are already some beautiful wrappers), but the speed is much higher. But note that the search in this case is done according to predetermined ranges - a request like "price from $ 42.34 to $ 123.45" will not work. - tamar agatha kapanadze
will do about 100 random seek

Strange statement. If you take the same MySQL, then you will have a sample, for example, after one pass through each of 2 tables (Entity and Attribute) with indices. Those. 100 fields for 2 passes on indices. - lemmy
lack of a scheme, but with the possibility of creating indexes on the fields

Depends on the DBMS. Somewhere you can. - cheeriolafs
bascha
You can try a variant with an entity that has: an identifier, common fields (such as cost and availability), a search field (concatenated attribute values ​​through a wild separator) and an XML field (or any other format) in which all information will be stored .
Replies:
At first glance, very good. Thanks for the idea, you have to think about it. - nalitta
By the way, if the number of attributes is limited, then you can make an entity table (id, num_attr_1, num_attr_2, str_attr_1, str_attr_2 etc.). All columns are nullable. Keep separate schema (entity type). - patti kielt
Alternative QNX :: GxNeur - Is the demon falling off? :: Telephony for an office in Moscow? :: Provider selection in St. Petersburg :: MongoDB and OLAP
Leave Repply for Can I do without Entity-Attribute-Value?
Useful Links