mysql – Database Design Optimization

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)):

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 Orders.

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.

mysql – Database Design Optimization

Leave a Reply

Your email address will not be published.