Q2.7: What's a natural key?
Let me think back to my database class... okay, I can't think that
far so I'll paraphrase... essentially, a natural key is a key for
a given table that uniquely identifies the row. It's natural in
the sense that it follows the business or real world need.
For example, assume that social security numbers are unique (I
believe it is strived to be unique but it's not always the case),
then if you had the following employee table:
<![CDATA[
employee:
ssn char(09)
f_name char(20)
l_name char(20)
title char(03)
]]>
Then a natural key would be ssn. If the combination of
_name and l_name were unique at this company, then
another natural key would be f_name, l_name. As a matter
of fact, you can have many natural keys in a given table but
in practice what one does is build a surrogate (or artificial) key.
The surrogate key is guaranteed to be unique because (wait, get back,
here it goes again) it's typically a monotonically increasing value.
Okay, my mathematician wife would be proud of me... really all it
means is that the key is increasing linearly: i+1
The reason one uses a surrogate key is because your joins will be
faster.
If we extended our employee table to have a surrogate key:
<![CDATA[
employee:
id identity
ssn char(09)
f_name char(20)
l_name char(20)
title char(03)
]]>
Then instead of doing the following:
<![CDATA[
where a.f_name = b.f_name
and a.l_name = a.l_name
]]>
we'd do this:
<![CDATA[
where a.id = b.id
]]>
We can build indexes on these keys and since Sybase's atomic storage
unit is 2K, we can stash more values per 2K page with smaller indexes
thus giving us better performance (imagine the key being 40 bytes
versus being say 4 bytes... how many 40 byte values can you stash in
a 2K page versus a 4 byte value? -- and how much wood could a wood
chuck chuck, if a wood chuck could chuck wood?)
Does it have anything to do with natural joins?
Um, not really... from "A Guide to Sybase..", McGovern and Date,
p. 112:
The equi-join by definition must produce a result containing
two identical columns. If one of those two columns is eliminated,
what is left is called the natural join.