Re: Migrating an Application from SQL Server 2000 to Oracle 10g
Date: Wed, 23 Jul 2008 08:04:47 -0700
Message-ID: <1216825484.134601@bubbleator.drizzle.com>
Shakespeare wrote:
> "DA Morgan" <damorgan_at_psoug.org> schreef in bericht
> news:1216766476.361735_at_bubbleator.drizzle.com...
>> Shakespeare wrote: >>> "DA Morgan" <damorgan_at_psoug.org> schreef in bericht >>> news:1216688701.64031_at_bubbleator.drizzle.com... >>>> Shakespeare wrote: >>>>> "Michael Austin" <maustin_at_firstdbasource.com> schreef in bericht >>>>> news:bIygk.33467$ZE5.10372_at_nlpi061.nbdc.sbc.com... >>>>>> One thing I ran into is having to create sequences and triggers for >>>>>> all of those "auto-increment" data types in SQLServer and MySQL that a >>>>>> lot of people like to use for the PK... (which IMO is a complete waste >>>>>> of compute time if the data can be a self-unique PK - but that is just >>>>>> me) >>>>>> >>>>> That's a discussion about meaningles and meaningful (primary) keys - >>>>> imho, data should never be the key itself. >>>>> >>>>> Shakespeare >>>> Are you ready to go toe-to-toe with Joe Celko and many others and >>>> argue that one should never use a natural key? >>>> >>>> I certainly hope not. >>>> -- >>>> Daniel A. Morgan >>>> Oracle Ace Director & Instructor >>>> University of Washington >>>> damorgan_at_x.washington.edu (replace x with u to respond) >>>> Puget Sound Oracle Users Group >>>> www.psoug.org >>> Never say never. If you're ABSOLUTELY sure the natural key never changes >>> and never duplicates, it's ok with me. Even technical real life keys may >>> cause unexpected problems. I recall a salary payment system that used >>> emplyee numbers as pk's. But then it appeared keys changed when women got >>> married and took their husbands name, just because of some external >>> demand that keys should be in the same order as the names... >>> >>> Shakespeare >> A key never changes. If it can then, by definition, it is not a natural >> key. >> >> In the example you used the reason it changes is a business process >> decision that is totally irrelevant to the data. They did not need to >> change the employee id. They choose to. >> -- >> Daniel A. Morgan >> Oracle Ace Director & Instructor >> University of Washington >> damorgan_at_x.washington.edu (replace x with u to respond) >> Puget Sound Oracle Users Group >> www.psoug.org
>
> You're right, but in the design process the value was thought never to
> change, and hence chosen as a primary key. Afterwards, some years later, an
> external progam needed the numbers to be in order. I just wanted to stress
> out that it is not always clear on forehand that an attribute will never
> change. Saying 'if it can change it is not a key' is turning it around.
> Business processes can change in an unpredictable way.
> I worked for a company some years ago that used technical keys only. I have
> seen both advantages and disadvantages. So to some extent I may go with Joe
> Celko, but not as a dogma.
>
> Shakespeare
One might well argue that an employee id is, by definition, a surrogate key. One created by the business for purposes of employee identification and that the natural key might be tax-id and last_name.
In your case I would argue that once the decision was made to use employee_id as a natural key then it MUST remain unchanged. Should someone in upper management decide to alter that decision part of the cost of that decision necessarily had to include the cost of rekeying every computer record in the business both currently in on-line systems and going backward in time for the entire history of the business. That cost, measured in dollars, would have stopped the entire nonsense.
-- Daniel A. Morgan Oracle Ace Director & Instructor University of Washington damorgan_at_x.washington.edu (replace x with u to respond) Puget Sound Oracle Users Group www.psoug.orgReceived on Wed Jul 23 2008 - 10:04:47 CDT