Received: from localhost (maia-1.hub.org [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id AA7039FB2CB for ; Tue, 17 Apr 2007 00:02:51 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.191]) (amavisd-maia, port 10024) with ESMTP id 64189-10 for ; Tue, 17 Apr 2007 00:02:47 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from westnet.com (westnet.com [216.187.52.2]) by postgresql.org (Postfix) with ESMTP id 84E4A9FB5DF for ; Tue, 17 Apr 2007 00:02:47 -0300 (ADT) Received: from westnet.com (localhost [127.0.0.1]) by westnet.com (8.14.0/8.14.0) with ESMTP id l3H32mJU011720 for ; Mon, 16 Apr 2007 23:02:48 -0400 (EDT) Received: from localhost (gsmith@localhost) by westnet.com (8.14.0/8.13.2/Submit) with ESMTP id l3H32mXl011717 for ; Mon, 16 Apr 2007 23:02:48 -0400 (EDT) X-Authentication-Warning: westnet.com: gsmith owned process doing -bs Date: Mon, 16 Apr 2007 23:02:48 -0400 (EDT) From: Greg Smith X-X-Sender: gsmith@westnet.com To: pgsql-performance@postgresql.org Subject: Re: Basic Q on superfluous primary keys In-Reply-To: Message-ID: References: <20070414075947.e5fbeec8.wmoran@collaborativefusion.com> <46239000.7000708@modgraph-usa.com> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII; format=flowed X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200704/322 X-Sequence-Number: 24199 On Mon, 16 Apr 2007, Merlin Moncure wrote: > extraordinary cases do happen, like a company overhauling its numbering > systems, but such cases can be dealt with by a number of methods > including letting RI do its thing. I think the point Craig was trying to make is that what you refer to here as "extraordinary cases" are, in fact, rather common. I've never seen a database built on natural keys that didn't at some point turn ugly when some internal or external business need suddenly invalidated the believed uniqueness of that key. The last really bad one I saw was a manufacturing database that used a combination of the customer code and the customer's part number as the key. Surely if the customer changes their part number, we should switch ours to match so the orders are easy to process, right? When this got fun was when one large customer who released products on a yearly schedule decided to change the bill of material for many of their parts for the new year, but re-used the same part number; oh, and they were still ordering the old parts as well. Hilarity ensued. > it is especially unclear how adding an integer to a table will somehow > magically solve these problems. If the key is a integer, it's always possible to figure out a trivial map that renumbers the entire database programmatically in order to merge two sets of data. -- * Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD