Using FKs to non PK unique-indexes

Recently, I decided to use the MSSQL feature that allows an FK to point to a unique-index that is *not* a PK. I must say that this lead me to several - not to say existential - questions about database modeling and theory. I am basically looking for your opinions on the subject.

Before discussing the case, here is a simplification of my design (tables in capitals) :

PRODUCTS
productID PK

CLIENTS
clientID PK

CLIENTS_PRODUCTS
clientID PK and FK on CLIENTS.clientID
productID PK and FK on PRODUCTS.productID

ORDERS
orderID PK
clientID FK on CLIENTS.clientID

Everything's normal until here. The CLIENTS_PRODUCTS table (in case you ask) holds prices and similar stuff because the company (a) does not sell all products to the same price and (b) does not sell all products to all clients.

The only table we still need to introduce is the one that will hold the order's products :

ORDERS_PRODUCTS
orderID PK
productID PK

OK for the PK ... but how do I set the FKs on that entity? The main integrity problem here is to make sure that the products included in an order belong to the client that placed the order.

So lets do it that way :

ORDERS_PRODUCTS
orderID PK
productID PK | FK on CLIENTS_PRODUCTS.productID clientID | CLIENTS_PRODUCTS.clientID

Perfect ... but how do I set the FK between ORDERS and ORDERS_PRODUCTS?  One might say I have 2 options :

1- I put the FK between ORDERS_PRODUCTS.orderID and ORDERS.orderID. 2. I extend the ORDERS PK table to the clientID column and I put the FK to ORDERS_PRODUCTS.orderID and ORDERS_PRODUCTS.clientID

Option 1 is absolutely out of question since it would create a very weak integrity model (allowing an order to have products that are not part of the order's client profile).

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.

I then came up with a third option :

3- I put a unique-index on ORDERS.orderID and ORDERS.clientID and point the FK on that index

Everything is perfect, no? Well ... this practice is new for me ... and I am asking myself several questions :

1- if both PKs and a unique-indexes can be pointed by FKs, what is the difference between a PK and a UI? (beside the little yellow key sign that appears beside PK columns in diagrams)

2- Having unique-indexes on an entity gives the impression that there is more than one PK on an entity ... is this conform to database theory?

3- Is my model normalized?

Thank for your comments.

BTG Received on Thu Feb 09 2006 - 17:39:41 CET