Re: Surrogate Keys: an Implementation Issue

From: Paul Mansour <paul_at_carlislegroup.com>
Date: 19 Jul 2006 09:37:40 -0700
Message-ID: <1153327060.322495.41590_at_h48g2000cwc.googlegroups.com>


Roy,

Thanks for the considered response.

I'm not sure I made myself clear, or else I misunderstand some of your points:

> Yes. It creates a comforting delusion about what you know about the real
> world, as well as creating lots of extra work, and concealing correct
> information behind spurious indirection.

and:

> the change of value everywhere. But it also costs a lot of coding, testing,
> development discipline, and obscurity.

How would this be extra work? From the users, and by user I mean programmer, there is no surrogate key. This is purely an implementation issue. I'm talking about the internals of the DMBS, not desiging a DB. The DBMS does not ever expose the surrogate key. Any from your perspective, as someone designing a DB it would appear no different than any other DBMS. You would have no knowledge that your primary key was not infact propagated all over the place.

> If someone made the sweeping recommendation that my "table should have a
> system-supplied, auto-incremented, and hidden, 'super primary key'." One
> might as well use pointers otherwise--they'd do the same job, and probably a
> heck of a lot faster.

Again, as a programmer, or DBA, should you really care (unless its slow, of course) if it is an implementation issue?

There is, I think, actually a two-pronged benetit to the surrogate key as a behind-the scenes immutable identifier. The first one is, as you say, perhaps not so consequential: its easy to change the natural primary key, and it saves space.

The second is perhaps more profound. Consider a rollback database, or a database that must provide a complete audit trail of every change. For example, the database must provide the answer to "who changed this SS number from X to Y, and when did they change it? As far as I can tell, if there is no way to answer this without an immutable identifier. ( I suppose you could design the DB to handle specific cases, but I'm interested in DBMS with native rollback and audit trail support.)

Again, let me restate that I'm talking about the internals of the DBMS, not how to design a database. Received on Wed Jul 19 2006 - 18:37:40 CEST

Original text of this message