|
|
Posted by Steve Jorgensen on 10/12/05 11:24
On Tue, 11 Oct 2005 20:44:08 GMT, "Tom" <nospam@yahoo.com> wrote:
>Thanks for the actual terms for these key thingies.
>
>I've searched google but found a lot of different opinions.
>Some like autonumber but some like... huh... natural key.
>
>To sum it up from all the posts I've read:
>1)
>The people who use autonumber said that it performed better and easier to
>update the record without affecting the database integrity. (imagine
>someone needs to change his or her social security number!!!! all hell broke
>lose!!!)
>
>2)
>The people who use natural key said that people who use surrogate were just
>lazy and not experience with database design. And using natural key could
>better model database to the business model.
>
>So... I don't know... that's why I am so confused.
>One has physical advantage. The other has logical advantage.
Unfortunately, all any of us can do is add more opinions to the pile, which is
not likely to generate any final closure, just give you more to ponder. I'll
try to offer what I think is useful, though.
- A table may have one key or more than one "key". It's not so much a
quesiton of whether a table should have a logical or surrogate key as whether
it should have a surrogate key in addition to one or more logical keys, and
use the surrogate key for joins.
- When multiple levels of 1-to-many relationship exist, surrogate keys can
prevent adding additional key fields at each level and coupling fine levels of
detail too closely to the models at courser levels.
- When a table has a surrogate key that is always of the same type and
following the same naming convention, it's easier to generate reusable
client-side code that can do things like keep track of a record, requery it,
save updates after an off-line editing session, etc.
- Surrogate keys tend to be 32-bit numbers that are fairly small, and easy for
the database engine to process.
- Using cascading updates to handle logical key change propagation can result
in very expensive updates with wide-ranging run-time side effects.
- Using surrogate keys to identify record links across subsystems can lead to
excessively tight coupling and interdependency. If 2 subsystems should be
relatively autonomous, they should very likely -not- reference each other's
records by internal surrogate key. Instead, they might use the logical key,
or they might use some kind of globally unique identifier. They should also
be built to tolerate broken links.
Navigation:
[Reply to this message]
|