Re: Trying to Understand Table Inheritance

Keary Suska wrote:
> I have a problem I am trying to address and am looking to see if table
> inheritance may be the solution.
>
> My problem is this: I have a POS system using basic POS principles--i.e., I
> have an "invoice" table, with a one-to-many related "line items" table. Now,
> each line item may specify one of certain "types" of products with
> differencing management behaviors. These boil down to: merchandise, which is
> ordered/shipped once; memberships, which persist and expire at some point;
> and subscriptions, which have a certain number of "issues" that are sent on
> a periodic basis.
>
> All line items share certain characteristics, such as a product, quantity,
> etc., but also have the differing management criteria--such as ship date,
> expiration date, and issues sent. My first approach was to have all of this
> data in a single table, but that creates a lot of redundant data and forces
> "loose" constraints--e.g. that I have to allow NULL expiration dates for
> merchandise products.
>
> 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.
>
> I understand that queries on order_item would include the child table(s)
> columns, but my question is that is this approach essentially the same as
> just using one table, vis a vis redundant data? If I insert into order_item,
> can I only include columns from a single child table, or do I have to
> include all children or risk a constraint violation? Or do I have to only
> insert into the child table(s) and include the parent's columns? The latter,
> from a functional standpoint, would be more difficult, and messy potentially
> messy. Of course, maybe I am barking up the wrong tree?
>
> Thanks,
>
No, running queries against the parent table will return the data for
columns in the child tables that are present in the parent table, not
the added columns in the child tables (unless you use ONLY in the from
clause of your query in which case you will only get data from the
parent table). Also, when you insert data into the parent table, you
can only insert column data into the parent table for columns that are
in the parent table. The parent and child table are separate tables.
And, if you insert directly into the parent table, then you can not
access that data through the child tables.

CREATE TABLE order_item (
order_item_id serial primary key,
name text not null,
quantity integer not null);

CREATE TABLE merchandise (
weight numeric not null
) INHERITS (order_item);

INSERT INTO order_item (name, quantity) VALUES ('prod1', 2);
INSERT INTO order_item (name, quantity) VALUES ('prod2', 1);
INSERT INTO order_item (name, quantity, weight) VALUES ('prod3', 5,
5.26); -- good luck with that ship date !!!

SELECT * FROM order_item;
order_item_id name quantity
-------------- ------ --------
1 prod1 2
2 prod2 1
3 prod3 5

SELECT * FROM merchandise;
order_item_id name quantity ship_date
-------------- ------ -------- ----------
3 prod3 5 5.26

What you would gain from this inheritance setup is this: when you
insert data or need item type specific data you can go to the child
tables. But, when all you need is data that for all items that is
common to all items, then you can go to the parent table. Note that if
you foresee you data set getting extremely large (think many millions of
rows) on a high volume db, this will become unwieldy as when you do a
"global" select using the parent table what you are effectively doing is
a UNION ALL on the common columns parent and child tables. The other
advantage to this scheme is that if you want to add more globally common
attributes (columns) to your items, you can do it in one place (the
parent table) and it will propagate down to the child tables.

--
erik jones <erik(at)myemma(dot)com>
software development
emma(r)