Question:
:
I have a hospital database where the patient ID does not
immediately exist when a patient first calls for an appointment. I
have a person table and an appointment tables, and each appointment
must be tied to a person. How to I create a good candidate key
until I can generate the correct primary key for the row?
Answer: As you know,
referential integrity (RI) is used to enforce entity
relationships, such as that between parent and child tables.
For example, a customer table may
place many orders, but each order only belongs to one customer.
You want a
candidate key, a unique secondary key which can be used as a
surrogate to the primary key . . .
When the primary key may not be available immediately, you can use a
surrogate key to enforce the business relationship.
Dr. Ralph Kimball makes a compelling argument for using surrogate
keys, primarily to improve database performance:
?Replacing big, ugly natural keys and composite keys with
beautiful, tight integer surrogate keys is bound to improve join
performance.
The storage requirements are reduced, and the
index lookups would seem to be simpler.
I would be
interested in hearing from anyone who has harvested a performance
boost by replacing big ugly fat keys with anonymous integer keys.?
