pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Francisco J Reyes <fran@natserv.net>
To: Ron Johnson <ron.l.johnson@cox.net>
Cc: pgsql General List <pgsql-general@postgresql.org>
Subject: Re: Inheritance vs child tables (Was Domains)
Date: Sun, 3 Aug 2003 10:27:11 -0400 (EDT)
Message-ID: <20030803093159.M47689@zoraida.natserv.net> (raw)
In-Reply-To: <1059861033.24817.57.camel@haggis>
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>

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




view thread (3+ messages)

Message-ID: <20030803093159.M47689@zoraida.natserv.net>
Permalink:  ../20030803093159.M47689@zoraida.natserv.net/
Also on:    postgresql.org/message-id/20030803093159.M47689@zoraida.natserv.net

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-performance@postgresql.org
  Cc: fran@natserv.net, ron.l.johnson@cox.net, pgsql-general@postgresql.org
  Subject: Re: Inheritance vs child tables (Was Domains)
  In-Reply-To: <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> <20030803093159.M47689@zoraida.natserv.net>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox