How to make product properties in the database?

Question

Database

The task is the following - I am making an online store in which the goods have several attributes. Accordingly, the combination of these attributes will have a different price .. With this, I had great difficulties.
1) How to build an architecture in the database so that the properties are dynamic and can be added to product groups.
2) Okay, I studied the architecture of eav and roughly understood how I would store them, but what to do with the selection is a little unclear .. That is, in theory, I will have products, offers will be attached to them, having a price, to which attribute values ​​will be attached product.. That is, in order to get the price of a product with specific characteristics, I will first have to select all offers associated with IDs of such and such values?
3) When the user selects properties, he should see the offer price corresponding to the selected attributes. But if you constantly request it from the server, the price update will take quite a long time.. What is the best way to solve this issue?

Answer 1

EAV is a performance guano.
It can be used for primary data storage before you digest and throw out ready-to-work data in Solr or Sphinx, for example.
But it is undesirable to use EAV directly for data sampling for performance reasons.
If you absolutely want to do this with a relational DBMS, then denormalization will help you (the data will be duplicated, but this is not scary, but it will work smartly).
And if you want to do it right and make it work as quickly as possible, then I would suggest considering systems that are more suitable for this task: Tarantool, Sphinx,
Solr.

Answer 2

If you are using a PostgreSQL database, then you can store attributes, for example, in a JSON table field. They are indexed and you don't have to do a bunch of JOINs like with EAV to get the data out.
But there are obvious disadvantages here, this is the removal of an attribute, for example. Can be quite heavy. But it all depends on how it's implemented.
In general, you can take EAV and add JOINs to the request with attributes, or JSON.
I forgot to say, JSON supports indexes and field queries.

Answer 3

If I understand correctly, you are going in the wrong direction. Products with different characteristics are different products, so the price should be set for the product, not its characteristics.

Answer 4

Example: there is a Philips light bulb. And there is an attribute - power. 40, 60, 75, 99 watts. So, these are 4 different products . The name is the same, but the group of attributes is different. The fact that you can show them on the product page as one product should be specified by the product field with some kind of unifying key. All.