I need to model the following requirement in an Entity-Relationship diagram.

"Customers place orders, which contain a list of products with their quantity and prices. A shipment is made when products are ready to be delivered to a customer. A shipment may include products from multiple orders, but only if they all belong to the same customer."

The last sentence is the one I'm not sure about. I could have a Shipment entity in relationships with Product, Order and/or Customer and the cardinalities are easy to work out. But how can I express the fact that a Shipment concerns one and only one Customer, even though it may contain multiple orders? I couldn't find a way. Is it impossible?

  • What about a many to one relationship between Shipments and Customer? – Robert Harvey Mar 15 at 20:06
  • Maybe, but the association between Shipment and Order won't prevent a shipment to contain many orders by different customers. I don't think a ternary association will do either. – holy_el Mar 16 at 0:32
  • Instead of relating the orders/products to the shipment directly, couldn't the customer be associated to the orders/products, and a shipment is only related to a customer? The shipment would only be able get to the orders/products based on customer alone. – eparham7861 Mar 17 at 0:22
  • @eparham7861 Thank you for the proposition. The thing with that is I suppose I need to know which orders are part of a given shipment. If a customer has, say, 10 orders, they might be delivered in 3 shipments. – holy_el Mar 18 at 14:48
  • OK, all that changes is that shipment is related to a list/collection of customer, and orders where there is only one shipment to one customer for many orders. With where this seems to be going, it may benefit you to start from all of the data together in a table, for instance an invoice, and work back splitting the data towsrds the sets you are needing. – eparham7861 Mar 18 at 22:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.