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

I take it from this that a given product can be assigned to multiple clients, and that a Client can be assigned multiple products?

> 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

This sort of seems like overkill, since the ORDERS.orderID column is already flagged as the primary key, and therefore unique index. Putting a unique index then on the combination of ORDERS.orderID AND ORDERS.clientID will not achieve anything.

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

It is my understanding that the Primary Key for an entity designates the unique index that is most likely to be used as the locator for that record. For example:

PRODUCTS
  productID
  productCode

The productCode field may be unique to a product, but it might be a varchar(30) data type. It wouldn't be necessarily efficient to use this field as the identifier for a product internally within the database. However, the user may have absolutely no idea what the productID field is, but only knows about the productCode.

The other option that you could use is to place non-index based constraints on the tables, so that when an ORDER_PRODUCTS entry is created, it checks that the productID entered has been assigned to the client. However, I am unfamiliar with SQL syntax for this, so am unable to give you an example.

--