Re: table inheritance - useful?

Actually, we use it at work. It's one of those features where it's not
real common to find a use-case, but when you do it makes life *so* much
easier.

They way we're using it is to store real-world money transactions.
There's two parts of that: the account in the world that we're dealing
with (what we call a payment_instrument), and the actual
physical_payment.

So we have payment_instrument as a table, with fields like
payment_instrument_id, customer_id, payment_instrument_status_id,
payment_instrument_type_id, etc. Different types of accounts are in
tables that inherit from payment_instrument, such as bank_account (all
of this is in a payment_instrument schema, btw). In addition to the
fields it inherits from payment_instrument, bank_account has fields like
routing_number and account_number. Similarly we have debit_card.

The great thing is that parts of the system that don't care about what
type of payment_instrument something is just use the payment_instrument
table. Parts that actually understand what a bank_account is just use
the bank_account table; they have no need to actually touch the
payment_instrument table. This makes the code a hell of a lot cleaner.

We've also built some tools that make it easier to handle some of this
stuff. For example, we have a trigger that tries to ensure that
payment_instrument_id is unique across all inherited tables. We also
have a trigger that allows other tables to have referential integrity to
a payment_instrument, regardless of it's type.

Things are even more interesting in physical_payment. For most of our
money movement, we're the ones who originate the request
(physical_payment_us), but for some stuff we actually get told by a bank
that money has moved in the real world, which means that we need to
account for it (physical_payment_them). So we have:

physical_payment.physical_payment
|
+-> physical_payment_them.physical_payment_them
| |
| +-> physical_payment_them.ppc_lines
|
+-> physical_payment_us.physical_payment_us
|
+-> physical_payment_us.debit_card_lines
+-> physical_payment_us.ach_lines

Again, this means that stuff that doesn't care about details (like our
accounting table) simply references physical_payment.physical_payment.
Our bank account transfer code for the United States uses
physical_payment_us.ach_lines. Debit cards use debit_card_lines.

I know this looks complicated, but consider the alternative: For
payment_instruments, you'd have a payment_instruments table that stored
all the common stuff, and then other tables that would simply have a
foreign key to payment_instrument. All of your code would have to make
sure it keeps records in both tables correct and in sync. So when you
add a record you'd have to first insert into payment_instrument, and
then insert into bank_account. And when you're reading from
bank_account, if you needed information that was common to all
types of payment_instrument, you'd need to join to that table.

The other option is that you have a ton of nullable fields, and
hopefully you only use the correct ones for the right type of
payment_instrument.

Hopefully it's obvious how both of those suck. :)

With inheritance, when you want to insert a bank account you just insert
into bank_account. The relevant fields magically show up in
payment_instruments. You can select from either table and you're looking
at the same record (except for obviously having more fields in the
bank_account table). Your code only needs to look at whichever table is
appropriate for what it's doing. IE: a lot of you website will only
look at payment_instrument; only one or two pages will look at
debit_card. Your debit card processing code will only look at debit_card
and debit_card_lines; it doesn't care at all about payment_instrument or
physical_payment_us (or physical_payment).

Hopefully this makes some sense. If people are interested I can talk
about what we have setup at the next PUG meeting.

On Thu, Oct 08, 2009 at 12:59:54AM -0500, Matthew Weigel wrote:
> One of the things I've heard touted for a long time about Postgres - but never
> used - is table inheritance. As far as I can tell, it's the only feature that
> leads to Postgres being considered an "object-relational" database system.
>
> I've done some Googling on the matter and it *seems* like virtually no one is
> using it or working on it; an approach that was interesting and had potential,
> but perhaps has fallen by the wayside now.
>
> Does anyone actually use it? Has it been useful for more naturally doing
> object/relational mapping? I've read the documentation and caveats on what it
> does and does not provide (indexes applying to only single tables is probably
> the most worrisome), but I'm curious about real-world experiences with it.
> --
> Matthew Weigel
> hacker
> unique & idempot . ent
>
> --
> Sent via austinpug mailing list (austinpug(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/austinpug
>

--
Decibel!, aka Jim C. Nasby, Database Architect decibel(at)decibel(dot)org
Give your computer some brain candy! www.distributed.net Team #1828