-1

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

  • ID (PK)
  • Name
  • Description
  • Category
  • etc...

Variations

  • VariantID (PK)
  • ProductID (FK - Products (ID))
  • Variant Type
  • Variant Name
  • Quantity
  • Price

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

  • OrderID (PK)
  • VariantID (FK - Variants(VariantID))
  • Quantity
  • OrderValue

Any suggestions on how best to handle product variants?

Thanks

Improve this question
4
  • Maybe store just a single Variant row for each of those Products. Then you don't need to think about the difference.
    – Charlieface
    Commented Jul 13, 2023 at 11:53
  • 2
    I don't agree with accepted answer, i think your solution works better, because with one variation = one product you still sometimes needs to display multiple colors or whatever on same page, and it becomes much more work to connect products together. For products without variants, you can create a dummy variant as Charlieface mentioned. I agree about both prices and stock though, it should be own table because you can have: multiple country prices and many warehouses, where one table breaks down quickly
    – siggemannen
    Commented Jul 13, 2023 at 20:10
  • The accepted answer is literally a relational database anti-pattern (as I commented under the answer). Regardless: this question is broad and opinion-based, as there are different ways to model such data.
    – David Makogon
    Commented Jul 14, 2023 at 12:14
  • Also: in case this is a homework assignment: you should probably spend a bit more time thinking through this, before turning in your assignment with the accepted-answer...
    – David Makogon
    Commented Jul 14, 2023 at 12:30

1 Answer 1

Reset to default
0

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.

Improve this answer
1
  • How is a "one table design" best? How would this be a best-fit in a relational database, where every row contains non-normalized data? That's a relational-database anti-pattern unless you're building a table for reporting purposes. In any case: the question itself is off-topic (broad and opinion-based )
    – David Makogon
    Commented Jul 14, 2023 at 12:13

Not the answer you're looking for? Browse other questions tagged or ask your own question.