Re: Surrogate Keys As Part Of Composite Keys?
Date: 2000/04/28
Message-ID: <39090088.750268FB_at_enternet.com.au>#1/1
Randy Yates wrote:
> <snip>
>
> You haven't answered my question. My question is: why *not* do it
> with a surrogate key? I've known for a week and agree that the method
> above is the proper way to model the table, but I'm missing the theory
> and corresponding insight on *why* I need to do it this way.
> --
> Randy Yates
> DSP Engineer
> Ericsson / Research Triangle Park, NC, USA
> qusraya_at_rtp.ericsson.se, 919-472-1124
I've used both methods in the past. I found it to be useful to inherit PK columns from identifying relationships for a couple of reasons: One is that you are able to simplify join statements in the eventual SQL which accesses the database. Imagine that you have five 'levels' of inheritance (if I may use an OO term here..). If you use strictly surrogate keys for each table in the hierarchy, then to join the records in the 'lowest' table with thier ancestor(s) in the 'highest' table you will have to include all of the intermediate tables in the join condition, since the FK reference to the ancestor is not propogated to the descendant table. In the case that PK columns are inherited you can join the two tables directly. Second is that if you don't inherit the PK columns, you will be unable to enforce some business rules by using shared FK columns. These sort of rules are along the lines of 'the person who placed the order must be the same as the person who recieves delivery'. Basically they can occur where two hierachies of inheritance having a common ancestor re-merge in a dependant table.
Having said that, I prefer to use surrogate keys if possible where I am supporting a reasonably sophisticated OO front end. The questions I ask before doing so are basically related to the systems capacity to create, allocate and manage values for the surrogate key columns. It is often not efficient in larger systems to rely on internal DBMS constructs like sequences (Oracle), since there will be a requirement to access the database multiple times to save a reasonably complex object in order to provide the parent's PK value to the referencing child records. You can get around this by using stored procedures, or by having one of your middle tiers in the application responsible for the generating and allocating surrogate keys. The advantage of using numeric surrogate keys in an OO environment is that the identifiers for all entities are union compatible and simple to use. If I know which object I want all I have to provide is a type (eg 'person') and number to retrieve it from the database.
I have used a composite approach before, which doesn't seem to have any significant downside. I inherit the PK columns, however all tables of any signifigance have a surrogate key column which is seperately unique. I always declare this column to be an alternate key in my model. So in a three tier inheritance hierarchy, my tables would have PK of (id_grandparent), (id_grandparent,id_parent), (id_grandparent,id_parent,id_child) where in each case the last column is also an alternate key (unique) for the table. In the OO front end the developer can simply use the AK column to reference the record. However when I build the SQL or stored procedures to join tables, I can skip redundant intermediate tables if I want. I am also able to implement any business rule relating to inheritance as discussed above.
Hope this is useful, David. Received on Fri Apr 28 2000 - 00:00:00 CEST