Re: Primary vs. Surrogate! What a nightmare debate.
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
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?
DELETE statements in code libraries, so that at least some library
routines do not have to be table-specific. <<
>> 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,
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.
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. <<
>> 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
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.
contains hidden secrets, <<
>> As for natural keys, by the time you have a working systems it
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.
turns out they have nothing to offer. <<