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

From: --CELKO-- <jcelko212_at_earthlink.net>
Date: 17 Oct 2004 13:56:09 -0700
Message-ID: <18c7b3c2.0410171256.57d7c738_at_posting.google.com>


>> 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?

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.

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

Or as a key, if you can help it.

>> 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.

>> This allows the user to accidentally enter the phone company twice,
but you
actually cannot prevent that no matter what you do, so trying to prevent that is a distraction. <<

Write an article entitled "Data Quality, a Distraction from Quick Programming" and see if you can place anywhere.

>> The Sales Order gets a system-generated numeric sequence ...
Assignment of this key is automatic. <<

Do you remember to add a check digit to this sequence or is data entry quality another distraction? What does the accounting department say when there are gaps in sequence, as is the case with IDENTITY in SQL Server?

>> 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.

>> 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. Received on Sun Oct 17 2004 - 22:56:09 CEST

Original text of this message