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" ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend
