Method of recursive objects for representing hierarchies in relational database systems

• US 7,548,935 B2
• Filed: 05/02/2003
• Issued: 06/16/2009
• Est. Priority Date: 05/09/2002
• Status: Expired due to Fees

• Current Assignee: Robert Pecherer
• Original Assignee: Robert Pecherer
• Inventors: Pecherer, Robert
• Primary Examiner(s): Jalil; Neveen Abel
• Assistant Examiner(s): Pulliam; Christyann R
• Application Number:
  US10/428,594
• Publication Number:
  US 20040083222A1
• Time in Patent Office: 2,237 Days
• Field of Search: 707 1- 10, 707100-1041, 707200-202, 709215-218, 345/968, 715/513, 705 1- 2, 705 26- 27
• US Class Current: 1/1
• CPC Class Codes: G06F 16/284 Relational databases
• CPC Class Codes: Y10S 707/99944 Object-oriented database structure

Abstract

A method is described for creating and managing a two-level hierarchical data structure. Parent and child object relation tables, an aggregate relation table and a link relation table having records are created wherein each link relation record has an aggregate identity field and a child object identity field and the aggregate relation records have a parent object identity field pointing to a parent object relation record. The link relation records have an aggregate identity field pointing to an aggregate relation record and to a child object relation record whereby zero or more child object relation records are linked to a parent object record relation record.

Claims

1. A method of recursive objects for constructing a representation of at least a first two-level hierarchy in a database of a Relational Database Management System (RDBMS) associating a parent record in a parent table in said database with a first plurality of child records in a child table in said database, said parent table containing a plurality of parent records each having a plurality of fields including a uniquely valued parent record identity field and each child record in said plurality of child records having a plurality of fields including a uniquely valued child record identity field, comprising the steps of:

(a) creating an aggregate table in said database for inserting aggregate records having a plurality of fields including at least a uniquely valued aggregate record identity field and a parent record identifier field for identifying a parent record;

(b) creating a link table in said database for inserting link records having a plurality of fields including at least a uniquely valued link record identity field, an aggregate record identifier field for identifying an aggregate record and a child record identifier field for identifying a child record;

(c) constructing the representation of said first two-level hierarchy by;

(i) inserting a first aggregate record into said aggregate table with said aggregate record identity field set to a unique first aggregate record identity value and said parent record identifier field set to the value of said parent record identity field in said parent record;

(ii) following step (c)(i), inserting a plurality of link records into said link table, one link record for each child record in said first plurality of child records with the respective value of said link record identity field set to a unique link record identity value, the respective value of said aggregate record identifier field set to said first aggregate record identity value and the respective value of said child record identifier field set to the value of said child record identity field of the respective child record, wherein the association of said first aggregate record with said parent record and the association of each link record with both said first aggregate record and the respective child record associates said parent record with the respective child record in said first two-level hierarchy, whereby said parent record is associated with said first plurality of child records in said first two-level hierarchy, and

(d) retrieving all associations of one parent record and one child record associated in said first two-level hierarchy using a first query over said parent table, said aggregate table, said link table and said child table, said first query restricting retrieval to all associations of one parent record and one child record associated by an aggregate record and an associated link record.

2. The method of claim 1, comprising the further steps of:

(e) constructing the representation of a second two-level hierarchy associating said parent record in said parent table with a second plurality of child records in said child table by;

(iii) inserting a second aggregate record into said aggregate table with said aggregate record identity field set to a unique second aggregate record identity value and said parent record identifier field set to the value of said parent record identity field in said parent record, and;

(iv) following step (e)(iii), inserting a plurality of link records into said link table, one link record for each child record in said second plurality of child records with the respective value of said link record identity field set to a unique link record identity value, the respective value of said aggregate record identifier field set to said second aggregate record identity value and the respective value of said child record identifier field set to the value of said child record identity field of the respective child record, wherein the association of said second aggregate record with said parent record and the association of each link record with both said second aggregate record and the respective child record associates said parent record with the respective child record in said second two-level hierarchy, whereby said parent record is associated with said second plurality of child records in said second two-level hierarchy.