IMPROVED METHOD AND DEVICE FOR CORRELATING MULTIPLE TABLES IN A DATABASE ENVIRONMENT

• US 20190324955A1
• Filed: 04/22/2019
• Published: 10/24/2019
• Est. Priority Date: 04/24/2018
• Status: Active Grant

• Current Assignee: The Von Drakk Corporation
• Original Assignee: The Von Drakk Corporation
• Inventors: von Drakk, VIktor
• Granted Patent: US 10,922,299 B2
• Time in Patent Office: Days
• CPC Class Codes: G06F 16/2282 Tablespace storage structures; Management thereof
• CPC Class Codes: G06F 16/2379 Updates performed during online database operations; commit processing
• CPC Class Codes: G06F 16/245 Query processing
• CPC Class Codes: G06F 16/81 Indexing, e.g. XML tags; Data structures therefor; Storage structures
• CPC Class Codes: G06F 16/86 Mapping to a database

Abstract

A system, process, and method for organizing unstructured data stored in a database environment with table structure such that the data is retrievable using relational set logic, even with a database environment that does not provide relational table structures, is disclosed. The method creates, updates, and deletes database objects as necessary to describe the relationships between disparate data object types for various unique values and to provide the necessary information to retrieve the desired data objects. A device embodying and enabling the practice of the method is also disclosed.

Claims

1. A method for correlating multiple tables in a database environment comprising the steps of:

a) Initiating a computer software application comprising a database management system application capable of creating at least six tables including at least three data tables, at least one index table, at least one stitch object table, and at least one stitch map object table, the at least six tables each having a unique corresponding table identifier and the at least six tables including;

i. a first data table comprising a plurality of first data table records, the plurality of first data table records comprising a plurality of first data table fields, the plurality of first data table fields having a plurality of associated first data table field names and the plurality of first data table fields storing a plurality of first data table field values;

ii. a second data table comprising a plurality of second data table records, the plurality of second data table records comprising a plurality of second data table fields, the plurality of second data table fields having a plurality of associated second data table field names and the plurality of second data table fields storing a plurality of second data table field values;

iii. a third data table comprising a plurality of third data table records, the plurality of third data table records comprising a plurality of third data table fields, the plurality of third data table fields having a plurality of associated third data table field names and the plurality of third data table fields storing a plurality of third data table field values;

iv. an index table, the index table comprising a plurality of index table records, each index table record comprising an index data value field and an index data table name field;

v. a stitch object table, the stitch object table comprising a plurality of stitch object key-object pairs, each stitch object key-object pair comprising a stitch object key value and a corresponding stitch object data object, the stitch object data object comprising a table of unique table identifiers and corresponding unique column identifiers associated with the stitch object key value; and

,

vi. a stitch map object table, the stitch map object table comprising a plurality of stitch map object key-object pairs, each stitch map object key-object pair comprising a stitch map object key value and a corresponding stitch map object data object, the stitch map object data object comprising a table of unique table identifiers and corresponding unique row identifiers associated with the stitch map object key value;

b) initiating the computer software application comprising running the computer software application on a computer;

c) Creating a first index table in the database management system comprising a plurality of first index table records;

d) Designating the first data table as the current data table;

e) Designating a first data table field record as the current data record;

f) Designating a current value as the first data table field value of the first data table record of the first data table;

g) Searching the first index table to determine whether the current value is present in the first index value field of any of the plurality of first index table records;

h) Adding a new first index table record to the first index table, storing the current value in the first index value field of the new first index table record, and storing the unique corresponding table identifier of the current data table in the first index table name field of the new first index table record if the current value is not present in the first index value field of any of the plurality of first index table records;

i) Appending the unique corresponding table identifier of the current data table to the first table association field of the first index table record containing the current value if the current value is present in the first index value field of any of the plurality of first index table records;

j) Determining whether the current data record contains any values which have not been evaluated as in steps (g) through (i) and if so, selecting a next value from the current data record which has not been evaluated as the current value and returning to step (g), otherwise moving to the next step;

k) Determining whether the current data table contains any records containing any values which have not been evaluated as in steps (g) through (i) and if so, designating a next current data record from the current data table containing at least one unevaluated value and designating an unevaluated data value in the next current data record as the current value and returning to step (g), otherwise moving to the next step;

l) Determining whether any of the plurality of data tables contain any records containing any values which have not been evaluated as in steps (g) through (i), and if so designating a next data table containing at least one unevaluated value as the current data table, designating a next current data record from the current data table containing at least one unevaluated value and designating an unevaluated data value in the next current data record as the current value and returning to step (g), otherwise moving to the next step;

m) Creating a first stitch object table in the database management system comprising a plurality of first stitch object table key-object pairs, each of the first stitch object table key-object pairs containing a first stitch object key value and a first stitch object data object;

n) Designating the first data table as the current data table;

o) Designating a first data table field record as the current data record;

p) Designating a current value as the first data table field value of the first data table record of the first data table;

q) Searching the first stitch object table to determine whether the current value is present in the first stitch object key value field of any of the plurality of first stitch object key-object pairs;

r) Adding a new first stitch object table key-object pair to the first stitch object table, storing the current value in the first stitch object key value field of the new first stitch object table key-object pair, and storing the unique corresponding table identifier of the current data table and the data table field name of the data table field corresponding to the current value in the first stitch object table data object of the new first stitch object table key-object pair if the current value is not present in the first stitch object key value field of any of the plurality of first stitch object table key-object pairs;

s) Adding the unique corresponding table identifier of the current data table and the data table field name of the data table field corresponding to the current value in the first stitch object table data object of an existing first stitch object table key-object pair if the current value is present in the first stitch object key value field of any of the plurality of first stitch object table key-object pairs but the unique corresponding table identifier of the current data table and the data table field name of the data table field corresponding to the current value are not present in that first stitch object table key-object pair;

t) Determining whether the current data record contains any values which have not been evaluated as in steps (q) through (t) and if so, selecting a next value from the current data record which has not been evaluated as the current value and returning to step (q), otherwise moving to the next step;

u) Determining whether the current data table contains any records containing any values which have not been evaluated as in steps (q) through (t) and if so, designating a next current data record from the current data table containing at least one unevaluated value and designating an unevaluated data value in the next current data record as the current value and returning to step (q), otherwise moving to the next step;

v) Determining whether any of the plurality of data tables contain any records containing any values which have not been evaluated as in steps (q) through (t), and if so designating a next data table containing at least one unevaluated value as the current data table, designating a next current data record from the current data table containing at least one unevaluated value and designating an unevaluated data value in the next current data record as the current value and returning to step (q), otherwise moving to the next step;

w) Creating a first stitch map object table in the database management system comprising a plurality of first stitch object table key-object pairs;

x) Designating the first data table as the current data table;

y) Designating a first data table field record as the current data record;

z) Designating a current value as the first data table field value of the first data table record of the first data table;

aa) Searching the first stitch map object table to determine whether the current value is present in the first stitch map object key value field of any of the plurality of first stitch map object table key-object pairs;

bb) Adding a new first stitch map object table key-object pair to the first stitch map object table, storing the current value in the first stitch map object key value field of the new first stitch map object table key-object pair, and storing the unique corresponding table identifier of the current data table and a corresponding current data table row number corresponding to the current value in the first stitch map object table data object of the new first stitch map object table key-object pair if the current value is not present in the first stitch map object key value field of any of the plurality of first stitch map object table key-object pairs;

cc) Adding the unique corresponding table identifier of the current data table and the corresponding current data table row number corresponding to the current value in the first stitch map object table data object of an existing first stitch map object table key-object pair if the current value is present in the first stitch map object key value field of any of the plurality of first stitch map object table key-object pairs but the unique corresponding table identifier of the current data table and the data table field name of the data table field corresponding to the current value are not present in that first stitch map object table key-object pair;

dd) Determining whether the current data record contains any values which have not been evaluated as in steps (aa) through (cc) and if so, selecting a next value from the current data record which has not been evaluated as the current value and returning to step (aa), otherwise moving to the next step;

ee) Determining whether the current data table contains any records containing any values which have not been evaluated as in steps (aa) through (cc) and if so, designating a next current data record from the current data table containing at least one unevaluated value and designating an unevaluated data value in the next current data record as the current value and returning to step (aa), otherwise moving to the next step;

ff) Determining whether any of the plurality of data tables contain any records containing any values which have not been evaluated as in steps (aa) through (cc), and if so designating a next data table containing at least one unevaluated value as the current data table, designating a next current data record from the current data table containing at least one unevaluated value and designating an unevaluated data value in the next current data record as the current value and returning to step (aa), otherwise ending the method.

2. A method for correlating multiple tables in a database environment as in claim 1 further comprising the steps of:

a) Creating or selecting a search value, the search value comprising a target data value;

b) Checking to see if the search value is present in the first index data value field of any of the plurality of first index table records and if so, outputting a subset of the plurality of first index table records in which the first index data value field contains the search value and going to the next step, otherwise indicating to a user that the search value was not found and ending the method;

c) Outputting a subset of the plurality of first stitch object key-object pairs from the first stitch object table having a stitch object key value which contains the search value;

d) Outputting a subset of the plurality of first stitch map object key-object pairs from the first stitch map object table having a stitch map object key value which contains the search value.

3. A method for correlating multiple tables in a database environment as in claim 1, wherein the database management system application does not support the creation and maintenance of relationships between corresponding fields in at least two tables at the application level.

4. A method for correlating multiple tables in a database environment as in claim 2, wherein the database management system application does not support the creation and maintenance of relationships between corresponding fields in at least two tables at the application level.

5. A device for correlating multiple tables in a database environment comprising:

a) a digital computer comprising a CPU, a RAM and/or a persistent storage device, an input device, and a visual display, the RAM and/or the persistent storage device, the input device, and the visual display operably connected to the CPU;

b) a database management system application stored on the persistent storage device;

c) at least six tables, the at least six tables including at least three data tables, at least one index table, at least one stitch object table, and at least one stitch map object table, the at least six tables being stored on the persistent storage device and each having a unique corresponding table identifier, and the at least six tables including;

vii. a first data table comprising a plurality of first data table records, the plurality of first data table records comprising a plurality of first data table fields, the plurality of first data table fields having a plurality of associated first data table field names and the plurality of first data table fields storing a plurality of first data table field values;

viii. a second data table comprising a plurality of second data table records, the plurality of second data table records comprising a plurality of second data table fields, the plurality of second data table fields having a plurality of associated second data table field names and the plurality of second data table fields storing a plurality of second data table field values;

ix. a third data table comprising a plurality of third data table records, the plurality of third data table records comprising a plurality of third data table fields, the plurality of third data table fields having a plurality of associated third data table field names and the plurality of third data table fields storing a plurality of third data table field values;

x. an index table, the index table comprising a plurality of index table records, each index table record comprising an index data value field and an index data table name field;

xi. a stitch object table, the stitch object table comprising a plurality of stitch object key-object pairs, each stitch object key-object pair comprising a stitch object key value and a corresponding stitch object data object, the stitch object data object comprising a table of unique table identifiers and corresponding unique column identifiers associated with the stitch object key value; and

,

xii. a stitch map object table, the stitch map object table comprising a plurality of stitch map object key-object pairs, each stitch map object key-object pair comprising a stitch map object key value and a corresponding stitch map object data object, the stitch map object data object comprising a table of unique table identifiers and corresponding unique row identifiers associated with the stitch map object key value;

d) a database management program file stored on the RAM and/or the persistent storage device, the database management program file comprising a set of instructions which are executed by the CPU and cause the database management system application to perform the following operations;

i. Create the first index table in the database management system comprising a plurality of first index table records;

ii. Designate the first data table as the current data table;

iii. Designate a first data table field record as the current data record;

iv. Designate a current value as the first data table field value of the first data table record of the first data table;

v. Search the first index table to determine whether the current value is present in the first index value field of any of the plurality of first index table records;

vi. Add a new first index table record to the first index table, store the current value in the first index value field of the new first index table record, and store the unique corresponding table identifier of the current data table in the first index table name field of the new first index table record if the current value is not present in the first index value field of any of the plurality of first index table records;

vii. Append the unique corresponding table identifier of the current data table to the first table association field of the first index table record containing the current value if the current value is present in the first index value field of any of the plurality of first index table records;

viii. Determine whether the current data record contains any values which have not been evaluated as in operations (v) through (vii) and if so, select a next value from the current data record which has not been evaluated as the current value and return to operation (v), otherwise perform the following operation;

ix. Determine whether the current data table contains any records containing any values which have not been evaluated as in operations (v) through (viii) and if so, designate a next current data record from the current data table containing at least one unevaluated value and designating an unevaluated data value in the next current data record as the current value and return to operation (g), otherwise perform the next operation;

x. Determine whether any of the plurality of data tables contain any records containing any values which have not been evaluated as in operations (v) through (ix), and if so designate a next data table containing at least one unevaluated value as the current data table, designate a next current data record from the current data table containing at least one unevaluated value and designate an unevaluated data value in the next current data record as the current value and return to operation (v), otherwise perform the following operation;

xi. Create a first stitch object table in the database management system comprising a plurality of first stitch object table key-object pairs, each of the first stitch object table key-object pairs containing a first stitch object key value and a first stitch object data object;

xii. Designate the first data table as the current data table;

xiii. Designate a first data table field record as the current data record;

xiv. Designate a current value as the first data table field value of the first data table record of the first data table;

xv. Search the first stitch object table to determine whether the current value is present in the first stitch object key value field of any of the plurality of first stitch object key-object pairs;

xvi. Add a new first stitch object table key-object pair to the first stitch object table, store the current value in the first stitch object key value field of the new first stitch object table key-object pair, and store the unique corresponding table identifier of the current data table and the data table field name of the data table field corresponding to the current value in the first stitch object table data object of the new first stitch object table key-object pair if the current value is not present in the first stitch object key value field of any of the plurality of first stitch object table key-object pairs;

xvii. Add the unique corresponding table identifier of the current data table and the data table field name of the data table field corresponding to the current value in the first stitch object table data object of an existing first stitch object table key-object pair if the current value is present in the first stitch object key value field of any of the plurality of first stitch object table key-object pairs but the unique corresponding table identifier of the current data table and the data table field name of the data table field corresponding to the current value are not present in that first stitch object table key-object pair;

xviii. Determine whether the current data record contains any values which have not been evaluated as in operations (xv) through (xvii) and if so, select a next value from the current data record which has not been evaluated as the current value and returning to operation (xv), otherwise perform the following operation;

xix. Determine whether the current data table contains any records containing any values which have not been evaluated as in operations (xv) through (xviii) and if so, designate a next current data record from the current data table containing at least one unevaluated value and designate an unevaluated data value in the next current data record as the current value and return to operation (xv), otherwise perform the following operation;

xx. Determine whether any of the plurality of data tables contain any records containing any values which have not been evaluated as in operations (xv) through (xix), and if so designate a next data table containing at least one unevaluated value as the current data table, designate a next current data record from the current data table containing at least one unevaluated value and designate an unevaluated data value in the next current data record as the current value and return to operation (xv), otherwise perform the following operation;

xxi. Create a first stitch map object table in the database management system comprising a plurality of first stitch object table key-object pairs;

xxii. Designate the first data table as the current data table;

xxiii. Designate a first data table field record as the current data record;

xxiv. Designate a current value as the first data table field value of the first data table record of the first data table;

xxv. Search the first stitch map object table to determine whether the current value is present in the first stitch map object key value field of any of the plurality of first stitch map object table key-object pairs;

xxvi. Add a new first stitch map object table key-object pair to the first stitch map object table, store the current value in the first stitch map object key value field of the new first stitch map object table key-object pair, and store the unique corresponding table identifier of the current data table and a corresponding current data table row number corresponding to the current value in the first stitch map object table data object of the new first stitch map object table key-object pair if the current value is not present in the first stitch map object key value field of any of the plurality of first stitch map object table key-object pairs;

xxvii. Add the unique corresponding table identifier of the current data table and the corresponding current data table row number corresponding to the current value in the first stitch map object table data object of an existing first stitch map object table key-object pair if the current value is present in the first stitch map object key value field of any of the plurality of first stitch map object table key-object pairs but the unique corresponding table identifier of the current data table and the data table field name of the data table field corresponding to the current value are not present in that first stitch map object table key-object pair;

xxviii. Determine whether the current data record contains any values which have not been evaluated as in operations (xxv) through (xxvii) and if so, select a next value from the current data record which has not been evaluated as the current value and return to operation (xxv), otherwise perform the following operation;

xxix. Determine whether the current data table contains any records containing any values which have not been evaluated as in operations (xxv) through (xxviii) and if so, designate a next current data record from the current data table containing at least one unevaluated value and designate an unevaluated data value in the next current data record as the current value and return to operation (xxv), otherwise perform the following operation;

xxx. Determine whether any of the plurality of data tables contain any records containing any values which have not been evaluated as in operations (xv) through (xxix), and if so designate a next data table containing at least one unevaluated value as the current data table, designate a next current data record from the current data table containing at least one unevaluated value and designate an unevaluated data value in the next current data record as the current value and return to operation (xxv), otherwise perform the following operation;

xxxi. Allow a user to create or select a search value, the search value comprising a target data value;

xxxii. Check to see if the search value is present in the first index data value field of any of the plurality of first index table records and if so, output a subset of the plurality of first index table records in which the first index data value field contains the search value and perform the following operation, otherwise indicate to the user that the search value was not found and terminating the instructions;

xxxiii. Output a subset of the plurality of first stitch object key-object pairs from the first stitch object table having a stitch object key value which contains the search value; and

,

xxxiv. Output a subset of the plurality of first stitch map object key-object pairs from the first stitch map object table having a stitch map object key value which contains the search value.

6. A device for correlating multiple tables in a database environment as in claim 5, wherein the database management system application does not support the creation and maintenance of relationships between corresponding fields in at least two tables at the application level.

7. A device for correlating multiple tables in a database environment as in claim 6, wherein the database management system application does not support the creation and maintenance of relationships between corresponding fields in at least two tables at the application level.