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

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
.

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 ... 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 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: Low Cost Hydrogen is here to stay
    ... I build a business model and note gaps, failings, cost ... and then use the coal to make jet fuel. ... use solar hydrogen to hydrogenate coal directly making liquid ...
    (sci.energy.hydrogen)
  • Re: Low Cost Hydrogen is here to stay
    ... I build a business model and note gaps, failings, cost ... and then use the coal to make jet fuel. ... use solar hydrogen to hydrogenate coal directly making liquid ...
    (sci.energy.hydrogen)
  • Re: more and more excel to access....and the inputs are really stupid
    ... the management of operations at this plant for their main customer and would ... business decisions as quickly as possible. ... while you make modifications that cost us $$$. ... MATTER what we do we are wrong and no matter how good the database is ...
    (comp.databases.ms-access)