Re: Surrogate keys (Was: enums)

On Thu, Jan 19, 2006 at 02:01:14PM -0500, mark(at)mark(dot)mielke(dot)cc wrote:
> On Thu, Jan 19, 2006 at 10:11:51AM -0800, Josh Berkus wrote:
> > > So ISTM it's much easier to just use surrogate keys and be
> > >done with it. Only deviate when you have a good reason to do so.
> > "The lazy man's guide to SQL database design", but Jim Nasby.
> > ;-)
>
> Hehe... I was thinking the same thing. I've definately seen cases
> where the use of surrogate keys verges on ridiculous. It hasn't
> harmed the application, except it terms of complexity. It still works.
> It still performs fine. The SQL queries are awful looking. :-)

Got an example?

> That's where I would tend to draw the line. For me, I find
> implementation and maintenance to be the most expensive part of my
> applications. My data hasn't yet become large enough to make disk
> space, compute resources, or I/O bandwidth a serious concern.

Which is exactly what my thought process is. If you mix surrogate and
non-surrogate keys, how do you know which table has which? Sure, while
you're actively writing the code it's not an issue, but what about 6
months later? What about if someone else picks up the code?

I know Josh was poking fun with his comment about me being lazy, but
lazy can make for better code. I can go back to code I wrote 3 years ago
and I know that 99% of tables will have something_id (where something is
almost certain to be the table name) as a surrogate key to join on;
there's no need for me to go and figure out what does and what doesn't
have a surrogate key. The 1% that don't fall into that generally aren't
an issue because they're normally very large tables that nothing joins
to.

There's actually an article floating around somewhere about how lazy
coders are good coders... :)
--
Jim C. Nasby, Sr. Engineering Consultant jnasby(at)pervasive(dot)com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461