Re: Primary vs. Surrogate! What a nightmare debate.

From: Kenneth Downs <firstinit.lastname_at_lastnameplusfam.net>
Date: Sun, 17 Oct 2004 20:49:06 -0400
Message-ID: <3u3vkc.05i.ln@mercury.downsfam.net>

--CELKO-- wrote:

>>>  Regardless of any theory, I use it  solely to code UPDATE and

> DELETE statements in code libraries, so that at least some library
> routines do not have to be table-specific. <<
>
> Routines should be table-specific, or were Yourdon, DeMarco,
> Constantine et al wrong when they did all that Software Engineering
> research? Coupling? Cohesion? all that jazz?

Who am I to say such great minds are capable of error? I just know how to make a system that lets everyone, that's users and me, avoid working Christmas.

>
> What exactly do you name such a routine?
> "Update_BritneySpears_or_Fishtanks()" only begins to touch the
> possible uses of nightmares that mix data and metadata in the same
> code.

If I told you my naming conventions you'd have a cardiac.

>

>>> Absolutely never use the meaningless integer as a foreign key. <<

>
> Or as a key, if you can help it.

hee hee hee, life needs a little humor.

>

>>> 2. Some tables are reference tables, such as the list of customers,

> vendors, employees and so forth. I give these a character unique key
> that is entered by the user but otherwise meaningless. So you can
> have keys in the vendors table like "PHONE_CO" and "LIGHTS" and
> "SEARS" and "OFFICEMAX" and so forth. <<
>
> Doesn't the accounting department get pissy about not being able to
> relate your database to their chart of accounts? Much as I dislike
> the trolls in accounting, they are a trusted source for the business.

Why can't they do that? Methinks you are making many many assumptions.

>

>>> Never pack a key, as in never have the key parsable so that it

> contains hidden secrets, <<
>
> The way that a sequence number gives us a count of orders? Or the way
> that UPC codes tell us the manufacturer and product? Thirty years of
> UPC hae not been a failure.

I prefer to limit my design decisions to those items which I control. I do not control the creation of UPC codes. My designs treat this information as received from a third party.

>

>>> As for natural keys, by the time you have a working systems it

> turns out they have nothing to offer. <<
>
> Validation. Verification. The ability to see that the data model and
> reality match. You know, things that are distractions to fast, sloppy
> coding that can be pushed out the door and left to the next guy to
> clean up.

You cannot prevent the addition of incorrect information, period. If you define a natural key on first name + last Name, somebody will enter a misspelled name and John Smith is in there twice, as John Smith, and John SSmith. For this reason natural keys as primary keys (and by extension as foreign keys) can really bite you in the rear-end, because they can lead you to a false sense of security.

Cheers!

-- 
Kenneth Downs
Use first initial plus last name at last name plus literal "fam.net" to
email me

Received on Sun Oct 17 2004 - 19:49:06 CDT