Re: Using FKs to non PK unique-indexes

before.the.gods_at_gmail.com wrote in news:1139503181.879478.24680 _at_g14g2000cwa.googlegroups.com:

> Option 2 has more sens ... but I dont like it for 2 reasons. First,
> having the ORDERS table PK on both clientID and orderID has no logic.
> It gives the impression (and actually allows the situation) that a
> single orderID can be repeated for many clients. Second, it creates a
> situation where all subsequent relationship entities that "inherit"
> from ORDERS will be stuck with the clientID column in their PK.
>

This is the part of your argument that I worry about. The column ORDERPRODUCTS. CLIENT_ID that you want to put in does not refer to the client that owns the order, but to the client with whom the client-product deal was made. Your current business rules may say that those two clients have to be the same, but it is feasible that a future manager will want the ability to say, "let ACME-UK have the widgets at same price as ACME-  I am not suggesting that the designer's job is to double-guess marketing managers' future mood swings, but I do suggest that this is an application-level decision rather than an integrity constraint.

I also think that it's a mistake of rigid column-naming rules like, for example, a FK column must have the same name as the PK it targets... For my money the name of a FK should capture the semantics of the relationship within that that table. In this case, you might think of ORDER-PRODUCTS.PriceSetForClient and ORDER-PRODUCTS.PriceSetForProduct or similar.

Just a thought...

Tim F Received on Tue Feb 14 2006 - 20:49:26 CET