On Wed, Jan 18, 2006 at 01:08:53PM +0000, Leandro Guimarães Faria Corcete DUTRA wrote: > Jim C. Nasby <jnasby <at> pervasive.com> writes: > > Generally, I just use surrogate keys for everything unless performance > > dictates something else. > > What I am proposing is the reverse: use natural keys for everything unless > performance dictates something else. > > In support of my PoV: > http://blogs.ittoolbox.com/database/soup/archives/007327.asp?rss=1
Interesting. However, in my experience very few things have "natural keys". There are no combination of attributes for people, phone calls or even real events that make useful natural keys. You don't say what the primary key on your events table was but I can see one possibility: (place,datetime) A unique on this won't prevent overlapping events. Sure, it'll get rid of the obvious duplicates but won't solve the problem. It also fails the criteria that keys stable, since you can move events. You do need a constraint on that table, but a unique constraint isn't it. While I agree with your statement that it's the abuse of these keys thats the problem, I find people are far too likely to see natural keys where none exist. BTW, the way I deal with people mixing up surrogate keys is by (usually by chance) having the sequences for different tables start at wildly different points. By starting one counter at a million and the other at one, the chances that you'll be able to mix them up is reduced. On some systems I can even identify the table a key comes from by looking at the number, just because I know only one table has keys in the 30,000 range. Have a nice day, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > tool for doing 5% of the work and then sitting around waiting for someone > else to do the other 95% so you can sue them.
signature.asc
Description: Digital signature
