I'm trying to build a stock management app which allows a user to create products, upload images and track Orders between sales channels such as Etsy and Shopify. I'm currently designing the database to hold the products but struggling on how to handle product variations. Products can have a few different variation types (such as colour, size etc) and within the types can have multiple variation (such as Red Size 1, Red Size 2, blue Size 1 etc) The price and quantity should be stored with the variants if a prodict has them.
I initially had 1 table however this would be very inefficient as it would be duplicating all the non variant details such as the description and images. I then switched to 2 tables, one to hold the product and the other to hold the variations, as per below.
Products
Variations
I'm struggling with how to handle how some products with have variants but others wont. The products that dont have variants wouldn't have a quantity or price associated with them.
I also not sure how I would store orders for products that dont have a variant using the 2 table design above as I would need the variantID in the order table.
Orders
Any suggestions on how best to handle product variants?
Thanks
The one-table-design would be best. If a product exists in different versions, they are different and therefore should have different IDs , different prices etc. Imagine you want to store them ... for sure you store them separated by color instead of mixing them. Also the customer selects one specific color and will not accept another one, just because you sent it. These are signs that the products are not the same and should be seen as individual items which just share some part of data. Therefore try not define a relation with two tables.
Additional: move the price to a separate table because prices can change over time and for sure you want to have a hsitory.