mysql – Database Design Optimization
The point of the question seems to be to introduce multi-column primary keys, and the fact that a foreign key needs a unique key to reference (in this case
Orders (Rep_Num, Customer_num) references Represents (Rep_Num, Customer_num)):
Orders (Order_Num, Order_Date, Rep_Num, Customer_Num)
You could admittedly maintain a
Represents_Id and reference that instead in
Your solutions flaw is that
Orders (Represents_Num) doesnt necessarily reference a valid representative for that specific customer.
On a completely off-topic note, your solution is actually the correct one in practice: the sales who picks up the phone gets part or all of the commission, regardless of whether the customer is assigned to him or not.
As a takeaway from the exercise, think of the use-case as one of many, many edge cases where the theory and the specs quickly get shredded to pieces by realities on the field, and where being pragmatic and flexible in a DB design trounces sticking to the spec.