Re: Normal vs Surrogate Primary Keys...

On Sun, Oct 01, 2006 at 07:48:14PM -0700, rlee0001 wrote:
> <snip> For example, if I key "employee" by Last Name, First Name, Date
> of Hire and Department, I would need to store copies of all this data
> in any entity that relates to an employee (e.g. payroll, benefits and
> so on). In addition, if any of these fields change in value, that
> update would need to cascade to any related entities, which might be
> perceived as a performance issue if there are many related records.

Err, those fields don't make a natural key since they have no guarentee
of uniqueness. You've simply decided that the chance of collision is
low enough that you don't care, but for me that's not really good
enough for use as a key.

Secondly, three of the four fields you suggest are subject to change,
so that indeed makes them a bad choice. My definition of "key" includes
"unchanged for the lifetime of the tuple".

In that situation your idea may work well, but that's just a surrogate
key in disguise...

Have a nice day,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.