I have the following case, which I can only describe as a chain of two many-to-many relationships:
There are three business entities (tables) and two junction tables:
Ordershas a many-to-many relation with
Productshas a many-to-many relation with
Ideally, it means that the same Product may has different Features through one or more Orders.
For example, suppose we have Products of "Chocolate Cake" and "Cheese Cake", and within one Order we have:
- 2 Chocolate Cake: plain, small size and diet (so using 3 Features)
- 1 Chocolate Cake: decorated, medium size, Coconut added and candle (using 4 Features)
- 1 Chocolate Cake: plain, big size (2 Features)
- 3 Cheese Cake: cinnamon, banana (2 Features)
How could I store and maintain this relation, when the same Product may have one or more of its related Features within one Order?