From: Craig A. James <cjames@modgraph-usa.com>
To: Merlin Moncure <mmoncure@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Basic Q on superfluous primary keys
Date: Wed, 18 Apr 2007 09:05:13 -0700
Message-ID: <462641B9.5020102@modgraph-usa.com> (raw)
In-Reply-To: <b42b73150704180744h28d99ebflebe4405b3af5e44d@mail.gmail.com>
References: <c2350ba40704140419r2f3c85dfi60171759b71f4db7@mail.gmail.com>
<20070414075947.e5fbeec8.wmoran@collaborativefusion.com>
<b42b73150704160603x1d164134pfd2af7481f802ed9@mail.gmail.com>
<46239000.7000708@modgraph-usa.com>
<b42b73150704160855s676a8c43n7ef4282f5ef175c9@mail.gmail.com>
<Pine.GSO.4.64.0704162239300.7058@westnet.com>
<b42b73150704170917n33ebcdc8wc852a3fa02fd8bb5@mail.gmail.com>
<46259937.7030502@modgraph-usa.com>
<b42b73150704180744h28d99ebflebe4405b3af5e44d@mail.gmail.com>
Merlin Moncure wrote:
>> Since the part number is NOT the primary key, duplicate part numbers
>> are not a problem. If you had instead used the part number as the
>> primary key, you'd be dead in the water.
>
> You are redefining the primary key to be (part_number,
> obsoletion_date). Now, if you had not anticipated that in the
> original design (likely enough), you do have to refactor queries that
> join on the table...so what? If that's too much work, you can use a
> view to take care of the problem (which may be a good idea anyways).
> *you have to refactor the system anyways because you are now allowing
> duplicate part numbers where previously (from the perspective of the
> user), they were unique *.
>
> The hidden advantage of pushing the full key through the database is
> it tends to expose holes in the application/business logic. Chances
> are some query is not properly distinguishing obsoleted parts and now
> the real problems come...surrogate keys do not remove complexity, they
> simply sweep it under the rug.
This really boils down to an object-oriented perspective. I have an object, a customer's catalog entry. It has properties such as catalog number, description, etc, and whether it's obsolete or not. Management of the object (its relation to other objects, its history, etc.) should NOT depend on the object's specific definition.
This is true whether the object is represented in Lisp, C++, Perl, or (in this case) an SQL schema. Good object oriented design abstracts the object and its behavior from management of the object. In C++, Perl, etc., we manage objects via a pointer or object reference. In SQL, we reference objects by an *arbitrary* integer that is effectively a pointer to the object.
What you're suggesting is that I should break the object-oriented encapsulation by pulling out specific fields of the object, exposing those internal object details to the applications, and spreading those details across the whole schema. And I argue that this is wrong, because it breaks encapsulation. By exposing the details of the object, if the details change, *all* of your relationships break, and all of your applications have to change. And I've never seen a system where breaking object-oriented encapsulation was a good long-term solution. Systems change, and object-oriented techniques were invented to help manage change.
This is one of the reasons the Postgres project was started way back when: To bring object-oriented techniques to the relational-database world.
Craig
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: cjames@modgraph-usa.com, mmoncure@gmail.com
Subject: Re: Basic Q on superfluous primary keys
In-Reply-To: <462641B9.5020102@modgraph-usa.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox