Re: Natural keys vs Aritficial Keys

From: Bob Badour <bbadour_at_pei.sympatico.ca>
Date: Thu, 21 May 2009 11:08:26 -0300
Message-ID: <4a15602d$0$23750$9a566e8b_at_news.aliant.net>


Michael Schuerig wrote:

> Jonathan Leffler wrote:
>

>>Walter Mitty wrote:
>>
>>>I'm confused about some recent trends in database design.
>>>
>>>When I learned databases, about a quarter of a century ago,  the
>>>preferred practice was to use natural keys to refer to individual
>>>table rows, unless
>>>there's some real good reason to go with an artificial key.  I've run
>>>into a few cases where I chose to use an artificial key, but most of
>>>the time I've
>>>used a natural key.  [...]
>>
>>Somebody mentioned in an answer on StackOverflow that (Ruby on) Rails
>>requires all tables to contain a column called 'id' (and, by
>>implication, that column should be an 'identity' column or
>>auto-generated value).  (I've not verified that this is so.)

>
> Yes, this is true with Rails out of the box, but there is an extension
> available at
>
> http://compositekeys.rubyforge.org/
>
> that allows the use of composite keys. I haven't used it myself, yet, so
> can't comment on how well it works.
>
>>I've also seen numerous suggestions that modern web-ish environments
>>(Rails, etc) get confused by composite keys.

>
> As far as Rails is concerned, it is far easier to go with the flow of
> simple, numeric ids. Looking at the URLs I encounter in applications all
> over the web makes me suspect that with other platforms it is similar.
>
>>In all cases, the problem seems to be a lack of understanding.

>
> Here you appear to take a shortcut in the argument. Would you mind to
> elaborate?
>
> Lack of understanding on whose part? Are you sure you're not mistaking a
> trade-off between ease of development, given the constraints of a
> specific platform, and adherence to data modeling principles as lack of
> understanding? I can be fully aware of doing something wrong, but do it
> nevertheless when it is the less wrong of two wrong alternatives.
>
> Michael

Mindless, automatic use of a numeric id without regard to the logical requirements is NEVER the lesser evil. The suggestion that it might be, itself, demonstrates a lack of understanding of: logical vs. physical and the integrity function of data management for a start. Received on Thu May 21 2009 - 16:08:26 CEST

Original text of this message