The architecture of the database and sorting?

Good night! I would like to hear advice and criticism of my architecture database regarding multiple entities, as well as assistance and advice in sorting these things.

There is a service in which are stored templates of the goods, they are added by the administrator. Then, suppliers can choose from the list a product and ask price and Qty. After which it is sold. Also, there are such things as characteristics, group characteristics, which we collect N characteristics and tied to the product. For example, the characteristics of the phone will be to have, say, screen, memory, storage.

Each characteristic has its values, for example screen: ips, oled, amoled, etc...

Therefore I have a table to store the names of the features
specifications:
id
title
position


The values of the characteristics I stored in the table
specifications_values:
id
specification_id
value
position


Group characteristics 2 tables, one stores the name of the group, the other is the relationship between group and characteristic.
specifications_groups:
id
title

specifications_groups_relation:
id
specification_group_id,
specification_id


I need to store the relationship between a product characteristic that is tied to and value this characteristic. For this, there is a table
products_specifications:
id
p_id - the ID of the product
v_id - ID of the value characteristics,
s_id - the ID characteristics

When a vendor creates a product, he chooses a group of characteristics and it appears select N tags, the number of which is equal to the number of characteristics in the group.

Further, when there is a request to the server, I create a product, take his ID, take the values for the v_id, s_id, and stores them.

The thing - how wrong nagymajtenyi base and looks to solve my problem? What to remove and what to add? Can have comments and tips, I would be very grateful for any constructive criticism and advice.

Then I need to display the products by sorting them according to certain rules. For example, goods from the category Iphone 6 will have a group of characteristics "Phone", although, of course, the user can choose any other.

So, I need to take all the goods from this category, take characteristics from group characteristics attached to the product and sort them. To do this, tables specifications_values and specifications field position.
The essence of the thing - I sort of characteristics in position, take the first feature, I go into it, sort items by values of this feature based on the position field. After receiving the results, I need to take the following feature, go to its values and re-sort the goods which have been sorted by the previous description, and so until until you go through all the bound properties. Then, I deduce.

In the end I should get something
Phone X 16GB(position:1) EU(position:1)
Phone X 16GB(position:1) CHINA(position:2)
Phone X 32GB(position:2) EU(position:1)
Phone X 32GB(position:2) CHINA(position:2)


What is the most correct method I should use. To break the sort into parts or is it possible without a lot of blood to make using eloquent orm?

A project on laravel. Thank you very much for any help!
March 23rd 20 at 18:42
2 answers
March 23rd 20 at 18:44
Solution
The crutches of the type of EAV is often best to settle using jsonb.

without a lot of blood to make using eloquent orm?
A project on laravel.

Choosing the task under tools, not tools for the task, and have nails a microscope to hammer.

Upd: Here is an example of how to do this quickly comfortable and beautiful - https://coussej.github.io/2016/01/14/Replacing-EAV...

Upd2: best practices usually start with a behavior model and data model, problems with the loads you obviously don't have.
March 23rd 20 at 18:46
The thing - how wrong nagymajtenyi base and looks to solve my problem? What to remove and what to add?

Looks like everything is fine. Especially if it all works. Because to make the structure in dB is half the problem, you need more to fill it and do it on a sample.

Can have comments and tips, I would be very grateful for any constructive criticism and advice.


1) it Seems that you invent a pattern of EAV. Suggest googling how to use it in conjunction with the database.

2) I'm not sure, but I can assume that you may have problems on large number of products and composite filters. So I advise you to think about what kind of real volume of records you will have +/-. And generate this number of data in the database. And test your solution on it.

Find more questions by tags Database design