X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 5C88C9FA5D3 for ; Thu, 1 Jun 2006 17:04:10 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 60510-02 for ; Thu, 1 Jun 2006 17:04:04 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from floppy.pyrenet.fr (news.pyrenet.fr [194.116.145.2]) by postgresql.org (Postfix) with ESMTP id 91CD69FA040 for ; Thu, 1 Jun 2006 17:04:04 -0300 (ADT) Received: by floppy.pyrenet.fr (Postfix, from userid 106) id 5126A31DC3; Thu, 1 Jun 2006 22:04:03 +0200 (MET DST) From: Chris Browne X-Newsgroups: pgsql.sql Subject: Re: Table design question Date: Thu, 01 Jun 2006 15:56:00 -0400 Organization: cbbrowne Computing Inc Lines: 49 Message-ID: <60r728sl9r.fsf@dba2.int.libertyrms.com> References: <12b7ac1e0606010405u6e062f71mf4adbaeb6c46df5f@mail.gmail.com> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Complaints-To: usenet@news.hub.org User-Agent: Gnus/5.1007 (Gnus v5.10.7) XEmacs/21.4.19 (linux) Cancel-Lock: sha1:MrTgOCwNpGclZ1YqBGXeQ1U8ZCI= To: pgsql-sql@postgresql.org X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200606/27 X-Sequence-Number: 25263 "codeWarrior" writes: > I never use anything other than "id SERIAL NOT NULL PRIMARY KEY" for my > PKEY's -- as an absolute rule -- I guess I am a purist... Everything else > (the other columns) can have unique constraints, etcetera and be FOREIGN > KEYS, etc... > > Try INSERTING your 100 character "natural" key into a table with 10M++ rows > only to find out there there is already a duplicate.... talk about a > performance hit.... or SELECT -- you end up using way too much RAM and > bandwidth -- unecessarily... Celko is decidedly *NOT* promoting the notion that you should use a 100 byte long "natural key." Jamie's comments of "Orthodox versus Reform" seem reasonably appropriate in outlining something of the difference between the positions. Just because Celko is "Orthodox" doesn't mean he's *stupid*; he makes all sorts of noises about using some ISO standard (11179) such that your primary key values are actually described in some authoritative manner. If you go about using ISO-11179, then, in principle, you could register your scheme for describing IDs for the objects in your system with some ISO people so that others might even become aware of the official status of this. I may not care for doing this; you may not either; a company that builds auto parts that they want to sell into the automotive industry may care about standardizing their part IDs quite a lot. They're not interested in generating stupidly long identifiers; that's inefficient in many ways... > IMHO: You ought to use a numeric, auto-generated sequence (SERIAL) > for you PKEY's ... That's definitely the "Reform" position ;-), and it is a position that can be rationally held. -- select 'cbbrowne' || '@' || 'cbbrowne.com'; http://cbbrowne.com/info/multiplexor.html Rules of the Evil Overlord #185. "If I capture an enemy known for escaping via ingenious and fantastic little gadgets, I will order a full cavity search and confiscate all personal items before throwing him in my dungeon."