Version-enabled, multi-typed, multi-targeting referential integrity relational database system and methodology

A relational database is disclosed which includes a number of target tables each exhibiting a different type, such as a general contact table, a personal contact table and a department contact table. The relational database includes a super-column which is divided into a number of columns corresponding to the number of types of target tables. A respective column is allocated in the super-column for each of the types of the target tables. Valuable system memory space is consequently conserved. Multiple-typed tables and multi-targeted tables are supported, and multi-version data storage capabilities are provided.

FIELD OF THE INVENTION

The disclosures herein relate generally to content management systems and more particularly to relational databases.

BACKGROUND

A relational database typically includes several tables with rows and column populated with data such as the CONTACT TABLE of FIG.1A and the NEWSLETTER TABLE of FIG.1B. The primary key field of a table typically refers to a column in the table which is unique to that table and which functions as an index for that table. The actual primary key value for each record of the table must contain data unique to that particular record. InFIG. 1Athe CONTACT ID field is the primary key and inFIG. 1Bthe NEWSLETTER ID field is the primary key. Two tables can be linked together by another table called a linking table such as the CONTACT-NEWSLETTER LINKING TABLE of FIG.1C. Since a linking table relates one table to another table, the term “relational database” is used to describe the resultant database.

A “foreign key” relationship exists between the CONTACT-NEWSLETTER LINKING TABLE of FIG.1C and the CONTACT TABLE ofFIG. 1Abecause the LINKING TABLE ofFIG. 1Cincludes the primary key “CONTACT ID” from the CONTACT TABLE of FIG.1A. Likewise, a “foreign key” relationship exists between the CONTACT-NEWSLETTER LINKING TABLE of FIG.1C and the NEWSLETTER TABLE ofFIG. 1Bbecause the LINKING TABLE ofFIG. 1Cincludes the primary key “NEWSLETTER ID” from the NEWSLETTER TABLE of FIG.1B.

The CONTACT TABLE of FIG.1A and the NEWSLETTER TABLE of FIG.1B. are referred to as “parent tables” because they are the source of the primary keys which populate the LINKING TABLE of FIG.1C. The LINKING TABLE ofFIG. 1Cis referred to as the “child table” since it contains the foreign keys which point back to the parent tables. The child table is dependent because its foreign key values are constrained to be values of the primary keys in the parent tables.

In relational databases, foreign keys are widely used in linking tables to establish a special referential relationship between two tables. However, foreign keys have several limitations in a real-world referential relationship between two tables. These limitations include no data definition support for allowing multiple parent tables and no data modification support for allowing multiple parent tables. Moreover, foreign keys do not provide version support.

What is needed is a mechanism and methodology for removing these limitations when using a foreign key to establish a relationship between tables.

SUMMARY

Accordingly, in one embodiment a database is disclosed including a plurality of first target tables exhibiting different table types. The database system further includes a source table including a plurality of records, each record having a common super-column including a plurality of sub-columns. At least one of the sub-columns of a particular is record references one of the table types of the plurality of first target tables.

Principal advantages of the embodiment disclosed herein include support for multi-typed target tables, multiple targeting and multiple versioning as explained subsequently.

DETAILED DESCRIPTION

When a conventional relational database system (RDBS) is created, foreign keys are statically defined. In other words, a particular foreign key in a child table can not point to one parent table under certain conditions and then point to another parent table under other conditions. A particular foreign key can only point to one parent table for the life of that table. In this respect, the conventional foreign key is regarded as being “single-typed”. There is no data definition support for allowing a conventional foreign key to point to multiple parent tables during data definition time. Data definition time is defined to be the time when tables in an RDBS are initially set up, namely the time before such tables are populated with data.

Moreover, during “run time” such a database has no data modification support for foreign keys. Run time is defined to be the time when the already initialized tables are populated with data, record by record. Another problem encountered with the conventional RDBS is that there is no versioning capability. For example, in the contact table ofFIG. 1A, it is not possible to have two different versions of CONTACT ID1or two different versions of CONTACT ID2.

As seen inFIG. 1A, once the tables of this conventional RDBS are created during data definition time and then populated with data at run time, the RDBS is static in the sense that it is difficult to modify the database to add support for multiple contact types or multiple versions. There is simply one contact type, namely “CONTACT ID” and one version of each CONTACT ID record. One possible solution for adding support of multiple data types is shown inFIG. 2as RDBS100. ComparingFIG. 2toFIGS. 1A-1C, the contact table ofFIG. 1Ahas been divided into three separate table types designated GENERAL CONTACT table102, PERSONAL CONTACT table104and DEPARTMENT CONTACT table106. Contact tables102,104and106employ the same fields as the contact table of FIG.1A. RDBS100also includes a NEWSLETTER table108employing the same fields as the newsletter table of FIG.1B. Since the contact table is now divided into three contact type tables102,104and106, the LINKING table110is likewise divided into multiple columns as shown in FIG.2. Each of the contact ID columns ofFIG. 2, namely GENERAL CONTACT ID, PERSONAL CONTACT ID and DEPARTMENT CONTACT ID correspond to a different contact type table102,104and106, respectively. Unfortunately, when LINKING table110is so divided and populated with foreign key contact type information (indicated by asterisks) many of the fields are left unpopulated. Such an approach, while functional, wastes a large amount of valuable storage space and is thus inefficient.

To solve the problems discussed above,FIG. 3Ashows an RDBS200which is capable of multiple types, multiple targeting and multiple versioning as will subsequently be explained. RDBS200includes a source table202, a portion of which is shown as having a “super-column”204which is divided into a plurality of sub-columns, for example 4 sub-columns in this particular embodiment. As seen inFIG. 3A, super-column204includes “target table name” sub-column204A, “target row ID” sub-column204B, “referential integrity (RI) row ID” sub-column204C and “target version number” sub-column204D. By including this information in super-column204, source table202is provided with great flexibility in being able to target multiple types of target records among target tables212,214and216. While in the present example, we have zoomed in on super-column204for discussion purposes, it should be understood that source table202will in actual practice include other columns which are defined by the user as desired.

A relational database view function can be used to make it appear to the user that super-column204is one column, when in actual practice it is implemented as multiple sub-columns in this particular example. From the user's perspective, super-column204will appear as a reference, namely reference206as indicated in FIG.3A. Reference206represents any contact and is capable of pointing to any of the contact tables such as GENERAL CONTACT table212, PERSONAL CONTACT table214and DEPARTMENT CONTACT table216. Stated alternatively, the “any contact” reference206associated with source table202is capable of pointing to any of target contact tables212,214and216.

Super-column204provides both a multi-type feature and a multi-targeting feature. It is noted that the multi-type feature is similar to the multi-targeting feature. What is important is that reference206, although single-valued, can point to different types (tables) and different targets (records). At run time RDBS200exhibits a multi-type feature because source table202and its super-column204can point to multiple types of tables, for example one of contact tables212,214and216. More particularly, “target table name” sub-column204A of the super-column can point to these different table types as desired by the user. RDBS200also exhibits a multi-targeting capability at run time because its super-column204can point to multiple targets within tables212,214and216. For example, in more detail, “target row ID” sub-column204B can point to such multiple different targets.

This solves two very significant problems associated with foreign keys, namely that they can not point to multiple type tables once the tables are set up and that they can not point to multiple targets once the tables are populated with data. While tables212,214and216correspond to different types or species, namely a general contact table, a personal contact table and a department contact table, respectively, they are related in that they are of the same genus, namely they are all contact tables. The usage of “referential integrity (RI) row ID” sub-column,204C and “target version number” sub-column204D are discussed later.

FIG. 3Billustrates an alternative embodiment of the disclosed RDBS as RDBS300. RDBS300is similar to RDBS200ofFIG. 3Awith like numbers indicating like elements. RDBS300is different from RDBS200in that it's source table302includes two super columns, namely super-column204(already discussed above) and a second super-column304. RDBS300is also different from RDBS200in that it includes 3 types of target newsletter tables, namely SHORT NEWSLETTER table312, MEDIUM NEWSLETTER table314and LARGE NEWSLETTER table316. To accommodate the multiple types or multi-typing of newsletter tables312,314and316, RDBS300includes an “ANY NEWSPAPER ID” super-column304which is divided into a plurality of sub-columns, for example 4 sub-columns in this particular embodiment. As seen inFIG. 3B, super-column304includes “target table name” sub-column304A, “target row ID” sub-column304B, “referential integrity (RI) row ID” sub-column304C, and “target version number” sub-column304D. By including this information in super-column304, source table302is provided with great flexibility in being able to target multiple types of target records among target tables212,214and216, as well as target tables312,314and316. While in the present example, we have zoomed in on super-column304(and super-column204) for discussion purposes, it should be understood that source table302will in actual practice include other columns which are defined by the user as desired.

A relational database view function can again be used to make it appear to the user that super-column304is one column, when in actual practice it is implemented as multiple sub-columns in this particular example. From the user's perspective, super-column304will appear as a reference, namely reference306as indicated in FIG.3B. Reference306represents any newsletter type and is capable of pointing to any of the newsletter tables such as SHORT NEWSLETTER table312, MEDIUM NEWSLETER table314and LARGE NEWSLETTER table316. In other words, the “ANY NEWSLETTER ID” reference306associated with source table302is capable of pointing to any of target newsletter tables312,314and316. Multi-typing and multi-targeting is thus provided by source table302via its “any newsletter” super-column304. If desired, super-column304could also be programmed to point to any of the contact tables such as GENERAL CONTACT table212, PERSONAL CONTACT table214and DEPARTMENT CONTACT table216. Great flexibility is provided by this RDBS architecture.

FIG. 4illustrates an RDBS400similar to RDBS300of FIG.3B and including a versioning capability. For example, rather than simply overwriting an old version of a contact with a new version of a contact, RDBS400saves and keeps track of older versions. Super-column204is version-enabled meaning that the super-column not only tracks the contact type and target in one example, but also tracks the particular version of the designated target contact type. More specifically, “target version number” sub-column204D points to a particular contact type version in one of target tables212,214or216. The actual version information is stored in the designated VERSION field222,224or226, respectively, of these target tables. In one embodiment, contact target tables212,214and216store the most recent version, the oldest (original) version or any specific version of the contact information. The VERSION field222,224and226stores the version information while the remaining fields of target contact tables212,214and216store the actual contact data.

From the user's perspective, super column204will appear as a reference, namely version-enabled reference206at source table302as indicated in FIG.4. Reference206represents any contact and is capable of pointing to any of the contact type tables (target tables) such as GENERAL CONTACT table212, PERSONAL CONTACT table214and DEPT. CONTACT table216. Version-enabled reference206also points to the particular version of the contact information desired whether it be the most recent version, the original version or a particular specific version. Multi-typing, multi-targeting and versioning capability are thus provided by source table302via its “any contact” super-column204operating in the database in conjunction with target contact tables212,214and216.

In more detail, it is noted that a reference such as version-enabled reference206uniquely identifies a referential relationship between a source row in source table302and a target row component in the target tables212,214and216. A source row may include zero or more references. A reference may be single-valued (i.e. referencing one target row) and is free to point to any row of any target table. A reference includes on-insert, on update, and on delete referential integrity on source rows which is maintained by the database system. Moreover, a reference has on-delete referential integrity (restrict, no action, set null, or cascade) on target rows which is also maintained by the database system. A reference also exhibits versioning referential integrity (referring to a specific or most current version) on both source and target rows which is again maintained by the database system. This referential integrity is specified during run time.

As mentioned earlier, super-column304is similar to super-column204and can be populated to point to multiple types and targets in target tables212,214,216,312,314and316. Each of these tables includes a version field. Target tables312,314and316include version fields322,324and326. In actual practice source tables may include other columns and rows in addition to super-columns204and304as desired by the user or others. For example,FIG. 5shows an RDBS500that includes a source table510which is a linking table. Source table510is also called a linking source table. Linking source table510includes multiple rows and columns. The records520,530,540,550,560and570include super columns such as204and304. Moreover, these records include other columns in addition to columns204and304as desired by the user or others. The super-columns in the records of source table510exhibit the multi-typed, multi-targeting, multi-versioning features described above. Adjacent records520and550can together be considered a record of source tables510. Likewise adjacent records530and560can together be considered a record of source table510.

FIG. 6shows the relation between a representative source row520of source table510and a representative target row610of target table212in more detail. While there is a direct path620between source row520and target row610, there is also a referential integrity path630through Referential Integrity Table of which a representative RI row640is shown. While the actual RDBS employs a source table, an RI table and target table, or a plurality thereof, for simplicity a source row520, an RI row640of an RI table and a target row610of target table212have been shown inFIG. 6instead of the entire table structures. The RI table is used to maintain referential integrity of all the references that exist in the data base system. Each row (such as row640) in the RI table represents a reference which is composed of source and target row information.

In path630, instead of pointing directly from source row520to target row610, source row520points to RI row640of the RI table for the purpose of maintaining referential integrity. However, direct path620provides high execution speed while RI path630maintains referential integrity. The path through RI row640and its RI table is transparent to the user.

To maintain referential integrity, target row610is synchronized with RI row640of the RI table. Whenever there is a delete in the target table, there is a corresponding delete in the RI table. Once this synchronization between the target table and RI table is carried out for a particular row, further synchronization between the RI table and the source table is conducted. From the source table to the RI table, a foreign key-like relationship is defined from the RI ROW ID (one column of the super-column) to the RI column (a primary key) in the RI table. However, from the RI table to the target table, there is no foreign key relationship. (It will be recalled that RI ROW ID is one sub-column of super column204, namely “referential integrity (RI) row ID” sub-column204C, as shown in FIG.3A.

Even though there is no foreign key relationship between the target table and the RI table (e.g. between target row610and RI row640in FIG.6), there is a target trigger650monitoring changes made to the target table. When target trigger650detects that a delete or other change has been made to the target table, it instructs the RI table to make the same delete or other change in the corresponding record in the RI table. Changes made to the RI table are cascaded back to the source table of which source row520is a member. It is noted that target trigger650includes trigger logic which is dynamically attached to the target table as needed to synchronize deletions and changes between the target and RI tables. These deletions and changes include version deletions and version changes.

A source trigger660is attached to source table510which includes source row510as shown in FIG.6. Source trigger660takes care of forward deleting the corresponding RI row when a source row is deleted. To summarize trigger operation, as described above, on delete and update triggers are attached to source and target tables for maintaining global referential integrity. Source table triggers are statically created during source table generation whereas target triggers are dynamically created.

FIG. 7is a block diagram of an information handling system or computer system700on which relational database systems (RDBS's)200,300,400or500are operated. More particularly, computer system700includes a client computer710which is coupled20to a server computer or host720. RDBS's200,300,400or500are installed on server or host computer720as shown. Client computer710sends queries to the RDBS in server computer710and receives results back from that RDBS.

The disclosed content management system can be stored on virtually any computer-readable storage media, such as CD, DVD and other magnetic and optical media in either compressed or non-compressed form. Of course, it can also be stored on a server computer system or other information handling system.

The principal advantages of these embodiments are the providing of a relational database system and methodology which permits multi-typing, multi-targeting and multi-versioning while conserving valuable memory space in the host server system.