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

joel garry wrote:
On Jul 23, 8:04 am, DA Morgan <damor...@xxxxxxxxx> wrote:
Shakespeare wrote:
"DA Morgan" <damor...@xxxxxxxxx> schreef in bericht
news:1216766476.361735@xxxxxxxxxxxxxxxxxxxxxxxxx
Shakespeare wrote:
"DA Morgan" <damor...@xxxxxxxxx> schreef in bericht
news:1216688701.64031@xxxxxxxxxxxxxxxxxxxxxxxxx
Shakespeare wrote:
"Michael Austin" <maus...@xxxxxxxxxxxxxxxxxx> schreef in bericht
news:bIygk.33467$ZE5.10372@xxxxxxxxxxxxxxxxxxxxxxx
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
damor...@xxxxxxxxxxxxxxxx (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
damor...@xxxxxxxxxxxxxxxx (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.

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

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
damorgan@xxxxxxxxxxxxxxxx (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
.

Relevant Pages

  • Re: Migrating an Application from SQL Server 2000 to Oracle 10g
    ... Puget Sound Oracle Users Group ... If you're ABSOLUTELY sure the natural key never changes and never duplicates, ... Business processes can change in an unpredictable way. ... the cost of that decision necessarily had to include the cost of ...
    (comp.databases.oracle.server)
  • Re: Migrating an Application from SQL Server 2000 to Oracle 10g
    ... Puget Sound Oracle Users Group ... If you're ABSOLUTELY sure the natural key never changes ... Business processes can change in an unpredictable way. ... the cost of that decision necessarily had to include the cost of ...
    (comp.databases.oracle.server)
  • Re: How should I generate a primary key?
    ... And I have agreed that there are cases where a pseudokey is useful. ... the business case that it should be VERY HARD to change ... change the value of the primary key which is more problematic in the field ... The generated key is used as a primary key, but the natural key still sits ...
    (comp.databases)
  • Re: Inserting a new PK into an existing table
    ... David Newman wrote: ... Normally we use a business column or set of column values in the table ... and that an artificial primary key is always preferred. ... If you use a natural key, for example a tax identification number, it ...
    (comp.databases.oracle.misc)
  • Re: Migrating an Application from SQL Server 2000 to Oracle 10g
    ... Puget Sound Oracle Users Group ... If you're ABSOLUTELY sure the natural key never changes ... Even technical real life keys may ... In the example you used the reason it changes is a business process ...
    (comp.databases.oracle.server)