ER diagram - feasibility of a requirement

Question

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 '19 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 '19 at 00: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 '19 at 00: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 '19 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 '19 at 22:16

score 1 · Answer 1 · answered Nov 24 '19 at 11:18

Your foreign keys would be a Necklace graph. Every necklace in your design needs careful attention. Often you need to transform them into a tree. However, sometimes they are exactly what you want. Often because you need to enforce the root, which seems to be the case here.

That is, you have two ways to go from the detail of the shipment to the customer. The customer is the root of the necklace. And you need to ensure that in both paths you get to the same customer.

The solution, is to propagate the root. As you know, you are going to have the key to the customer in the shipment and in the order, add it to the their details too.

Now, remember that a unique key can have multiple fields, and that foreign keys can be to unique keys. Thus, you can to the order detail a unique key that include the primary of the order and the key of the customer. If the detail of the shipment is a separate entity, you can do the same there. Then use foreign keys to enforce the constraint (the combination of shipment and customer in the detail must match both the order and the shipment).

I'm taking this use of the term necklace from my brother's university graduation project, which analyzed cases of necklace graphs in database diagrams. This was in spanish, and I do not think it is available online. You can find prior art refering to them as "loops", however they rerely have the relationships going all in the same direction, which is why the term "necklace" was adopted. The beads of the necklace are the foreign keys (not the entities), and their "color" is the direction in which they are going.

score 0 · Answer 2 · answered Nov 24 '19 at 10:32

From the question I assume that the model looks essentially like this:

To my surprise I managed to create a database (MySql, see DDL below, and please verify!) that propagated the CustomerID via forein keys into the shipmentItem (attribute Customer_ID_Shipment). My surprise was that this attribute can be used in both the FK to OrderItem (where it is the CustomerID of the Order) as in the FK to Shipment (where it is the CustomeID of Shipment).

However I would not do it that way: I am sure that the IT-system where the problem arises has a reasonable architecture meaning that there will be one piece (or very few pieces) of logic responsible for the "SQL insert" and the "SQL update" (e.g. in OO exactly one class "shipment"). I would simply code the necessary constraint there – typically there are other business rules that are difficult to express and maintain in SQL and easy in "code".

Here is the DDL:

/* ---------------------------------------------------- */
/*  Generated by Enterprise Architect Version 12.1      */
/*  Created On : 24-Nov-2019 11:02:14               */
/*  DBMS       : MySql                      */
/* ---------------------------------------------------- */

SET FOREIGN_KEY_CHECKS=0 
;


/* Create Tables */

CREATE TABLE `Customer`
(
    `Name` CHAR(10)      NULL,
    `CustomerID` INTEGER NOT NULL,
    `Number` INTEGER NOT NULL,
    CONSTRAINT `PK_Customer` PRIMARY KEY (`CustomerID` ASC)
)

;

CREATE TABLE `Order`
(
    `OrderNumber` INTEGER NOT NULL,
    `OrderID` INTEGER NOT NULL,
    `CustomerID` INTEGER NOT NULL,
    CONSTRAINT `PK_Order` PRIMARY KEY (`CustomerID` ASC, `OrderNumber` ASC)
)

;

CREATE TABLE `OrderItem`
(
    `OrderNumber` INTEGER NOT NULL,
    `ProductID` INTEGER NOT NULL,
    `OrderItemNumber` INTEGER NOT NULL,
    `CustomerID` INTEGER NOT NULL,
    CONSTRAINT `PK_OrderItem` PRIMARY KEY (`CustomerID` ASC, `OrderNumber` ASC, `OrderItemNumber` ASC)
)

;

CREATE TABLE `Product`
(
    `Name` char      NULL,
    `ProductID` INTEGER NOT NULL,
    CONSTRAINT `PK_Product` PRIMARY KEY (`ProductID` ASC)
)

;

CREATE TABLE `Shipment`
(
    `ShipmentID` INTEGER NOT NULL,
    `CustomerID` INTEGER NOT NULL,
    CONSTRAINT `PK_Shipment` PRIMARY KEY (`ShipmentID` ASC, `CustomerID` ASC)
)

;

CREATE TABLE `ShipmentItem`
(
    `ShipmentID` INTEGER     NULL,
    `CustomerID_Shipment` INTEGER    NULL,
    `OrderNumber` INTEGER    NULL,
    `OrderItemNumber` INTEGER    NULL
)

;

/* Create Primary Keys, Indexes, Uniques, Checks */

ALTER TABLE `Order` 
 ADD INDEX `IXFK_Order_Customer` (`CustomerID` ASC)
;

ALTER TABLE `OrderItem` 
 ADD INDEX `IXFK_OrderItem_Order` (`CustomerID` ASC, `OrderNumber` ASC)
;

ALTER TABLE `ShipmentItem` 
 ADD INDEX `IXFK_ShipmentItem_OrderItem` (`CustomerID_Shipment` ASC, `OrderNumber` ASC, `OrderItemNumber` ASC)
;

/* Create Foreign Key Constraints */

ALTER TABLE `Order` 
 ADD CONSTRAINT `FK_Order_Customer`
    FOREIGN KEY (`CustomerID`) REFERENCES `Customer` (`CustomerID`) ON DELETE Restrict ON UPDATE Restrict
;

ALTER TABLE `OrderItem` 
 ADD CONSTRAINT `FK_OrderItem_Order`
    FOREIGN KEY (`CustomerID`, `OrderNumber`) REFERENCES `Order` (`CustomerID`,`OrderNumber`) ON DELETE Restrict ON UPDATE Restrict
;

ALTER TABLE `OrderItem` 
 ADD CONSTRAINT `FK_OrderItem_Product`
    FOREIGN KEY (`ProductID`) REFERENCES `Product` (`ProductID`) ON DELETE No Action ON UPDATE No Action
;

ALTER TABLE `Shipment` 
 ADD CONSTRAINT `FK_Shipment_Customer`
    FOREIGN KEY (`CustomerID`) REFERENCES `Customer` (`CustomerID`) ON DELETE No Action ON UPDATE No Action
;

ALTER TABLE `ShipmentItem` 
 ADD CONSTRAINT `FK_ShipmentItem_OrderItem`
    FOREIGN KEY (`CustomerID_Shipment`, `OrderNumber`, `OrderItemNumber`) REFERENCES `OrderItem` (`CustomerID`,`OrderNumber`,`OrderItemNumber`) ON DELETE Restrict ON UPDATE Restrict
;

ALTER TABLE `ShipmentItem` 
 ADD CONSTRAINT `FK_ShipmentItem_Shipment`
    FOREIGN KEY (`ShipmentID`, `CustomerID_Shipment`) REFERENCES `Shipment` (`ShipmentID`,`CustomerID`) ON DELETE Restrict ON UPDATE Restrict
;

SET FOREIGN_KEY_CHECKS=1 
;

here some data: insert into Customer values("Client33", 33, 3333); insert into Shipment values(22, 33); insert into Product values ("P66", 66); insert into customdb.Order values (4,4,33); insert into customdb.Order values (4,4,32); ## should fail insert into OrderItem values (4,66,1,33); insert into ShipmentItem values (22,33,4,1); — Übend, Nov 24 '19 at 10:34

Tulains Córdova · Answer 3 · 2020-08-20T19:04:30.870

Short Answer:

I would add a "before update" and a "before insert" trigger that rejects the insertion or update if the customer id of the shipment item is not the same as the id of the customer to whom the order belongs.

Long Answer:

It's not clear to me if your need is just to graphically represent such constraint in an ER diagram.

@Übend 's solution works, but it's complex to understand and explain, perhaps because of the fact that an order's uniqueness should be provided solely by the order id. Let me explain, if the unique identifier of the Orders table is (OrderID,CustomerID) it means that potentially there could be two orders with the same OrderID as long as the CustomerID is not the same. It doesn't sound quite right to me. CustomerID should be just a FK pointing to Orders.

Some business rules are not possible to satisfy solely by constraints and one have to resort to triggers, because we don't want to trust the app solely, right?

It comes to my minds requirements like not permitting credits that are beyond a client's limit, or guaranteeing that only one contract in a "many" table has a null end date for a given person.

This is not one of those un-modellable requirements, but for the sake of simplicity I would not propagate the CustomerID in every possible table, because it could potentially propagate to other tables that are not depicted yet creating compound FKs with 3, 4 or 5 columns.

Don't get me wrong, I like natural compound primary keys as long as they don't exceed 3 columns or provoke other tables to have 4 column foreign keys.

If by any change we add a historical table somewhere here we will have monstrous 4 column or 5 column composite PKs or PKs.

So I'd just add a constraint that rejects the anomaly we want to prevent by raising an exception (like in some RDBMS) causing the failure of the insertion or update.

ER diagram - feasibility of a requirement

3 Answers3

Short Answer:

Long Answer: