Re: Using FKs to non PK unique-indexes

before.the.gods_at_gmail.com wrote:
> Hello
>
> 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 : [...]
>
> 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)

In theory nothing, but in practice there might be some difference in whether the index is a clustered one or not, for example. Unlikely to be a big concern here.

> 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?

Yes. In fact, the theoretical model makes no distinction between PKs and  other (candidate) keys.

> 3- Is my model normalized?

No, there is redundancy because the ORDERS table is a projection of the ORDERS_PRODUCTS table. But I would say that in this case this is more than acceptable here because (1) the redundancy is controlled by integrity constraints that *can* be maintained by the DBMS so there is little risc of update anomalies and (2) the fully normalized schema requires an integrity constraint that cannot be expressed by just unique key and foreign key constraints and which can therefore often *not* be mantained by the DBMS and therefore *could* lead to update anomalies. Since normalization is usually only a means to a higher goal, namely preventing update anomalies, this leads to the conclusion that full normalization would actually be counter-productive here.

Hope that helped.

• Jan Hidders