Store/catalog database structure for SQL. What is the best way to store attributes and their values ​​in a database?

Question

MySQL

It is planned to develop a "standard" online store (implementation will be on Laravel).
Should be:

• product categories (categories: electronics, gardening, books,...)
  
• goods (products), while products of different categories have different attributes
  
• attributes (attributes: color, mass, size)
  
• attribute values ​​(values: green, red, 1, 2, 5, m, s, xl, xxl)
  

When creating a product, we specify a category, then, depending on the selected category, you can specify attribute values ​​(attributes and their values ​​must be created in advance).
I drew the following database structure:

So far, theoretically, how everything should work. But would you like to see comments / advice, who had experience of similar work. Is such an implementation possible in practice and will I encounter problems and limitations that are not yet known to me?

Answer 1

Wrong at the core.
You essentially drew EAV (there is such a ready-made data scheme for this task - google it, there are many examples).
Pros:
It is convenient to fit into the relational model, it is convenient to program (if it is convenient for you to work with the relational model), at first glance it fits well with any relational DBMS (MySQL, PostgreSQL, Oracle).
Cons:
Unreasonably inefficient in terms of performance.
There are much faster solutions.
However, if you have some strict requirements for performance or for minimizing server costs, it's fine.
For me, EAV is convenient as a means of editing (well, storing primary information).
But the search itself is much more efficient on full-text search engines, for example:
SphinxSearch (or its Manticore clone), ElasticSearch, etc.
And the more filters are searched for, the larger this gap is (although it is already quite noticeable for one filter).
In the most advanced full-text search engines, property search is a "cheap" operation that is perfectly compatible with basic full-text search.
Moreover, working with multiple filters by properties in these engines is even easier than full-text search (since the filters do not need to be broken into words, run through the stemming algorithm with its possible jambs - they are already "ready to use" right away).
As a bonus, you get the so-called almost "free" bonus. "faceted totals", answering the question "how many products do we have with such and such a property" (this is often used in displaying filters on websites).
====================
Conclusion:
If you care about MySQL - just see the description of the EAV data schema. She's exactly what it is for.
If you need everything to fly - see SphinxSearch (it is extremely fast and undemanding to resources).
PS:
SphinxSearch
Can extract data from MySQL. Based on them, it builds its own highly specialized but very fast search for samples, in your case, by name and by product properties.

Answer 2

I'm in a similar situation, using a JSON field. Those. the content of such a field for one product can be as follows: [ "color" : black, "size" : 42, "gender" : "male", "season" : "winter"], and for another as follows: [ "color" : black, "size" : 42, "kind" : "mountain", "sole" : "vibram"]. The current MySQL allows you to search for such fields, and the PL has functions for decoding JSON into a regular array.

Answer 3

Here's a hint for you (see screenshot), however, the colors have been removed, there is an order of magnitude more fuss with them both in the database and when searching.
Those. different color is different product.
On the other hand, sometimes colors need to be included in a combination, i.e. one item is a combination of size and color. This is true if the supplier (or the bulk of your suppliers) ships you something like this assortment - if you look closely, both sizes and colors hang on the same article. moronic of course, but sometimes without varicosities

spoiler