Re:Database development mistakes made by application developers
by alexeyklyukin on Monday December 13, 2010 @08:26AM
(#2000370)
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?
[ Parent
| on HN ]
Re:Database development mistakes made by application developers
by jasonlotito on Monday December 13, 2010 @10:39AM
(#2000736)
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.
Re:Database development mistakes made by application developers
by jfb on Monday December 13, 2010 @11:18AM
(#2000904)
ON UPDATE CASCADE. Tis a pity it's not more widely implemented.
Re:Database development mistakes made by application developers
by jasonlotito on Monday December 13, 2010 @01:05PM
(#2001366)
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.
Re:Database development mistakes made by application developers
by jfb on Monday December 13, 2010 @02:18PM
(#2001623)
Fair enough. I'm more worried about database consistency -- it's just the way I roll, I guess.
Re:Database development mistakes made by application developers
by jasonlotito on Monday December 13, 2010 @03:19PM
(#2001827)
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. =)
Re:Database development mistakes made by application developers
by Devilboy on Monday December 13, 2010 @11:14PM
(#2003097)
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.
Re:Database development mistakes made by application developers
by matwood on Monday December 13, 2010 @11:09AM
(#2000858)
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.
Re:Database development mistakes made by application developers
by ams6110 on Monday December 13, 2010 @09:48AM
(#2000588)
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.
Re:Database development mistakes made by application developers
by ora600 on Monday December 13, 2010 @04:39PM
(#2002073)
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...