What is the structure of the base for a product with different characteristics and prices of these characteristics?

Question

Database design

Product example:
Product A, Size S, Color red, 5000 rubles
Product A, Size L, Color red, 4500 rubles
Product A, Size XL, Color red, 3000 rubles
Product A, Size S, Color blue, 3000 rubles
Product A, Size L, Color blue, 3000 rubles
Product A, Size XL, Color blue, 5000 rubles
Please tell me the database structure for optimal storage of this data.

Answer 1

Depends on the goals and understanding of their goals by the customer.
Does it need to search for any word in the title, or just the exact match of the beginning of the word?
Does it matter to him that products of different colors and sizes are displayed on one page, and then product options are displayed through the drop-down list?
Or are all products of different colors/sizes separate pages on the site?
Is it important for him to keep track of the quantity and display only what is available?
Or maybe for SEO purposes it is important to display everything always, but from the sizes / colors, only what is available?
Can more features be added (not just color and size)?
Is there a single size grid for all types of goods?
Will a product with different sizes/colors have a different price, or is the price of a product with the same name always the same, regardless of color and size?
What are the requirements for the speed of work / development cost (you can sharpen the speed a lot, but this is more brain effort)?
Without these clarifications , it is impossible to come up with an optimal data storage structure .
Here you can advise the structure of EAV.
She's really versatile. But rather slow when fetching data, and this advice will only be from an uneducated developer.
If you do it wisely, then you need not relational PostgreSQL, but a Full-text-search DBMS (SphinxSearch, for example).
Just for such data structures, the DBMS of the FTS type will give the maximum search speed.
Moreover, it is suitable both for searching by part of the name, and for searching by characteristics.
However, you can try to tie the FTS built into PostgreSQL.

Answer 2

Amazon and taobao (aliexpress) have the following structure (at least they give it that way in api).
Amazon doka Product data example
Amazon Product
data example Taobao
There is a main product. Usually it does not have any characteristics, but there is a price, a discount, pictures, a description, etc.
The item belongs to one or more categories. If there is more than one category, then there is one main one.
The main product can have a list of child products. All products, both main and child, have identifiers, the only difference is in the reference to the parent - the main Amazon product refers to itself.
Taobao does not have child products, all options for feature combinations (SKU, OtapiConfiguredItem in the example) are described inside the main product.
As a result, for Amazon, all product options are independent (have different URLs), for Taobao, everything is tied to one product (and URL).
Taobao always has one store for a product. Amazon may have several of them, and each, respectively, has its own prices, discounts and delivery methods.
In general, it is obvious that the types of characteristics are better stored separately (although you can denormalize). So you get the opportunity to work with them in a complex way (for example, you can easily see all the possible options for each type). Probably the same should be done with variants of these characteristics.
Do not try to store sku or characteristics with json)).
It is also a big question whether your product name or description, properties, pictures, etc. are changing. depending on the set of characteristics (this was the case with dx.com before).
It is also important to understand whether your product is always in one category or can be in several at the same time.
Also think about discounts. They usually have a limited duration. They may be per product group. They may have different conditions, for example, a discount is issued only when certain conditions are met. For this reason, I would store discounts separately from the product.
I would try to keep the product table small (in terms of the number of columns and their volume). In general, it is easier to operate with small amounts of data than trying to filter something in a huge table with a bunch of indexes and fields.
Keep product statistics separately as well. Description, if it is large, store separately. If each product has a large number of images, it may be worth storing them separately as well.

Answer 3

These are options (my scheme, used in a friend's CLOTHING store))) )
although in 90% of stores there is a more complex scheme with options, a typical one, so to speak, but in fact all the features of this typical scheme are rarely used, but it hits the server aptly:

spoiler