I have a table "products" and a child one "product_modifications"

Some products have "product_modifications": "colour", "size",, .... But not all.

When a client orders a product, views pictures and other things, he might do that with either product or its modifications, depending on whether or not a particular product is a pure one or a product with modifications.

Question: how do make it more effortless and flexible?

Should I keep reffering to either "products" or "product_modifications" when creating orders, calculating prices, and so on.... depending on whether or not a product has modification?

Or should I always create an empty/default "product_modifications" for each product and work only with "product_modifications"?

  • 1
    A couple questions that comes to mind, why isn't the table with "Product Modifications" not a lookup table, instead of a child, with existing products and their possible mods instead, where products without modifications are associated to some "N/A" option? That way the concept of ordering a product is based on both tables and not one or the other. Also, What does the thing that holds the orders (table or whatever you are using) look like? Some context of the destination/final result will help in determining what the need is for your issue. Mar 3 '19 at 21:35

In both scenarios, if the tables are well normalized, you likely will need to join between the two tables - most queries that require modification information would also require information from the product table (product name).

If I understand the problem correctly, I think you can have the best of both of your two options by having a single query which joins with an outer join between the two tables, while not having to worry about inserting dummy rows.

You can leverage the outer join to make sure a row comes back from the query even if there's no modifications - you don't need to create a dummy row in the product_modifications table to get a row back in the result. A left outer join will allow you to return a row from the product table even if there is no corresponding row in the product_modifications table

  • I know. My question is about which approach choose
    – bibat
    Mar 6 '19 at 4:05
  • I was trying to suggest a third option which splits the difference - I reworded to hopefully make it more clear.
    – Egret
    Mar 9 '19 at 0:23

Your Answer

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

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