Re: Surrogate primary key plus unique constraint vs. natural primary key: data integrity?

Am Donnerstag, 29. August 2013 00:20:44 UTC+2 schrieb com..._at_hotmail.com:

> > clients {client}
>
> > client_orders {client, order}
>
> > client_locations {client, location}
>
> > deliveries {delivery_no, client, order, location}
>
> >
>
> > Now add surrogate keys to all relations, and replace the last one with:
>
> > deliveries {delivery_no, order_id, location_id}
>
> >
>
> > You may see that client attribute is no longer needed since it can be determined from either order_id or location_id. However this design no longer enforce the rule that the order and the location should refer to the same client.
>
Philip is right, my notion of notion of referential integrity is very limited. Based on a design in one of our applications (repair_orders) I would change his design in this way

clients {client, client_id}
client_orders {client_id, order_no, location} client_locations {client_id, location}
deliveries {delivery_no, order_no}

As long as clients can have several locations, I see the need to secure the address of the order ("who has issued the order"). Referencing clients with natural keys vioalates referential integrity when companies change names. One of our customer recently changed from BHR to EHR. Client location can change too (new factories) so the actual location has to be copied to orders. Deliveries and Orders need no surrogate keys, since finished, commited business transactions never change.

m.f.G.

Karl Scheurer Received on Sat Aug 31 2013 - 16:10:09 CEST