Re: Natural keys vs Aritficial Keys

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. (I should clarify: if a university's registration
> office assigns each student a StudentID, before entering that student into
> the database, I'm treating that key as "natural" in the context of the
> database, even though one might argue that it's "artificial" in some other
> context.) This generally surfaced in the choice of one or more columns to
> be declared as a PRIMARY KEY.
>
> Every time the issue of natural keys comes up in Stackoverflow.com, the
> prevailing view seems to be that the best primary keys are artificial and
> opaque. And responses that take this view get the votes. They emphasize
> efficiency (mainly efficiency in the index that you usually get
> automatically when you declare a PRIMARY KEY). But data integrity seems to
> be forgotten, here. The same is not generally tru when the conversation
> turns to referential integrity. Slowly but surely the programming community
> seems to heve been dragged, kicking and screaming, into turning over
> referential integrity enforcement to the DBMS.
>
>
> What I don't get is whether I've understood something that today's
> trendsetters never learned or whether they are taking into consideration
> matters that I never considered important, back in the day. I've read their
> arguments carefully, and it seems to me that failure to declare UNIQUE and
> NOT NULL constraints on the most widely used candidate keys is simply an
> invitation to degradation of data integrity. PRIMARY KEY is a convenient
> way to get those two constraints with one declaration.
>
> Is data integrity less important than it used to be? Are most people
> building databases that get embedded in some appplication to be sold to the
> non technical buyer? Is something else going on that I'm unaware of? Can
> some body explain to me what these people are thinking?
>

I don't think it's you who's confused. Received on Fri May 15 2009 - 21:26:03 CEST