How good is table inheritance?

It is a good move to use the table inheritance provided by informix v 9.1, instead of using the 'classical' approach of putting the parent's OID in the child table like this: TABLE oo_parent oid INT8 primary key, <---------- The Object Identifier clsid INT8 <------- A class identifier to support polymorphism TABLE oo_child oid INT8 primary key, <------- The same value for parent/child oidParent INT8 References oo_parent(oid), <---------- A reference to the parent clsid INT8 At this moment whe're using this approach, and I'm not sure to change to the table inheritance supported by Informix. My Questions: - What if you want to change some definition of a table that is part of an inheritance tree? Let's say you defined a table person, and suddenly you need to add a new column (and that's NOT an uncommon situation). At this moment, to alter a table in a table hierarchy you have to do download data/drop types/modify...... Any comments about the practical consequences of this? - Is it possible to know the type of the row that is obtained from a hierarchy tree? Suppose that customer inherits from person, and you make SELECT * FROM person, how to know if it is a person or a customer? What isthe best approach to achieve this using Informix table inheritance? - Is it possible to define a column of a ROW TYPE with a FOREIGN KEY? If not, what is the way to define a 1..1 relation between typed tables (objects) mantainig the referential integrity? Any comments are appreciated! Regards, Juan Ignacio Saitua.

In article <3a786b5f$1@dnewserver.firstcom.cl>, "Juan Ignacio Saitua" <jisaitua@cge.cl> wrote: > It is a good move to use the table inheritance provided by informix v 9.1, > instead of using the 'classical' approach of putting the parent's OID in the > child table like this: [ snip ] For some things, 9.X style table inheritance is very useful. But as it is implemented currently, there are a couple of significant drawbacks. > - What if you want to change some definition of a table that is part of an > inheritance tree? Let's say you defined a table person, and suddenly you > need to add a new column (and that's NOT an uncommon situation). At this > moment, to alter a table in a table hierarchy you have to do download > data/drop types/modify...... Any comments about the practical consequences > of this? This is the biggest drawback. :-( With a conventional base table, using ALTER TABLE is both easy to do and fairly well implemented by the product. With 9.X, making a change to a table is hard. It can't be done directly because of the need to change the TYPE and TABLE, and there is no ALTER TYPE for ROW TYPEs that works in the same way as ALTER TABLE. It's a lot like having no ALTER TABLE at all, with the added encumberence of having to change the type. If you anticipate making a lot of changes to the table structure, I would avoid the use of the inheritance mechanism. Implementing a better the ALTER TABLE for inheritance hierarchies isn't difficult, but it would take a fair bit of customer pressure to get it to happen. Given the design you've got above, I would judge that the 9.X table inheritance will outperform it when you have a large hierarchy and make relatively few modifications to it.(CREATE and DROP TABLE OK, ALTER TABLE quite bad) because of the indexing work that has been done. Indices and constraint rules defined for a table in the hierarchy are inherited by all tables lower down, making queries over an entire table hierarchy both easy to write, and fast to execute. > - Is it possible to know the type of the row that is obtained from a > hierarchy tree? Suppose that customer inherits from person, and you make > SELECT * FROM person, how to know if it is a person or a customer?What is > the best approach to achieve this using Informix table inheritance? The way I get folk to do this is to create an overloaded UDF for each ROW TYPE called Class_Name() that looks like this: CREATE FUNCTION Class_Name ( Arg1 Person_Row_Type ) RETURNS LVARCHAR RETURN 'Person_Row_Type'; END FUNCTION; Then the query; SELECT *, Class_Name ( P ) FROM Person P Will return the name of the ROW TYPE with each ROW. > > - Is it possible to define a column of a ROW TYPE with a FOREIGN KEY? If > not, what is the way to define a 1..1 relation between typed tables > (objects) mantainig the referential integrity? No, you can't define a column of a ROW TYPE as a FOREIGN KEY. It's important to grok the difference between TYPEs (Object Classes) and TABLES. Remember that a single ROW TYPE may be used to define multiple tables, and it is the relationship rules between the *tables* that the DBMS needs to enforce. Also, A ROW TYPE can also be used to define a variable in an SPL UDR. Instead, associate the foreign key on the TABLE, as the following example illustrates. Note that this SQL will not work in the DBMS because the other types involved (Part_Code, Product_Name, Currency) do not ship as part of the IDS 2K product. To give you some idea of why the types are important, I've included a little UDF that applies a "business rule" to pricing. This ROW TYPE would be used as the "abstract super-class" in an entire hierarchy of merchandise (Apparel, Media, Tickets, etc), and by overloading the DiscountMultiplier() UDF different rules might be applied. CREATE ROW TYPE Merchandise_Type ( Id Part_Code NOT NULL, Name ProductName NOT NULL, Movie Movie_Id NOT NULL, Description lvarchar NOT NULL, Price Currency NOT NULL, ShippingCost Currency NOT NULL, NumberAvailable Quantity NOT NULL ); GRANT USAGE ON TYPE Merchandise_Type TO PUBLIC;-- CREATE FUNCTION DiscountMultiplier ( Arg1 Merchandise_Type ) RETURNING float DEFINE crTotalCost Currency; DEFINE nNumTens INTEGER; LET crTotalCost = Arg1.ShippingCost + Arg1.Price; IF (( Arg1.NumberAvailable < 10::Quantity) OR ( crTotalCost < Currency(10.0, 'USD'))) THEN RETURN 1.0; ELIF ( crTotalCost > Currency(30,'USD')) THEN RETURN 0.75; END IF; LET nNumTens = MOD( GetQuantity ( crTotalCost ), 10); RETURN (1.0 - (0.05 * nNumTens)); END FUNCTION; -- CREATE TABLE MerchandiseOF TYPE Merchandise_Type ( PRIMARY KEY ( Id ) CONSTRAINT Merchandise_PK, FOREIGN KEY ( Movie ) REFERENCES Movies ( Id ) ON DELETE CASCADE CONSTRAINT Merch_Movies_FK ); GRANT ALL ON Merchandise TO PUBLIC; Now, any rows inserted into the Merchandise table (and there really should be none at all, because all of the actual items being sold fall into one of its more specialized sub-categories) will be checked to ensure that there is a corresponding Movie with which that Merchandise is available. Row inserted into any tables defined "UNDER" this table will also be checked to ensure that they comply with the foreign key constraint defined for Merchandise. Elsewhere in the schema, the Merchandise_Type is re-used to manage "outstanding orders", and there, it has a different set of foreign key relationships. Hope this helps. I would think carefully about the specifics of your application before using the inheritance. But it can be useful in a number of situations. KR Pb Sent via Deja.com http://www.deja.com/

Thank you again for your answer KR (or Pb.....or whatever ;-) I'm going to sit down and think about it. Regards, Juan Ignacio Saitua.