I need to store products in two forms, simple and variable.

Simple products have a name, desc and price.

Variable products have a name and description and each variation will have additional fields, variationName, price.

This is a sample schema:

enter image description here

So a product will contain its name and description in the products table and the price will go in the variation table.

Here's an example of a simple product:

Name, description, variationName, variationPrice

Product X, sample description, NULL, 3.99

And a variable product:

Name, description, variationName, variationPrice

T-Shirt, T-shirt description, Small, 2.99

The variations for T-Shirt would be things like Medium, Large etc each with its own price but same parent (T-Shirt).

The problem with this design I am having is keeping the price for simple products where a variation name isn't needed. How to store this without redundancy?

  • It's not entirely clear if your product table contains a price or not. Anyway, you seem to be looking for a join. specifically a left join in your case. Since you seem to be working with sql for at least 4 years, I am not sure if that is your actual problem though, so you may need to be more specific. Also, generally, if you plan to write your own webshop (except for learning purposes): don't! There are plenty of ready-to-use solutions out there (and they might be interesting for learning purposes too). – Solarflare Oct 6 '17 at 6:16
  • updated question – Abu Nooh Oct 15 '17 at 18:28
  • The "correct" solution will depend on what you consider/define to be a product. You also currently do not have a redundancy problem, as you store the "simple" price only once (you could think of a simple product as its first variant). You could also add prices in both tables, and if the variant price is given, use this, if it's null, use the main price - this will e.g. prevent redundancy in cases where variants in different colors might have all the same price, but gives you the option to define differing prices for different sizes. Also, I repeat my suggestion: look at literally any – Solarflare Oct 15 '17 at 19:37
  • webshop. They have lots of documentation about how you can define/manage/model prices - and there are other aspects you could include, e.g. price changes on some date or different prices for special groups (like resellers or regular customers), so reading the documentation might inspire you to define it clearly. As the main question/task is for you to decide what a product, a price and a variant is exactly (e.g. if a "simple product" should be regarded as a variant, allowing you to simply add more variants; or if maybe a variant is an actual product (so no extra table, but a self-reference)). – Solarflare Oct 15 '17 at 19:37
  • The redundancy isn't in the price column, it's in the naming. Not all products will be the variants of a simple product. I'm in the school of thought that NULL should be avoided if we're sticking true to normalisation principles. Thanks for your suggestions though. – Abu Nooh Oct 15 '17 at 20:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.