Chained two Many-to-Many relations 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:


Orders
Products
Features
Orders has a many-to-many relation with Products through a order_products table
Products has a many-to-many relation with Features through a feature_products table


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?
Looks like you need a one-to-many between orders_products and Features.

Your orders_products will hold the first part of the order:


OrderItemID, OrderID, quantity, ProductID (e.g. "Item 1: 2 Chocolate Cakes")


and a new table (e.g. order_products_features) will hold the one-to-many relation needed for Features:


OrderItemID, FeatureID (e.g. "for Item 1, add "Plain")
OrderItemID, FeatureID (e.g. "for Item 1, add "Small Size")
OrderItemID, FeatureID (e.g. "for Item 1, add "Diet")


You will need some extra logic to check that users only enter valid Product-Feature combinations into this new table.

Designing it this way means that in the future, if you change the Product-Feature links, you will still have a historical record of what people ordered in the past, even though those Product-Feature links might not be valid today.