CODEWITHSUNDEEP
database-designerdentity-relationship
Ring
Ring

Reputation: 1

Relationships and cardinalities for an order-delivery system

Currently pilfering through a sample scenario that seems simple but I can't seem to figure it out

Basically, I'm building an entity-relationship diagram for an online clothing store to hold information about customers, orders, deliveries, and products. The entities I have are "customer", "order" (with a unique order number), and "product" as well as "delivery" (unique delivery ID).

From what I gather, customer has a 1:N relationship with order, order has a M:N relationship with product and will be resolved with an appropriate associative entity. However, I'm having some issues seeing how a "delivery" entity would fit. Assuming an order with multiple items can ship each item individually, would a 1:M relationship between order and delivery be appropriate/sufficient?

The diagram is basically [customer - places - order - has - product] and an extra branch off of order where [order - shipped - delivery]

Does this make sense? The scenario gives a generic order form and a receipt for delivery, and wants an ERD that stores info sufficiently to make these two kinds of documents. I feel as if I'm missing something, such as another unspecified entity or a relationship other than the three I have

Upvotes: 0

Views: 1189

Answers (1)

Amira Bedhiafi
Amira Bedhiafi

Reputation: 1

Let's imagine that client A ordered 2 products that need to be delivered to his home address :

 OrderID SalesPersonID ClientID   ProductID Quantity UnitPrice TotalPrice ShippingAddress Status
    101     100           10         1         2        100       260        Gotham city     Shipped
    101     100           10         2         1        30        260        Gotham city     Shipped

Your entities are :

  • Order : OrderID, ClientID, ProductID, Quantity, TotalPrice, Status

  • Client : ClientID, FullName, Address, Phone, Email

  • Product : ProductID, ProductName, ProductReference

  • SalesPerson : SalesPersonID, FullName..

Upvotes: 0

Related Questions