is there a really good guide on database design for e-commerce?
The product can be of simple type – it means that users can buy this product.
The product can have also multiple variants. So one product can have few options to buy.
The main issue I have is how to bind them with the order/cart entity.
When a user is buying a simple product (without variant) there is no problem. I can bind this product with the cart and then with the order.
But when the user is buying a variant?
Should I create something like:
product: id: int title: string ... //other fields
Variant table. There might be many options like colour, size, etc and I don't think it should be separate relation for this
product_variant: id: int option: string value: string
relation between product and variant table
product_variant_relation: id: int id_variant: int id_product: int
and then in the cart and in the order I should simply add for example:
Order item table:
id: int order_id: int product_id: int variant_id: int|null
where variant_id = product_variant_relation.id ?
Is it that simple ?
Or there is another better solution ?