Re: Inheritance vs child tables (Was Domains)

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