Question: I am designing a database and I
need to understand the relative advantages of using natural primary/foreign keys
vs using surrogate leys (Oracle sequences). Can you list the costs
benefits of the natural key vs a surrogate key in an Oracle table.
Answer: First, tp review, a
"natural" key is a key that already exists within a table
(such as the Social Security Nunber of a person, while s
surrogate key has no business meaning and is a unique number
generated by an Oracle "sequence". The choice if a
natural vs a surrogate key is straightforward and it depends
on these characteristics of the natural key:
Size of natural key: A
surrogate key is a replication, and while small, it takes
disk space. A generated key (an Oracle sequence) is an
integer number and takes very little disk space to replicate
as a foreign key.
Volatility of natural key:
If the natural key changes frequently, the
resulting changes to foreign keys cause changes to index
orders and row relocation. An excellent example of a
natural key is a Stock-Keeping Unit (a SKU) that is
generally small, static and unique.
Uniqueness of natural key:
To be effective and keep the database flexible, a primary
key should be truly unique. In most vases, even key
values such as social security number and problematic
(because foreigners do not have an SSN)
In sum, natural keys may be appropriate then you have small,
rarely changing, unique values (such as lookup tables),
while surrogate keys are desirable for large, highly
changing data key columns with duplicate key values (like
"people" tables).
 |
|
Get the Complete
Oracle SQL Tuning Information
The landmark book
"Advanced Oracle
SQL Tuning The Definitive Reference" is
filled with valuable information on Oracle SQL Tuning.
This book includes scripts and tools to hypercharge Oracle 11g
performance and you can
buy it
for 30% off directly from the publisher.
|
