Parent -> Child -> Grandchild with a foreign key back to Parent, Bad Practice?

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?

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).

StackExchange Database Administrators Q#135229, answer score: 2