A
A
Andrey Boychenko2019-06-01 23:08:38
Database design
Andrey Boychenko, 2019-06-01 23:08:38

Database architecture and sorting?

Goodnight! I would like to hear advice and criticism of my database architecture, regarding several entities, as well as help and advice in sorting these things.
There is a service that stores product templates, they are added by the administrator. Then, suppliers can select an item from the list and set the price and quantity. After which it is sold. There are also such things as characteristics, groups of characteristics, according to which we collect N characteristics and bind them to a product. For example, a group of characteristics - a phone, will include, say, a screen, memory, storage.
Each characteristic has its own values, for example screen: ips, oled, amoled, etc...
Therefore, I have a table to store the names of the characteristics

specifications:
id,
title,
position

I store the characteristic values ​​in a table
specifications_values:
id,
specification_id
value,
position

For a group of characteristics there are 2 tables, one stores the name of the group, the other the relationship between the group and the characteristic.
specifications_groups:
id,
title

specifications_groups_relation:
id,
specification_group_id,
specification_id

Also, I need to store a connection between the product, the characteristic that is attached to it and the value of this characteristic. For this, there is a table
products_specifications:
id,
p_id - product identifier,
v_id - characteristic value identifier,
s_id - characteristic identifier
When a supplier creates a product, he selects a group of characteristics and N select tags appear, 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 its identifier, take the values ​​for the v_id, s_id fields, and save them.
The thing is - how wrong and heaped up does the base look for solving my problem? What to remove and what to add? Maybe there are comments and advice, I will be extremely grateful for any, constructive criticism and advice.
Next, I need to display the products, sorting them according to certain rules. For example, products from the Iphone 6 category will have the "Phone" feature group, although, of course, the user can choose any other.
So, I need to take all the products from this category, take the characteristics from the group of characteristics associated with the product and sort them. To do this, the specifications_values ​​and specifications tables have position fields.
The bottom line is - I sort the characteristics by position, take the first characteristic, go into it, sort the goods by the values ​​of this characteristic, based on the position field. Having received the result, I need to take the next characteristic, go to its values ​​and again sort the products that were sorted by the previous characteristic, and so on until I go through all the associated characteristics. Then I take it out.
I should end up with something like this
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 way I should use. Should sorting be split into parts or can it all be done without much bloodshed using eloquent orm?
Project in laravel. Thanks a lot for any help!

Answer the question

In order to leave comments, you need to log in

2 answer(s)
E
Evgeny Romashkan, 2019-06-02
@Ka4_Piton

EAV crutches can often be rendered more beautifully using jsonb.

without much blood to do using eloquent orm?
Project in laravel.

Choosing a task for tools, and not tools for a task, you will have to hammer nails with a microscope.
Upd: Here's an example of how to do it quickly, conveniently and beautifully - https://coussej.github.io/2016/01/14/Replacing-EAV...
Upd2: best practices usually start with a behavior model and not a data model, You obviously don't have any load problems.

V
Vitaliy Orlov, 2019-06-02
@orlov0562

The thing is - how wrong and heaped up does the base look for solving my problem? What to remove and what to add?

Everything looks fine. Especially if it all works. Because to make a structure in the database is the floor of the problem, you still need to fill it and make selections on it.
1) It looks like you're inventing the EAV pattern . I advise you to google how it is used in conjunction with the database.
2) I'm not sure, but I can assume that you may have problems on a large number of products and composite filters. Therefore, I advise you to think about what real volume of records you will have +/-. And generate this number of data in the database. And test your solution on it.

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question