Re: surrogate keys in Oracle

From: Sybrand Bakker <postbus_at_sybrandb.demon.nl>
Date: Sat, 23 Jun 2001 14:18:48 +0200
Message-ID: <tj925ekke9922e@beta-news.demon.nl>

"Guenter Werner" <guenter_werner_at_hp.com> wrote in message news:9h025v$34g$1_at_web1.cup.hp.com...
> Hi,
>
> I was looking for information or feedback from anyone with experience
 using
> surrogate keys (aka. system generated oid). There doesn't seem to be much
> information on this subject available and I was wondering what experiences
> (good or bad) you may have have run across using them. Any info. would be
> greatly appreciated.
>
> GW-
>
>

Just my 2 cts

Of course, it depends.
If you have a candidate primary key consisting of 1 column, I wouldn't use surrogate keys.
However, with a composite primary key?
I will briefly describe the database I am now coping with. In this particular system there is one main fact table with hospital visits. As this is a country wide system it contains 2 million rows per year (not necessarily 2 million people admitted to hospital). The primary key consists of

- a number for the hospital (mistakingly designed as varchar2)
- a number for the visit, assigned by the hospital (again a varchar2)
- the registration year, again a varchar2 and only the YY part of the year

of departure from the hospital either normally or by death.

All other tables for patient info are dependent of this one table. The primary key, all those 3 columns, is inherited by all tables. I will be recommending adding a surrogate key.

The second case in the same system is the case of time dependent data. The hospital assigns a number to local GPs sending their patients to hospital. However, the doctor may be succeeded by another one, in which case not necessarily a new number is assigned, but the name of the doctor changes from a certain moment in time.
The primary key consists of a hospital assigned number, a starting date and an ending date. While is it formally necessary to inherit these three columns in many tables, the designers didn't do that. A surrogate key assigned by the database would have resolved this problem.

Regards,

Sybrand Bakker, Oracle DBA Received on Sat Jun 23 2001 - 07:18:48 CDT