Thread: Trying to Understand Table Inheritance
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, Keary Suska Esoteritech, Inc. "Demystifying technology for your home or business"
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@myemma.com>
software development
emma(r)
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
--- Jeff > 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. Then if you wanted, you could add one additional layer to for convienence. you can create multiple updateable views, one view for each of your extenction tables such as mechandise in this example that are joined back to your primary table. This way you can handle each relation seperately. Regards, Richard Broersma.