3

Illustration Of The DB Design

Basically the foreign key on options is there to make it so that if you want to know what product an option belongs to you don't have to go through variants.

A variant is like Color, Size Etc.. An option is like Medium, Large, Red Blue.

  • Is this a bad design choice?
  • Is it sometimes okay to do this?

The more I think about it, it seems like it's a bad idea just for the sheer fact that blue, when related to the product has no meaning without also being tied to the variant. This option loses any true meaning without the variant so the shortcut is never really worth taking?

1
  • It's frustrating when questions, have links to images which are no longer valid. It makes deciphering the solution more difficult.
    – WiseGuy
    Sep 3, 2020 at 16:21

2 Answers 2

2

You'll have your reasons for going this route, where it seems you're duplicating information (since id_Products is implied from id_Variants).

However, unless what you want to achieve is having Options with an optional product OR variant (i.e. a CHECK constraint ensuring one and only one of these IDs is not null), you must also ensure that you don't get into an invalid id_Variants + id_Products combination in Options in order to ensure you don't break referential integrity. For this, you need a UNIQUE key on Variants(Id, id_Products) and a FOREIGN KEY on Options(id_Variants, id_Products) REFERENCES Variants(Id, id_Products).

1

If you really want id_Products in Options for performance reasons (you do not need it there to ensure data integrity; indeed it confuses that argument) you can do so. Make the foreign key in Options a multi-column key. Make it point to the corresponding columns in Variants.

Even if Variants.Id is unique across all products there is no risk to the data by including is_Product in the primary key.

Usually this is a waste of time. id_Products will not be meaningful to a human so all queries will join to Products to get the semantic value. You can join Products to Options without having to have an explicit FK constraint in place.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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