0

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?

Oreo
  • 1,512
  • 9
  • 21
SaidbakR
  • 101
  • 2
  • 1
    What is your question about this setup? It's not clear at the moment. – Oreo Feb 15 '18 at 20:13
  • @Oreo I need to know the suitable database design that allows recording order's products with different features starting from three basic entities. – SaidbakR Feb 15 '18 at 20:28
  • Why not map Orders to feature_products then feature_products to the Products and the Features tables? – JasonBluefire Feb 15 '18 at 21:19
  • @JasonBluefire Do you mean that considering junction table as an entity that belongs to Order entity by adding order foreign key to it, then relate this entity in many to many relation with Features through new junction table? – SaidbakR Feb 16 '18 at 07:20

1 Answers1

1

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.

Oreo
  • 1,512
  • 9
  • 21