© 2002 International Business Machines Corporation. All rights reserved.

Introduction

The object-oriented (O-O) approach to programming was a revolutionary approach developed in the 1980s [3]. In the 1990s, integration with an O-O architecture became an important area of research and development for relational database management systems (DBMSs) [1,7]. In 1996, Informix delivered Informix Universal Server as its object-relational (O-R) database. Of course, the traditional relational model is still an important and vital model. Nevertheless, there are a growing number of problems that are far from being properly solved by means of relational or object-relational DBMSs.

In this article, I will briefly describe how some problems are not ideally solved by relational or object relational features. I will then describe a data structure called an Objects Tree that can help overcome these limitations.

Limitations of relational databases

In a relational model, objects described in records have relationships with some attributes. The number of attributes (fields) in the table differs from several to tens and even hundreds. As the record gets wider (more and longer attributes), performance can degrade because the longer the length of the string, the more input/output operations are required to read and write records to disk. Dividing a wide table into several tables with fewer attributes requires joins, which can sometimes be expensive.

Indexes can't always help either. Indexes are most effective on a result set with a large number of records because of the exponential dependence between search speed and number of records:

(1) exp (v_i) ~ n
or
(2) v_i ~ ln (n)

On the other hand, dependence between sequential selecting speed and the size of selection is proportional:

(3) v ~ n #xb7 k

v - speed of sequential selection
v_i - searching speed using index i
n - number of records in the table
k - number of columns in the table

Indexes are more effective on large number of narrower records rather than on wide records [5]. In addition, indexes have their own costs, including:

• The more attributes (columns in the table), the more indexes to create.
• Additional disk space is needed for every index (in practice, the size of indexes may exceed the size of the indexed table).

When data is modified or the table is altered, statistics must be updated or the whole index tree must be reconstructed.

Another limitation of relational model is the "flattening" of an object's attributes. This means that attributes can't be grouped or structured; instead, all of the attributes in relationship are on the same level of the "hierarchy". In practice, attributes of an object are not all of equivalent importance and should belong to different structure levels.

There are of course additional data types, such as BLOBs, CLOBs, and user-defined types as well. However, these present their own limitations. For example, BLOBs and CLOBS cannot be ordered or indexed and must be processed on the client side.

Taking advantage of object-relational features

Informix Universal Server [2,6] is an (O-R) database that embeds O-O technology into popular Informix Dynamic Server. It includes the following features to help us solve our problem:

• Abstract data types
• Inheritance
• Data and type hierarchies

In practice, there are many examples where a standard set of types is insufficient. Although abstract data types do provide some advantages, defining new types into the DBMS is not always an ideal solution - there always will be a problem that needs yet another data type.

One possible disadvantage of defining new types, which you can do in Informix Universal Server, is that the types require the skill of experienced programmers to develop C language methods for data storage, access, and indexing.

The property of inheritance does not ideally solve the problem of great number of attributes being hierarchical. Sometimes the problem of adding a new attribute arises during database runtime causing modification of table structure.

Lastly, one attribute can require the use of a set of other attributes. If we try to solve this problem by creating several tables, the database structure is too complicated, query execution slows down, and data errors are more likely.

Back to top

A hierarchical structure of objects

I am proposing an Objects Tree [4] as an information system for storing a hierarchical structure of objects. The problem is further complicated by enabling objects to have any number of attributes, varying from tens to hundreds or more, and each attribute can be of any Informix data type (integer, float, decimal, string, datetime, etc.). This data structure can handle that complexity.

This system is based on Informix Universal Server and Informix Web DataBlade^TM sets table hierarchies and data type extensibility. But standard methods of access are used, meaning we don't have to program methods. This is why the data structure can work on both Informix Universal Server and Informix Dynamic Server platforms.

The data structure shown in Figure 1 has been created in Informix DBMS by means of SQL scripts and consists of data tables described below. Stored procedures have been developed to modify these tables. The script for creating tables is included in a separate file and also includes sample stored procedures for updating the objects_tree table.

The objects_tree table

The table objects_tree contains information about both parent objects and child objects of the hierarchical tree.

Table 1. The objects_tree table

The attributes (columns) of objects_tree are:

obt_id - the object ID for the row
dlv_id - A reference to the table of division IDs
obj_name - Any text
ch_ar - An archive record flag character used for storing information about deleted objects

Stored procedures are used to add, modify, and delete objects. For deleting, there is no recursive removal of objects that have children; that is, if those children refer to parents, the parents cannot be deleted. Child objects must be deleted first, or an SQL error code is raised from within procedure. This is done for data safety. If we allow recursive deletion of child objects in the stored procedure, it increases the probability that a whole branch of the object tree could be accidentally deleted.

The objects_parents table

Table 2, the objects_parents table, contains references both to parent and child records in the objects_tree table, creating the correspondence between parent and child objects.

Table 2. The objects_parents table

Both the child and parent code ID fields refer to the objects_tree table. New relationships between objects can be added by a stored procedure. For better performance, parent and child objects can be assigned simultaneously, but no modification is available. If modification is needed, a corresponding record must be deleted directly by SQL statement DELETE.... FROM...., and then a new record is added by stored procedure call.

Every object in the database structure can have arbitrary number of children and parents, as is demonstrated in Example 1.

Example 1. In the hierarchical structure of university, department is a child of institute and a parent of laboratories at the same time as shown in the Table 3 and Table 4, below.

Table 3. The objects_tree table

Table 4. The objects_parents table

Back to top

Classifying hierarchical objects

Object classification is assigned in the following linked tables (as shown in Figure 1):

• object_classes
• eq_classifiers
• eq_classes
• eq_class_link
• eq_class_parms

The object_classes table is intended for storing information about object classes. An object class is linked with a list of attributes (sometimes called "parameters"). Parameters values, or links to corresponding reference tables, are also available in the database structure.

The object_classes table

The object_classes table (Table 5) contains records that link object descriptions with classifier positions. Every object can have several links with classifiers, but those classifiers must be different. Uniqueness of such kind is provided by a special index in database. Users with no database access (DBA) privileges have only rights to data selection. Any operations of data modification can be executed only through special stored procedures.

Table 5. The object_classes table

• eqcs_id. A reference to the eq_classifiers table- it is the object's classifier.
• eqc_id. A reference in table eq_classes to the class to which this object is assigned.
• valid. The validity of correct objects classification used in automatic adding of object-class links for equivalent classes establishment (see description of Table 8, eq_class_link). The valid field can contain 'y' or 'n,' depending on the necessity of object-to-class establishment.

You can link existing objects to a class and classifier (add a record to the table) by executing a stored procedure. Different stored procedures modify existing object-to-class assignment (re-classifying) and remove links (canceling object classification).

When objects are reclassified, the field valid is automatically assigned with value 'y'. When an object classification is removed, all links of the specified class are deleted if the object code input parameter is null, and all links of the object are deleted if the code of classifier position is not specified.

The eq_classifiers table

Table 6, the eq_classifiers table, contains codes and names of classifiers used for classifying objects. Their descriptors are located in the hierarchical objects_tree table.

Table 6. The eq_classifiers table

Names of classifiers are unique. eqcs_sign is the signature of the classifier, and can contain any character string in upper case, or null. If not null, string value must be unique. Special triggers check for uniqueness. Users without DBA privileges can only select data.

Stored procedures are used for modifying data. Besides adding the new classifier, a root class for this classifier is added to eq_classes. This root class has the same name as classifier itself and value classlevel is set to 0. For the procedure that deletes classifiers, all of its child classes are removed.

The eq_classes table

Table 7, the eq_classes table, contains a hierarchy of classifier elements used for object classification. Their descriptors are located in the hierarchical objects_tree table.

Table 7. The eq_classes table

Names of elements having the same parent are unique.

eqc_sign. The signature of the class, consisting of any character string in upper case, or null. If not null, the string value must be unique when concatenated with the value in eqcs_id. Triggers are used to check this uniqueness. Users with no DBA privileges have only rights to data selection.

Any operations of data modification can be executed only through stored procedures. In the case of inserting, a new element is added as child for specified parent one. In the case of editing, the procedure modifies the name and also tries to edit name of the child for specified parent. If this new parent is bound to another classifier, then editing element together with all its child elements and all links of those elements are re-assigned to classifier specified by parent_id. In the case of deleting, the classifier element with all possible child elements and links with objects are removed recursively. If class references to the root classifier element (without parents), then the whole classifier is deleted as well as its descriptor in the eq_classifiers table.

The eq_class_link table

Table 8, the eq_class_link table, specifies equivalent classes. Equivalence means that all of the objects assigned to primary class (that is, the class in which they have been created) will be assigned to an additional class as well. Primary or additional classes are assigned to objects in the object_classes table.

Table 8. The eq_class_link table

Both eqc_id_from and eqc_id_to contain references to the eq_classes.eqc_id field. Class equivalence is established by a stored procedure. On deleting links between classes, the procedure automatically removes records that assigning objects of a primary class to an additional class in table object_classes. Users without DBA privileges can only select data.

The eq_class_parms table

Table 9, the eq_class_parms table, stores a list of parameters for classes and sub-classes of objects. The table contains links that determines the list of available parameters for a class or sub-class and establishes links between the parameters list of a specified class in the eq_param_descr table and the class in the eq_classes table.

Table 9. The eq_class_parms table

Records are added to the table by a special stored procedure that returns the eqcp_id value of the record that was added.

Example 2. For the classification of university using objt_id values of Example 1 (that is, the value 4), the hierarchy of classes can be assigned as shown in following tables.

Table 10. The eq_classifiers table

Table 11. The eq_classes table

Table 12. The object_classes table

Back to top

Modeling the attributes

The proposed data structure can store an arbitrary number of attributes (object parameters) with their values of various type in the following tables:

• Table 13, eq_param_descr
• Table 14, eq_param_refs
• Table 15, eq_param_values

The eq_param_descr table

The eq_param_descr table is a catalogue of parameter descriptors. There are two ways of getting the real value of a parameter depending on the value of eqpref_id:

• If eqpref_id is null, the value of parameter must be specified explicitly and in any case is stored in the eq_param_values table.
• If eqpref_id is not null, the eq_param_values table must contain a link to the record in the external reference table. The value itself is retrieved from the contents of the record in reference catalogue eq_param_refs; that is, a reference to table name, field name used as primary key, and field name with data values (see description of tables eq_param_refs and eq_param_values).

Table 13. The eq_param_descr table

• eqp_type. Contains the signature of Informix standard types; that is, int, float, nchar, date, dtime, money, intrv.
• meas_id. A reference to the table of measure units, or null.
• eqpref_id. A reference to the table eq_param_refs, or null.
• prm_sign. A signature of descriptor containing any character string in upper case for data selection convenience, or null. If not null, the string value must be unique. A special trigger checks for uniqueness. Adding, editing, and, deleting records is done through stored procedures.

The eq_param_refs table

The eq_param_refs table contains references used in descriptions of parameters.

Table 14. The eq_param_refs table

Before establishing a link between the existing reference table and object parameter list, a link table must be previously created with the name consisting of prefix '_l_' and the reference table name. The name of link table can be generated through a stored procedure that receives the reference table name as an input parameter and returns the string value of the link table name. The link table must contain two fields; one is the serial key of the reference table and the other is the serial key of the eq_param_values table. After the link table has been created, records can be added to and deleted from the table eq_param_refs by using stored procedures. Adding or deleting records in the link table are executed directly by corresponding SQL statements.

Example 3. Here is how a link table called _l_bux_inv_num is created. The purpose of this link table is to store IDs of the reference table called bux_inv_num with corresponding IDs of the eq_param_values table (see description of tables eq_param_refs and eq_param_values):

 
				

CREATE TABLE _l_bux_inv_num ( 
in_id		INTEGER, 
eqpv_id	INTEGER UNIQUE 
); 
 
ALTER TABLE _l_bux_inv_num ADD CONSTRAINT FOREIGN KEY (eqpv_id) 
REFERENCES eq_param_values (eqpv_id) CONSTRAINT fk1; 
 
ALTER TABLE _l_bux_inv_num ADD CONSTRAINT FOREIGN KEY (in_id) 
REFERENCES bux_inv_num (in_id) CONSTRAINT fk2; 

The eq_param_values table

Table 15, eq_param_values, stores objects parameters values.

Table 15. The eq_param_values table

The values of all parameters must be in text form. For correct interpretation of value selected, use the signature of the specified type of the parameter from the eq_param_descr table.

i_value and f_value contain the same parameter value but of integer and float types respectively. Storing, modifying and removing their values are executed through stored procedures and triggers that convert, if possible, string data types into integer and float, and vice versa. If such conversion is impossible, i_value and f_value are assigned null. This enables a quick search of records that have corresponding data types.

Working with string notation of parameters types and their values (such as float, datetime, etc.) depends on the setting of locale (that is; decimal and datetime separator). Those data conversion may cause an error if parameters are updated on one computer and selected on another with different locale settings.

Example 4. The following tables show you how to store parameter values of various type using departments objt_id data of Example 1, classifier eqc_id codes of Example 2, and after creating link table _l_bux_inv_num of Example 3.

Table 16. The eq_param_descr table

Table 17. The eq_class_parms table

Table 18. The eq_param_refs table

Table 19. The _l_bux_inv_num table

Table 20. The eq_param_values table

Back to top

Creating views for the object data structure

For convenient data access in an application, I strongly recommend creating the necessary views. Data type conversion may be executed through select statement in such views with the help of stored procedures.

Example 5. Here is how to creating a view v_obj to be used for selecting information about departments:

 
				
CREATE VIEW v_obj 
    (objt_id,dlv_id,obj_name,ch_ar,in_id,inv_num,date_born,chief_name) 
AS 
SELECT t1.objt_id, t1.dlv_id, t1.obj_name, t1.ch_ar, 
    (select {+INDEX(v 'ix1')} i_value 
     from eq_param_values v, eq_param_descr d 
     where v.objt_id=t1.objt_id 
	 and v.eqpdes_id=d.eqpdes_id 
	 and d.prm_sign='INV_NUM')in_id, 
    (select inv_num from bux_inv_num 
     where 
     in_id=(select {+INDEX(v 'ix1')} 
	 i_value from eq_param_values v, eq_param_descr d 
     where v.objt_id=t1.objt_id 
	 and v.eqpdes_id=d.eqpdes_id 
	 and d.prm_sign='INV_NUM'))inv_num, 
    (select {+INDEX(v 'ix1')} str2date(value) 
	 from eq_param_values v, eq_param_descr d 
     where v.objt_id=t1.objt_id 
	 and v.eqpdes_id=d.eqpdes_id 
	 and d.prm_sign='DATE_BORN')date_born, 
    (select {+INDEX(v 'ix1')} value[1,40] 
	 from eq_param_values v, eq_param_descr d 
     where v.objt_id=t1.objt_id 
	 and v.eqpdes_id=d.eqpdes_id 
	 and d.prm_sign='CHIEF')chief_name 
FROM 
  objects_tree t1, eq_classes t2, object_classes t3, 
  objects_tree t4, objects_parents t5 
WHERE	t2.eqc_sign='DEP' AND 
	t3.objt_id=t1.objt_id AND 
	t4.objt_id=t5.prnt_id AND 
	t5.chld_id=t1.objt_id AND 
	t2.eqc_id=t3.eqc_id 
WITH CHECK OPTION; 
 
GRANT SELECT ON v_obj TO PUBLIC; 

In the v_obj view, departments are filtered from other objects by the condition eqc.sign='DEP' (see Example 1 above). Objects attributes such as inventory full name, date of foundation, and chief name are selected by prm_sign signatures that are INV_NUM, DATE_BORN, and CHIEF respectively. For the inv_num attribute (inventory full number), the in_id code as well as inv_num field are selected from the bux_inv_num reference table. The user-defined function str2date is used to convert the date_born string (date of foundation) into a datetime value, taking into account the datetime separator locale setting. For better database performance, only the first 40 characters of chief_name string parameter (chief of department) are selected.

Selecting from v_obj view gives the list of departments with their attributes as shown below.

 
SELECT obj_name, inv_num, date_born, chief_name FROM v_obj 

Table 21. SELECT results

Back to top

Successful use in an implementation

The data structure I presented in this article consists of a mixture of relational, object-oriented, and hierarchical structures. It is intended for storing hierarchically multilevel objects having an arbitrary number of attributes (parameters) of various types as well as their classifications. The database elements can be created both on Informix Universal Server and Informix Dynamic Server platforms to solve common known information systems problems.

This object data structure has been successfully implemented at a petroleum-refining plant to handle the problem of keeping accounting and repair scheduling of operating environment of various types, including petrochemical storage tanks, pressure vessels, heating furnaces, compression pumps, safety valves, pipelines, building constructions, hoisting and handling machinery, electrical and other auxiliary equipment, as well as manufacturing personnel.

The classification of operating environment is both multipurpose and multilevel. For example, the classifier of production equipment on the basis of divisions has more than four levels of hierarchy, including enterprises, workshops, installations or work cells, units, and even their components. The total number of attributes encountered for any type of environment is in the order of 100, whereas the average base of installation units is about 30,000 in each of 80 workshops.

The advantages of the proposed data structure in comparison with traditional analogues are as follows:

• The data structure and stored procedures are ready to be used by client-server applications, sparing the developer's time from creating database tables, indexes, foreign key constraints, references between tables, and typical stored procedures of data updating.
• Database performance is not particularly sensitive to the number of accounting records (as shown in expressions (1-3) above) and has been proven for four years in a database running under industrial enterprise conditions.
• New classes and classifiers can easily be assigned to the existing classification of available objects.
• You can add hierarchical objects and classifiers without altering the data structure.
• You can add new attributes for existing objects, either by assigning values directly or by linking through a reference table.
• You can add and modify attributes without changing the database structure (adding columns, creating new indexes) or to the application code.
• The data structure is supported with a special application for viewing and even modifying the hierarchical tree of objects under various available classifiers with their list of parameter names and values.

Limitations

There are some restrictions for storing attributes of particular type, especially datetime types. (See the eq_param_values table above). Except for integer, decimal, float, and character, values of other types are storing in field 'value' as strings. For datetime values the eq_param_values table has only an integer, float, and string types. There is no datetime column, which means that dates and times must be stored as strings. The conversion of string to datetime must occur during select operations. Stored procedures are used to do that using the appropriate locale setting.

Back to top

Download

Description	Name	Size	Download method
Download the code sample:	obj_sql.zip	5 KB	FTP | HTTP

Information about download methods

Resources

• [1] P. Brown. Object-Relational Database Development: A Plumber's Guide. Informix Press (2001).
  
  
• [2] A. Grachev. "Informix Universal Server as Object-Relational DBMS." Informix Magazine / Russian Edition, 1 (1998). See also http://www.florin.ru/win/informix_magazine/1_98/5_0.html (In Russian).
  
  
• [3] M. Guttman and J. Matthews. The Object Technology Revolution. John Wiley & Sons (1995).
  
  
• [4] "Online Objects Tree (OOT) - System for Building, Agregating and Storing Hierarchical Structure of Arbitrary Type Objects." Informix Magazine / Russian Edition, 1 (1998). See also http://www.florin.ru/win/informix_magazine/1_98/4_5.html (In Russian).
  
  
• [5] A. Prokhorov. "Definition of Optimal Database Structure." Informix Magazine / Russian Edition, 1 (1998). See also http://www.florin.ru/win/informix_magazine/1_98/5_1.html (In Russian).
  
  
• [6] A. Sanchez. Informix Dynamic Server with Universal Data Option: Best Practices. Informix Press (1999).
  
  
• [7] M. Stonebraker and P. Brown with D. Moore. Object Relational Databases (2nd edition). Morgan Kaufmann Publishers (1998).
  
  

About the author

Serg V. Mescheryakov, Ph.D., graduated from Vologda Politechnical Institute, specializing in computer-aided manufacturing in mechanical engineering. Currently, he works as an associate professor at St.Petersburg State Polytechnical University. Dr. Mescheryakov can be reached at serg@imop.spbstu.ru

Comments (Undergoing maintenance)

Back to top

Trademarks  |  My developerWorks terms and conditions

Table of contents

• Introduction
• A hierarchical structure of objects
• Classifying hierarchical objects
• Modeling the attributes
• Creating views for the object data structure
• Successful use in an implementation
• Download
• Resources
• About the author
• Comments

Local resources

• IBM Innovation Center - Austin, TX
• IBM Innovation Center - Chicago, IL
• IBM Innovation Center - Dallas, TX
• IBM Innovation Center - San Mateo, CA
• IBM Innovation Center - Waltham, MA
• Technical Briefings in North America

My developerWorks community

Dig deeper into Information Management on developerWorks

DB2 pureScale

See how true transparent scaling architecture can be achieved on distributed platforms

Special offers