Re: Migrating an Application from SQL Server 2000 to Oracle 10g

From: DA Morgan <>
Date: Thu, 24 Jul 2008 11:10:50 -0700
Message-ID: <>

joel garry wrote:
> On Jul 23, 8:04 am, DA Morgan <> wrote:

>> Shakespeare wrote:
>>> "DA Morgan" <> schreef in bericht
>>>> Shakespeare wrote:
>>>>> "DA Morgan" <> schreef in bericht
>>>>>> Shakespeare wrote:
>>>>>>> "Michael Austin" <> schreef in bericht
>>>>>>> news:bIygk.33467$
>>>>>>>> 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
>>>>>> (replace x with u to respond)
>>>>>> Puget Sound Oracle Users Group
>>>>> 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
>>>> (replace x with u to respond)
>>>> Puget Sound Oracle Users Group
>>> 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.

> Marriage? Divorce? People who refuse to give tax-id for religious
> reasons? People who use several names? (My wife, for example, uses
> my last name for some things and the name under which she received her
> degree and licensure for others. Makes for some entertainment when
> the junk mail/phone calls come in.)
> Some guy was on the news, had the same name and birthdate as a sex
> offender - and was born in the same county. Yikes!
> I think I've mentioned before about the vendor who uses parts of
> customers names and parts of the customer company name as an id for
> support, and the company I work for changed their 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.

> I do stuff like that all the time. Businesses buy and sell other
> businesses and parts of themselves. Sometimes it's nonsense, true.
> Usually it's more tedious than hard. With thousands of tables that
> are normalized, it isn't every computer record.
> jg
> --
> is bogus.
> What's in your database?

I was involved a few years back with a bank that was in the business of purchasing other banks. Part of the cost calculated into those purchases was system integration. And that included, by definition, rekeying every record.

There is always a fast cheap way of doing things. There is also always a DBA who works late and on weekends. At the end of the day no one gives away awards for masochism.

Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington (replace x with u to respond)
Puget Sound Oracle Users Group
Received on Thu Jul 24 2008 - 13:10:50 CDT

Original text of this message