EAV alternative, base structure?

Question

SQL

Good afternoon!
In the online store there is a question about the additional parameters of the goods. The parameters are finite. Many types of goods. The first thing that comes to mind is an exemplary EAV architecture. On the Internet they write that this is bad and an alternative is to create your own table in the database for each type of product.
I'd like to know what's wrong with that. Real reasons, not fictitious ones that “I don’t rub”. And see the structure of the database, how to work with it if you make your own table for each type of product. How to show for example all items in stock. Or all red goods. If there are several tables for each type of product ... I can not think of an optimal scheme.
Used ms sql 2008.
And one more thing… I initially know all the possible parameters, i.e. there will be 2 tables… Products and ProductsDescriptors which contain ProductID(int), DescriptorID(int), DescriptorValue(str). And I know what ID that means at the creation stage. Those. this is not an online store for abstract goods, but goods I know. For example DescriptorID=7 is responsible for the color of the TV...

Answer 1

Base structure . On tests, 250 thousand products with 10 thousand parameters (10 parameters per product) work out in less than 1 ms on posgresql, so the base will obviously not be the most slow place.
>I would like to know what all the same is bad.
There is nothing wrong with EAV. If sufficient hardware resources are allocated for the current size of the database and indexes are placed in the database in the normal form, then the database will obviously not be the most slow place. So "advisers" who say so without specific arguments can be immediately safely sent to the forest.

Answer 2

Typically, for EAV online stores, the structure is something like this:
Category (* - *) Option ( 1 - *) Value ( * - 1 ) Product
Search for products is allowed via INNER JOIN and creates quite a large overhead. This is the only, and quite significant, disadvantage of this approach. It is solved using views in the database, or NoSQL solutions. There are also options to use faceted search, for example through Sphinx. But development flexibility is a pretty big plus for me.
In your case, if you implement your version with a table of descriptors, you get the following structure:
Product (* - *) Value ( 1 - * ) Descriptor.
Moreover, if the values ​​of the descriptors are known to you, then it is logical to put it in, say, ENUM or something else. In fact, this is not an EAV pattern, it's just one of those alternatives.
A search for such a structure will also be implemented through INNER JOIN (although it can be done differently, but in theory everything rests only on indexes, and the performance will not differ much from the search method) and will still be slower than through a view or just from a table.

Answer 3

In general, the best alternative to EAV is schemaless databases, the same MongoDB , for example. Another option is to use SQL (EAV or separate tables) + schemaless search engine (e.g. elasticsearch ).