Re: Using FKs to non PK unique-indexes

Since orderID implies clientID, adding clientID to ORDERS_PRODUCTS would introduce redundancy; however, since SQL Server doesn't support referencing a view in a foreign key constraint, it may be the only way to use declarative means to enforce the constraint. In this case, you would need a foreign key constraint from ORDERS_PRODUCTS to ORDERS on orderID and a foreign key constraint from ORDERS_PRODUCTS to CLIENTS_PRODUCTS on both clientID and productID. A foreign key constraint to CLIENTS or PRODUCTS would be redundant. On the other hand, you could forego the redundant clientID by using a trigger to look it up in ORDERS to verify the existence of a CLIENT_PRODUCTS row.

Redundancy cedes a database's constraint enforcement responsibility to the application, so it should be eliminated whenever possible. The additional clientID solution above requires that the database engine support cascading updates. Cascading updates are not part of the Relational Model--in fact, they're not relational at all, relying on the database engine's ability to correlate old and new values for individual rows. They are a kludge that addresses the redundancy inherent in having natural foreign keys, and they place limitations on the types of modifications that can be applied to a database.

<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 Fri Feb 10 2006 - 05:41:43 CET