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
The values of the characteristics I stored in the table
Group characteristics 2 tables, one stores the name of the group, the other is the relationship between group and characteristic.
I need to store the relationship between a product characteristic that is tied to and value this characteristic. For this, there is a table
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!