4 ms·

A suggestion to use surrogate keys instead of natural ones doesn't seem right to me. IMO proper choice of natural keys leads to better mapping from a knowledge

A suggestion to use surrogate keys instead of natural ones doesn't seem right to me. IMO proper choice of natural keys leads to better mapping from a knowledge domain into the corresponding relational model; if you are unable to find natural keys maybe there's something wrong with your database schema?

ams6110 16y ago
A professor of mine (this was YEARS ago) maintained that if each table did not have a natural key then the model was wrong. In theory this may be correct, but the use of surrogate keys avoids a lot of problems in practice.
jasonlotito 16y ago
Not being able to find a natural key isn't why you use a surrogate. You use a surrogate because natural keys are not static: they can change. Surrogate allows you to have a key that is unrelated to the data, data that will change. Basing keys on changing data is risky. I've experienced this myself, and while natural keys do work in theory, in practice, they are prone to failure.
- jfb 16y ago
  ON UPDATE CASCADE. Tis a pity it's not more widely implemented.
  - jasonlotito 16y ago
    Which does nothing for anything utilizing the resource itself outside the database. I'm not referring at all to keeping the database consistent. Having something you can always refer to to grab the same data is invaluable.
    - jfb 16y ago
      Fair enough. I'm more worried about database consistency -- it's just the way I roll, I guess.
      - jasonlotito 16y ago
        I'm all for database consistency. It's just not only database consistency that I'm worried about. I've just been burned by keys that were also data before. Having keys that aren't related at all to the data just hurts a few sensibilities and a few individuals sense of calm. =)
  - Devilboy 16y ago
    There are limits to even this. For example, you may have a table referencing itself (e.g. for tree structures) and on most systems you can't cascade updates. Also if your key is used in lots of other tables the cascade will become expensive.
matwood 16y ago
No matter how correct you think your schema is today something will always come up. Surrogate keys also give you a way to always have a single column key to use in joins and foreign key constraints.
ora600 16y ago
The discussion in the comments of the following blog post make some pretty good points in favor of surrogate keys: http://prodlife.wordpress.com/2007/09/05/meaningless-foreign-keys/ http://prodlife.wordpress.com/2007/09/05/meaningless-foreign...

ams6110 16y ago

A professor of mine (this was YEARS ago) maintained that if each table did not have a natural key then the model was wrong. In theory this may be correct, but the use of surrogate keys avoids a lot of problems in practice.

jasonlotito 16y ago

Not being able to find a natural key isn't why you use a surrogate. You use a surrogate because natural keys are not static: they can change. Surrogate allows you to have a key that is unrelated to the data, data that will change. Basing keys on changing data is risky. I've experienced this myself, and while natural keys do work in theory, in practice, they are prone to failure.

jfb 16y ago
ON UPDATE CASCADE. Tis a pity it's not more widely implemented.
- jasonlotito 16y ago
  Which does nothing for anything utilizing the resource itself outside the database. I'm not referring at all to keeping the database consistent. Having something you can always refer to to grab the same data is invaluable.
  - jfb 16y ago
    Fair enough. I'm more worried about database consistency -- it's just the way I roll, I guess.
    - jasonlotito 16y ago
      I'm all for database consistency. It's just not only database consistency that I'm worried about. I've just been burned by keys that were also data before. Having keys that aren't related at all to the data just hurts a few sensibilities and a few individuals sense of calm. =)
- Devilboy 16y ago
  There are limits to even this. For example, you may have a table referencing itself (e.g. for tree structures) and on most systems you can't cascade updates. Also if your key is used in lots of other tables the cascade will become expensive.

matwood 16y ago

No matter how correct you think your schema is today something will always come up. Surrogate keys also give you a way to always have a single column key to use in joins and foreign key constraints.

ora600 16y ago

The discussion in the comments of the following blog post make some pretty good points in favor of surrogate keys: http://prodlife.wordpress.com/2007/09/05/meaningless-foreign-keys/ http://prodlife.wordpress.com/2007/09/05/meaningless-foreign...