Re: Using FKs to non PK unique-indexes
Date: Tue, 14 Feb 2006 16:35:27 GMT
Message-ID: <jFnIf.81$H%4.49_at_pd7tw2no>
vldm10 wrote:
> 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.
>
>
> In my opinion you made mistake with the table ORDERS_PRODUCTS in your
> solution. The data from this table are derived. You can get
> ORDERS_PRODUCTS's data from the tables CLIENT_PRODUCTS and ORDERS
> (maybe with small improvements), i.e. you don't need ORDERS_PRODUCTS
> as a table. It seems that ORDER-PRODUCTS is source of the troubles.
>
> Vladimir Odrljin
>
When the OP gave the definitions he called them a "simplification". I took that to mean that he had just included the 'key' and 'foreign key' attributes and that ORDERS_PRODUCTS would in practice have other attributes, for example, such as QUANTITY.
I puzzled over the example and concluded that it shows an interesting little gap in the RM approach, let alone mssql, in that attributes that are removed by applying normalization could cripple the use of another basic idea, ie., foreign keys. Another use of views, eg. having a join view that shows CLIENT_ID as well as PRODUCT_ID so that a foreign key reference is possible (if any products allow such a thing) might disguise the problem but I wish there were a simpler way.
Of course there might be a simpler design that I can't see. If there isn't one and given that ORDER_ID is mentioned in ORDERS_PRODUCTS, I wondered why there isn't a basic RM notion that could infer the CLIENT_ID from the FD ORDER_ID -> CLIENT_ID in a way that would let CLIENT_ID participate without it being an attribute of ORDERS_PRODUCTS!
The only way I know to make the inference is with a join but as I said, that seems a more elaborate work-around than should be necessary.
p Received on Tue Feb 14 2006 - 17:35:27 CET