Re: Using FKs to non PK unique-indexes

The following schema eliminates the redundant clientID from ORDERS_PRODUCTS while still enabling the database engine to enforce the constraints declaratively:

CLIENTS
clientID PK

PRODUCTS
productID PK

CLIENTS_PRODUCTS
clientproductID PK
clientID AK and FK to CLIENTS.clientID
productID AK and FK to PRODUCTS.productID

ORDERS
orderID PK
clientID FK to CLIENTS.clientID

ORDERS_PRODUCTS
orderproductID PK
orderID AK and FK to ORDERS.orderID
clientproductID AK and FK to CLIENTS_PRODUCTS.clientproductID

Note: the alternate keys in CLIENTS_PRODUCTS and ORDERS_PRODUCTS are composite keys and should be enforced with a unique constraint.

<before.the.gods_at_gmail.com> wrote in message news:1139503181.879478.24680_at_g14g2000cwa.googlegroups.com...
> 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 :
>
> 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 Sat Feb 11 2006 - 08:47:12 CET