Re: Trying to Understand Table Inheritance

On Wed, 2006-12-06 at 13:58 -0700, Keary Suska wrote:
> My thoughts then turned to table inheritance, but I am not sure whether this
> addresses the issue or introduce new problems. My thought was that I could
> have an "order_item" table with the columns/data shared by all line items,
> and then have three tables--merchandise, membership, and subscription--that
> inherit from it.
>

There are two ways to accomplish basically the same thing. I think Erik
already answered your inheritance questions, but it's possible to do
without inheritance as well.

You can create a table called order_item with columns that exist for all
items (the shared columns). Create a table called merchandise with
columns that exist only for merchandise and not other items (without any
of the shared columns).

When you insert new merchandise, put the common values into the
order_item table, and the merchandise-specific attributes into the
merchandise table with a foreign key to the order_item record. When you
want all the items, select from order_item. When you want only the
merchandise, join the order_item and merchandise tables, and of course
the join will eliminate all non-merchandise records. And you can do the
same for memberships and subscriptions.

Hope this helps,
Jeff Davis