How are variants of one product created in the database?

Question

PostgreSQL

Hello, I really need your answer, for 3 days I have been looking for information on how to create several options for one product in the database in
order to create a similar functionality, an example in the photo

There is a product and plus it has several different models by type of color, price and size.
I have already created a Product and ProcuctVariant model, and made a one-to-many relationship

Product.hasMany(ProductVariant, { as: 'variant' })
ProductVariant.belongsTo(Product)

In the output I get:

{
            "id": 1,
            "name": "Nike air max 97",
            "createdAt": "2021-10-06T20:19:26.898Z",
            "updatedAt": "2021-10-06T20:19:26.898Z",
            "variant": [
                {
                    "id": 2,
                    "title": "Nike air max 97 Black Edition",
                    "img": "dbc1cd8a-e246-4001-a748-a4fed4678c6f.jpg",
                    "color": "purple",
                    "price": 190,
                    "createdAt": "2021-10-06T20:19:26.924Z",
                    "updatedAt": "2021-10-06T20:19:26.924Z",
                    "productId": 1
                },
                {
                    "id": 3,
                    "title": "Nike air max 97 Purple Edition",
                    "img": "f9bce92c-da44-4b95-ba51-a38aa8e479bc.jpg",
                    "color": "purple2",
                    "price": 200,
                    "createdAt": "2021-10-06T20:23:00.826Z",
                    "updatedAt": "2021-10-06T20:23:00.826Z",
                    "productId": 1
                }
            ]
        }

But the problem is how can I display all variants of one product on one page, because initially I thought to display only one type of product on the page, that is, like this:

{
                    "id": 2,
                    "title": "Nike air max 97 Black Edition",
                    "img": "dbc1cd8a-e246-4001-a748-a4fed4678c6f.jpg",
                    "color": "purple",
                    "price": 190,
                    "createdAt": "2021-10-06T20:19:26.924Z",
                    "updatedAt": "2021-10-06T20:19:26.924Z",
                    "productId": 1
                },

But now I realized that I still need to add a navigation for switching colors, that is, a transition to a clear variety of this product
. I don’t even know how to explain it in detail, but I want to make an analogue of adidas, functionally with switching the variety of one product.
I would be very grateful for help

Answer 1

I once separated the essence of a product card (GOODS) and a commodity item (PRODUCT). A commodity item is an object of inventory control (that is, it is physically located in a warehouse), and a commodity card only groups these items in a convenient way and stores photos, a name, but not characteristics.
Then I implemented a many-to-many relationship (one product item can be included in several product cards), but this was fraught with collisions of double orders, when there is one unit of goods physically in the warehouse, and the user added it to the cart several times through different cards. Of course, the transaction will not go through, but the user will know about it at the last moment if no additional notifications are made in the interface.
Therefore, when unloading from the accounting system, I created a third entity (ITEM), which "virtualized" the GOODS_PRODUCT table. Thus, on the store site, I had GOODS and ITEM.goods, and a unique index was configured for ITEM.product (for one store, it was impossible to create two cards referenced by the same product item).
I kept the characteristics (size, color, weight, etc.) at the heading level. In this case, it is quite easy to implement an algorithm that derives options from differing characteristics when the user opens a product card.
That is, it is important to understand that the user ultimately chooses exactly the commodity item, and the card is nothing more than a page on the site.