X-Original-To: pgsql-general-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.130]) by svr1.postgresql.org (Postfix) with ESMTP id D0649D1C4D5 for ; Sun, 3 Aug 2003 14:25:30 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new, port 10024) with ESMTP id 33072-10 for ; Sun, 3 Aug 2003 11:24:21 -0300 (ADT) Received: from mail1.acecape.com (mail1.acecape.com [66.114.74.12]) by svr1.postgresql.org (Postfix) with ESMTP id 9FB3CD1C4BF for ; Sun, 3 Aug 2003 11:25:19 -0300 (ADT) Received: from p65-147.acedsl.com (p65-147.acedsl.com [66.114.65.147]) by mail1.acecape.com (8.12.9/8.12.9) with ESMTP id h73EPM0H023829; Sun, 3 Aug 2003 10:25:23 -0400 Date: Sun, 3 Aug 2003 10:27:11 -0400 (EDT) From: Francisco J Reyes To: Ron Johnson Cc: pgsql General List Subject: Re: Inheritance vs child tables (Was Domains) In-Reply-To: <1059861033.24817.57.camel@haggis> Message-ID: <20030803093159.M47689@zoraida.natserv.net> References: <20030801075335.D51229-100000@megazone.bigpanda.com> <60vfthl6q6.fsf@dev6.int.libertyrms.info> <16388.1059756693@sss.pgh.pa.us> <20030801131642.Q93127@zoraida.natserv.net> <1059782193.22384.85.camel@haggis> <20030802140600.G99004@zoraida.natserv.net> <1059861033.24817.57.camel@haggis> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200308/127 X-Sequence-Number: 46544 On Sat, 2 Aug 2003, Ron Johnson wrote: > > Inheritance makes it easier to see everything for a userid or just a > > particular type of records. > > But isn't this what LEFT OUTER JOIN is for? Yes but the more tables you have the more cumbersome it would become to do with outer joins. Imagine a parent table and 20 children tables. To get a count of all records the user has I either have to do a nasty/ugly union or do 20 counts and then add them (ie doing the separate counts and keeping track of them with a language like PHP) > Of course, just yesterday, in a post on -general or -performance, > I read that LEFT OUTER JOIN isn't particularly efficient in PG. And it's probably worse when many tables are involved. > Also, wouldn't it be odd to have a userid without a name? So, > why isn't table_b combined with table_a? I have a separate table with user information. The main reason I thought of inherittance was because I need to do accounting and keep track of how many records a user has for certain type of data or in total. Inheritance makes this really easy. Table A, B and C are not combined because B, C and onward have totally different type of data and they are not one to one. There are times when children tables make more sense like: *person table -person id -name -address *phones -person id -phone type (ie fax, home, work) -area -phone *emails -person id -email type (home, work) -email In my opinion a case like that is best handled with children tables. Specially if there are only a couple of childre tables. On my case I have about 8 inherited tables and what I believe inheritance does for me is: * Easy way to count both a grand total or a table per inherited table. * Easy to work with each inheritted table, which will be very often. * Much simpler queries/reporting