Re: [Info-ingres] Re: dbms sequences access

Betty & Karl Schendel wrote:
At 3:10 PM -0700 7/2/06, simonl@xxxxxxxxxxxxxx wrote:
Oh, ok. But taking your beef cattle example, the ISO cattle number is
really a surrogate key, it was just assigned by somoene else. There
are many cases where there is no natural unique key. ...

Other examples include identity numbers (unless your in the US and can
use social security number as a natural unique key), ...

Which would be a huge mistake, because they aren't guaranteed to be
unique. I don't know why not, and maybe it's urban legend; but
I have long heard rumor of horror stories based on nonunique SSN's,
going back at least as far as the OGSL student loan tracking system
that was hosted for a while at my first employer (On-Line Systems),
lo many years ago before dirt was invented.

Karl
Gang,

Karl is correct, if not for any reason than people can give the
incorrect SSN to their employer, either by accident or by mistake.
But even more common, the SSN usually needs to be typed into
25 systems and manually copied/faxed/transcribed a few times.

Taking it further, I recently worked on a business system where the
business entity Client was very difficult to assign a logical primary
key to because the Client would supply multiple, conflicting, and
purposely incorrect identification information. Very often days or
months later the business would figure this out and need to merge
complete sets of records for the Client.

Many business analysts looked at the problem and always tried to
fix it by making the Client properly identify themselves. That would
be nice if the Client wasn't a criminal being arrested!

I've run into way to many real-life situations where all the data analysis
and modeling cannot shake out a practical, legal, or actual primary key. And
taking it even further, I find many real-life situation where one or more
parts of a logical primary key can change over time.

Surrogate/artificial keys aren't always for lazy programmers or inferior data
mapping tools that require them.

Unfortunately, many lazy programmers mis-use them, over-use them,
or use them for no apparent reason.

The value of the surrogate/artificial keys shouldn't matter. They don't
need to be monotonically increasing or gapless. Matter of fact,
they should be completely random and unidentifiable for many
reasons. A 'uuid' style key works best, randomly generated. Why?

- indexes work better
- hard for hackers to "walk" sequences of data without raising suspicion
- transaction semantics have no meaning with random keys
- b-tree tables experience distributed locking during heavily concurrent insertions
- people don't start using these keys for business purposes (too hard to deal with!)
- more ...

The only reason sequences are so popular is that programmers or framework
developers are lazy.

Dealing with the one-in-a-billion chance of a "duplicate key" error when
inserting a new row with a randomly generated key just can't be that hard. It
is a "cross-cutting" concern and could be easily wrapped using an "aspect-oriented"
tool.

A properly generated uuid-style key has so little chance of collision that it pales
in comparison to the chances that deadlock or another transaction resource
failure causes an error. Doesn't the programmer/framework have to deal with
that? Unfortunately, deadlock and/or transaction resource failure are ignored by
both, or handled very poorly.

Anyways, my $0.02 (tax included). Flame away!

Mike Leo
Caribou Lake Software LLC
begin:vcard
fn:Michael Leo
n:Leo;Michael
org:Caribou Lake LLC
adr:Suite 100;;8401 Golden Valley Drive;Minneapolis;MN;55427;United States
email;internet:mleo@xxxxxxxxxxxxxxx
x-mozilla-html:FALSE
url:http://www.cariboulake.com
version:2.1
end:vcard

Relevant Pages

  • Digital Signal Processing Engineer - Europe
    ... CommsResources is a global ICT - Information Communication Technology ... Staffing firm/ business. ... Our Client is looking for Digital Signal Processing Engineer based at ... a permanent post carrying responsibility for managing our ...
    (comp.soft-sys.matlab)
  • Digital Signal Processing Engineer - Europe
    ... CommsResources is a global ICT - Information Communication Technology ... Staffing firm/ business. ... Our Client is looking for Digital Signal Processing Engineer based at ... a permanent post carrying responsibility for managing our ...
    (comp.dsp)
  • HYPERION ESSBASE DEVELOPER - full time position in NC
    ... the Business Intelligence sector since 1993. ... This position offers an unusual opportunity in that while our client ... The client originally used Hyperion Essbase, ...
    (comp.databases.olap)
  • Excellent openings for an MNC Client in Blore for java,c++,.net & Testing Professionals
    ... Excellent openings for an MNC Client in Blore for java,c++,.net & ... DB2 Database Architects: 5+yrs experience ... Skills given below: ... Business analyst ...
    (microsoft.public.dotnet.framework.aspnet)