Re: Migrating an Application from SQL Server 2000 to Oracle 10g
Date: Wed, 23 Jul 2008 15:57:44 -0700 (PDT)
Message-ID: <7e9375a3-90a9-4535-bc33-045a49258df4@s21g2000prm.googlegroups.com>
On Jul 23, 8:04 am, DA Morgan <damor..._at_psoug.org> wrote:
> Shakespeare wrote:
> > "DA Morgan" <damor..._at_psoug.org> schreef in bericht
> >news:1216766476.361735_at_bubbleator.drizzle.com...
> >> Shakespeare wrote:
> >>> "DA Morgan" <damor..._at_psoug.org> schreef in bericht
> >>>news:1216688701.64031_at_bubbleator.drizzle.com...
> >>>> Shakespeare wrote:
> >>>>> "Michael Austin" <maus..._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.
>> >>>> Are you ready to go toe-to-toe with Joe Celko and many others and
> >>>>> Shakespeare
> >>>> argue that one should never use a natural key?
>> >>> married and took their husbands name, just because of some external
> >>>> I certainly hope not.
> >>>> --
> >>>> Daniel A. Morgan
> >>>> Oracle Ace Director & Instructor
> >>>> University of Washington
> >>>> damor..._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
> >>> demand that keys should be in the same order as the names...
>> >> A key never changes. If it can then, by definition, it is not a natural
> >>> Shakespeare
> >> key.
>> >> Puget Sound Oracle Users Group
> >> 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
> >> damor..._at_x.washington.edu (replace x with u to respond)
> >>www.psoug.org
>> > seen both advantages and disadvantages. So to some extent I may go with Joe
> > 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
> > 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
-- @home.com is bogus. What's in your database? http://www.democratherald.com/articles/2008/07/13/news/top_story/1aaa01_id.txtReceived on Wed Jul 23 2008 - 17:57:44 CDT