Re: one-to-one

Adrian,

> What are the advantages and disadvantages of this approach over table
> inheritance?

Well, I've always had a problem with table inheritance from a theory
perspective ... it's orthagonal to the SQL pseudo-relational model, and as an
orthagonal structure can cause consistency issues.

Postgresql has a few of these as implementation problems; particularly, the
inability to index over inherited tables, or to properly maintain a list of
dependancies for pg_dump and schema management. And gods forbid you should
start playing with the structure of the parent table after inheritance.
These are all surmountable, but are current drawbacks to the inheritance
implementation.

In Michael's case, he has another issue; non-members will often *become*
members. Using an inheritance model, he would then have to delete the person
from the "people" table, and insert them into "members", a less efficient
operation than simply adding member information to the member_info table.
Futher, it would be likely to disrupt whatever historical auditing mechanism
he has in place by adding a false delete and insert.

Finally, the creation of inherited child tables almost inevitably lead to the
desire for grandchild tables, which I don't think PostgreSQL supports. For
example, imagine if Micheal realized that he needed an even more specific
group of members, "board_members". Or it can lead to a desire for
"multiple-inheritance", another extremely problematic area.

Take this all with a grain of salt; as I said, I have an ideological issue
with table inheritance that certainly biases my viewpoint. From my
perspective, table inheritance is an effort to extend inappropriate OOP
thinking into the SQL database space, and encourages many developers to
remain ignorant of SQL database architecture theory.

--
-Josh Berkus
Aglio Database Solutions
San Francisco