X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (mx2.hub.org [200.46.204.254]) by postgresql.org (Postfix) with ESMTP id 4307511F6961 for ; Wed, 12 Apr 2006 18:41:44 -0300 (ADT) X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Score: -1.979 X-Spam-Level: X-Spam-Status: No, score=-1.979 required=5 tests=[AWL=0.620, BAYES_00=-2.599] Received: from postgresql.org ([200.46.204.71]) by localhost (mx2.hub.org [200.46.204.254]) (amavisd-new, port 10024) with ESMTP id dg+VSxU9pd1v for ; Wed, 12 Apr 2006 18:41:38 -0300 (ADT) X-Greylist: delayed 00:08:17.643449 by SQLgrey- Received: from svr4.postgresql.org (svr4.postgresql.org [66.98.251.159]) by postgresql.org (Postfix) with ESMTP id 4A41411F6CA9 for ; Wed, 12 Apr 2006 18:41:07 -0300 (ADT) Received: from noel.decibel.org (noel.decibel.org [67.100.216.10]) by svr4.postgresql.org (Postfix) with ESMTP id 515EE5AFAE6 for ; Wed, 12 Apr 2006 20:59:47 +0000 (GMT) Received: by noel.decibel.org (Postfix, from userid 1001) id E803456423; Wed, 12 Apr 2006 15:59:15 -0500 (CDT) Received: (hashcash-sendmail, from uid 1001); Wed, 12 Apr 2006 15:59:14 -0500 Date: Wed, 12 Apr 2006 15:59:14 -0500 From: "Jim C. Nasby" To: "Craig A. James" Cc: PFC , Michael Glaesemann , Rodrigo Sakai , pgsql-performance@postgresql.org Subject: Re: FOREIGN KEYS vs PERFORMANCE Message-ID: <20060412205914.GL49405@pervasive.com> References: <001301c65d9c$08884ef0$4700a8c0@TREEZANTHUS> <72C39159-1A4B-4595-A978-1DF57AD42527@myrealbox.com> <443D127D.2070601@modgraph-usa.com> <20060412153951.GH49405@pervasive.com> <443D3A9C.40106@modgraph-usa.com> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <443D3A9C.40106@modgraph-usa.com> X-Operating-System: FreeBSD 6.0-RELEASE-p4 amd64 X-Distributed: Join the Effort! http://www.distributed.net User-Agent: Mutt/1.5.11 X-Hashcash: 1:20:060412:cjames@modgraph-usa.com::wHRpKp5zY8YN2b1P:00000000000000 0000000000000000000000003e73 X-Hashcash: 1:20:060412:lists@peufeu.com::uiCM1cEdXzNsqRh5:03kzH X-Hashcash: 1:20:060412:grzm@myrealbox.com::KAAmCwKHblTHzLAv:0000000000000000000 0000000000000000000000005WMj X-Hashcash: 1:20:060412:rodrigo.sakai@zanthus.com.br::iLKS8GsqHQiZ7148:000000000 00000000000000000000000009JJ X-Hashcash: 1:20:060412:pgsql-performance@postgresql.org::/o80PIzmCzh+E7/Z:00000 0000000000000000000000003YqF X-Archive-Number: 200604/273 X-Sequence-Number: 18357 On Wed, Apr 12, 2006 at 10:36:28AM -0700, Craig A. James wrote: > Jim C. Nasby wrote: > >>1. You have only one application that modifies the data. (Otherwise, you > >>have to duplicate the rules across many applications, leading to a > >>code-maintenance nightmare). > > > >You forgot something: > > > >1a: You know that there will never, ever, ever, ever, be any other > >application that wants to talk to the database. > > > >I know tons of people that get burned because they go with something > >that's "good enough for now", and then regret that decision for years to > >come. > > No, I don't agree with this. Too many people waste time designing for > "what if..." scenarios that never happen. You don't want to be dumb and > design something that locks out a foreseeable and likely future need, but > referential integrity doesn't meet this criterion. There's nothing to keep > you from changing from app-managed to database-managed referential > integrity if your needs change. In this case your argument makes no sense, because you will spend far more time re-creating RI capability inside an application than if you just use what the database offers natively. It's certainly true that you don't want to over-engineer for no reason, but many times choices are made to save a very small amount of time or hassle up-front, and those choices become extremely painful later. -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461