Re: Propagated composite keys or one surrogate key?

adizon_at_mail.us.net wrote:
>
> In normalizing tables, the primary key of a parent table is usually
> propagated to the child table. In a multi-level parent-child arrangement,
> the lowest-level child table could have a composite primary key
> consisting of several columns. What are the advantages and disadvantages
> of substituting a single column as the surrogate primary key in place of
> the inherited composite key?
>
> Thanks,
> Lito
>
> Lito Dizon
> adizon_at_us.netI assume here we are talking only about identifying relationships
(primary key of parent migrates to primary key of child). The advantage in using a meaningless surrogate key and moving the migrated attributes out of the primary key is that on subsequent relations from the child those attributes do not migrate and thus result in smaller tables/keys in the child's children. Doing this for a table that does not have any children does not provide that benefit.

The downsides include additional index on the table with the surrogate key. The surrogate key will be the primary but in order to maintain your integrity you will need to build a unique alternate key comprised of all of the columns that surrogate replaces. The other downside is that in order to join an earlier generation table with children of the table with the surrogate key, you will have to include the surrogate key table in the join.

When you get to tables with a lot of records, I try to get the primary key to be as small as possible to keep the data to index ratio up so I would tend to use a surrogate key to reduce the number of migrating columns before I get to big detail tables.

Hope it helps. Received on Wed Sep 11 1996 - 00:00:00 CEST