Postgres inheritance for maintainability

Question

Edit: TMI in initial question, cut to essentials.

I'm thinking of a schema to support updating entries and version tracking. This is for a slowly changing dimensions scenerio, with a twist. To support the behavior I want, the basic schema is replicated three times:

1. public tables,
2. private tables, and
3. change tracking tables

This will work beautifully for my purpose, but the down side of the replication approach seems to be that it would be cumbersome and error prone to maintain (we generally have periodic minor schema changes).

To help with maintainability, I was thinking of using table inheritance: define the primary fields in a set of base tables, and inherit the three new sets of tables from these (augmented with bookkeeping fields). When schema changes are needed, just make them to the base table. Queries would only be made on derived tables.

So the question is: is this a valid use of table inheritance? Is there a better way to support maintainability of replicated tables? Relevant links would be appreciated.

I've never used table inheritance before, would like know if I'm strolling into a mine field. Thanks.

Edit: found one mention of using inheritance for change tracking tables in the comments of the pg8.0 docs.

Answer 1

Why do you want to replace your actual "two bases" system ? You alternative looks more complex, difficult to maintain, and calls for acrobatic coding techniques.

If it ain't broke, don't fix it

What's the added power/flexibility you expect ?