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 EA5E0D1C995 for ; Sat, 2 Aug 2003 21:50:33 +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 52013-07 for ; Sat, 2 Aug 2003 18:49:24 -0300 (ADT) Received: from lakemtao02.cox.net (lakemtao02.cox.net [68.1.17.243]) by svr1.postgresql.org (Postfix) with ESMTP id D413FD1C9DD for ; Sat, 2 Aug 2003 18:50:31 -0300 (ADT) Received: from [192.168.1.1] ([68.11.66.83]) by lakemtao02.cox.net (InterMail vM.5.01.04.05 201-253-122-122-105-20011231) with ESMTP id <20030802215034.OFBP24359.lakemtao02.cox.net@[192.168.1.1]> for ; Sat, 2 Aug 2003 17:50:34 -0400 Subject: Re: Inheritance vs child tables (Was Domains) From: Ron Johnson To: pgsql General List In-Reply-To: <20030802140600.G99004@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> Content-Type: multipart/mixed; boundary="=-h97oAXxMLvPOSyqbgYso" Message-Id: <1059861033.24817.57.camel@haggis> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.4.3 Date: 02 Aug 2003 16:50:33 -0500 X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200308/113 X-Sequence-Number: 46530 --=-h97oAXxMLvPOSyqbgYso Content-Type: text/plain Content-Transfer-Encoding: 7bit On Sat, 2003-08-02 at 13:22, Francisco J Reyes wrote: > On Fri, 1 Aug 2003, Ron Johnson wrote: > > > On Fri, 2003-08-01 at 12:26, Francisco J Reyes wrote: > > > On Fri, 1 Aug 2003, Tom Lane wrote: > > [snip] > > > Currently I used inheritance to enforce the consitency > > > since a good number of fields needed to be common among the tables AND the > > > inheritted tables are basically a supperset of the data, so some times I > > > would want to access the inheritted tables and other times the parent/main > > > table. > > > > Isn't this when you'd really want child tables, instead? > > > I think both ways can accomplish the same (if not very simmilar > functionality), however I find using inherittance easier. > Not really sure about efficiency though. > > A simple example of the type of design I am planning to do would be: > > Table A > Userid > date entered > last changed > > > Table B inherited from A(additional fields) > person name > birthday > > Table C inherited from A(additional fields) > book > isbn > comment > > I plan to keep track of how many records a user has so with inherittance > it's easy to do this. I can count for the user in Table A and find out how > many records he/she has or I can count in each of the inheritted tables > and see how many there are for that particular table. > > 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? Attached is a zip of the sql and results of what I mean. Plain inner joins or LOJ with "WHERE {B|C}.whatever IS NOT NULL" also pare things dawn. Of course, just yesterday, in a post on -general or -performance, I read that LEFT OUTER JOIN isn't particularly efficient in PG. Also, wouldn't it be odd to have a userid without a name? So, why isn't table_b combined with table_a? But all circumstances are different, I guess... -- +-----------------------------------------------------------------+ | Ron Johnson, Jr. Home: ron.l.johnson@cox.net | | Jefferson, LA USA | | | | "I'm not a vegetarian because I love animals, I'm a vegetarian | | because I hate vegetables!" | | unknown | +-----------------------------------------------------------------+ --=-h97oAXxMLvPOSyqbgYso Content-Disposition: attachment; filename=oj_example.zip Content-Type: application/zip; name=oj_example.zip Content-Transfer-Encoding: base64 UEsDBBQAAAAIAEuDAi9Yr/EqzAAAAEECAAANABUAb2pfdGFibGVzLnNxbFVU CQADfSwsP4csLD9VeAQA6APoA61Quw7CMAzc/RWGqUhlgJWPqZzElAiaICcU +vcYyqMVrwUPlnxn+84GJ3GPmcyO+1zRCl4w8wazKwArTJnH41jAIbF4h4/w IXPNgiU4ba9YS+Erf6lL2FHKld1QqIfoXnxD0uGWOyz6lTOcvVc1n1V1EUuK oQrU8JXIfMolGC9546i7t38WLWEdhX0dxlaE13pGsJyep/+wab/ZNDFucRC9 TZ9MGKItiX5KisVSfRkbm0bfORpBnM9xemOm6BOSelXVVr97jOIm/7zyDFBL AwQUAAAACABLhAIvhROVTecAAAC0AgAACgAVAG9qX3BvcC5zcWxVVAkAA14u LD9yLiw/VXgEAOgD6AOt0UFrwyAUB/C7n8JbOpigRk3anDfa7TAYuw9NXtuA NWBfy/btF0s2GhbCGD3J+8P78VdJAx4Q6DZ2B4rWeXivK/I7dFOhrQghbThC RNoG7L5jerb+BEe6EPc0k5znjBdMqmy1aizCT1YyLobsrpp15I2c/NrR/3fU tWPmnQnIjR7oqYOsP8TSGCYk42augxvd5TFCM+wWqQtXf9tN/dc2xs9huW8s mSxnS9ej33jbA113zrV4IWSutCnKJRcyzQ8fNXgPAbPJIvXoEq+Apxjoy5Ym 9LkNuwupldGF0slN8yYg9FACvwBQSwMEFAAAAAgAa4UCL0jWTtp4AAAAywAA AAsAFQBval90ZXN0LnNxbFVUCQADeTAsP4YwLD9VeAQA6APoA4WNsQ7CMBBD 93yFR0BVfgDxLdXd9SoCyR1K0oG/p4WKgQUvtiU/OTTNKh0UTwM4PrQ2t9Go 6FY51X6d6DlAIrvfN0+N7d3FS1HrYa5e0ImzjgRCwK6sc4cvXStunmyfMBhu OFBcmtY04bL+fOLxDyqQX1S+6Dm8AFBLAwQUAAAACACNhQIvoRb9ZnQFAACW FwAADgAVAG9qX3Jlc3VsdHMubGlzVVQJAAO5MCw/tjAsP1V4BADoA+gD5Vdt b9s2EP6uX3FLC9hpLc+S31IXxZamyuq1TTrXw1AgQCDRtM1WFh2JTmLA2G/f UaYkypFsNeuAAhOCyOQdH/LeHp5ISF1BQbier/5fu1A3VhEN2QTShwWCzmgI DWOC6tcUhyGN5XLcMHw3Etdk7gYzfXYZsoUbruErXUN9C3kMxy+NZXTjD/iX pnxZrQFcXI6HZ84A4GzknI4dGJ++fu/Az/BxNPxwOvoM75zPcMd8H8j2tGyx 9BlhAo81ofdQU+e+XuJGNZjyUNmTCGqGDmyQhzZ75TajGTSMeHAduAsaCwS9 Fw3DY6GYT9x1ol5ucsPAI1E2C/KOCOkUnRgQGmWOL3NS/3s5yStzkleruGPh HiJkM/RVPTqOkc8vR87wt4v4TGROyVcUHIwB2RcDj/OvoD3bGLDIC/TZWzfE JAzrlo1O9whfLDBTc0sATBOOlOQIWAQuBgJ3vcXEvePh5Kf/KIR293uFkJSF kNQq7vh9Qrigv87d2YxFg7+fwi0D/uVa0EjIvfMyeSgwXTCnug7IH5YxoT7F Y0xDvkjy4KXxxnnv4D6tAqm3V+pqUhZgJKTvBE+jc+v6KwxW3WpAzW612mar b9qd2mAQV28yd2K2LDWHURxefHJGY7D6VqcP1l5Y+3GwJwdg2zpstzLsiwOw HR22VxW22yqE9XK+/Z3TGr6sF72eadlmq1eIZB1Aknaf4z2joPryoK1OIZR9 AEra+tYNw7XCQuts0z4pxGoXYpFclMdzCm+55zERA9rtTrfXP3nRsmw5du4J 9X1kmFoeuXMAWRo8omIVBnA5BbnHOxbM4h26nV633+nKbeR4iMSIQDv4XcQv LD1VbH9RH6kPCZdvJf1mu4l7CtzoI4/ELKSf/ngfk27oEsFuUZOGCxa4ftMA Y7xeUqSSK8KXaySLuYiJYsIipA5vJRgPYvXIUIR7NY8V5tRfIuWIOUhwSb1u MMmUfsmUECDeG/eDCFuK+UPtGUjW2x4KSz+GjeiCEe7L7TnQe0pWKLlZ0XCd LruRopsVE2hG7IpXT3CZT4mAZzv8AeoO2kCu1dmA3uMYpnqem7lnZ6j2t3B1 Rgs4kQxljSkdu4JOW9fpFut0dJ3erk69AyG/Qybf7wZPd4Pe/mwAkr6nxAXP S+xHRkiv6Q1kxKCZdq5aylRFFbxmWVzCuoqqY6PermQY0Q1Tj95abCfTtmID SRNRFvCDWaCFN+MM3RMadeAwZQ7NLw8ZQa7TCAGHig+Muv3AD1eTJLmTchjH XcPRcuXh1d9UwqNYesb91SJQrpAFH//6wCdsyjAPjDJD8ybn2rhN+vWwgYAL CFa+b+wWlxzGykauznKSoeyCaDRIzIn7INDbNU+ElKYtWJEPPKPcBV7sgm/y QBUHFNufryrZmyrz9e+KPdZ7B60/Vx3rOxQRHiBLu3gQBHhq5bq7tGMdOefO yLk4cz4lHk5Flxfw58c3sve7uITTs/EQJ/BP9VrpXJHHSZJ1pX4n0u+J2zeZ 6tb3aRYVhaCwGsujkYUi/+iBSekgi8qOsqEThPzewLsSIfHjY421KT8+4jgm nx/lODshJT94SBWhus1nDfCaz7bz5pPc/QluI+29vFTjbo5ljgtVIF7h8u3P itctDh95HZUOq91apTcypJla8XIrvbczpIp3YOntniE98qo8EFnw6VQAX8lk /8JZkAVadm71gvAe/z/iu7d3A43TMnJTRPIjpsfDFvEq/3GfzxYtXnKYxKsB pCmpVL4lXcbjpJnK51V6QXx7fh1YSoDsLiXp0uq5WZKSWjz/VQ+5d/i8XHR4 eeVML0vwnYwtTOM0jyuXwUOQxzfH+wunrF6+savOymZ/dZUVVUU3pqX3D1BL AQIXAxQAAAAIAEuDAi9Yr/EqzAAAAEECAAANAA0AAAAAAAEAAACkgQAAAABv al90YWJsZXMuc3FsVVQFAAN9LCw/VXgAAFBLAQIXAxQAAAAIAEuEAi+FE5VN 5wAAALQCAAAKAA0AAAAAAAEAAACkgQwBAABval9wb3Auc3FsVVQFAANeLiw/ VXgAAFBLAQIXAxQAAAAIAGuFAi9I1k7aeAAAAMsAAAALAA0AAAAAAAEAAACk gTACAABval90ZXN0LnNxbFVUBQADeTAsP1V4AABQSwECFwMUAAAACACNhQIv oRb9ZnQFAACWFwAADgANAAAAAAABAAAApIHmAgAAb2pfcmVzdWx0cy5saXNV VAUAA7kwLD9VeAAAUEsFBgAAAAAEAAQAHAEAAJsIAAAAAA== --=-h97oAXxMLvPOSyqbgYso--