Systems and methods for automatically generating user interface elements for complex databases

• US 10,025,801 B2
• Filed: 07/07/2014
• Issued: 07/17/2018
• Est. Priority Date: 10/31/2000
• Status: Expired due to Fees

• Current Assignee: Michael Philip Kaufman
• Original Assignee: Michael Philip Kaufman
• Inventors: Kaufman, Michael Philip, Silverman, Micah Philip
• Primary Examiner(s): ALAM, SHAHID AL
• Application Number:
  US14/324,414
• Publication Number:
  US 20150019593A1
• Time in Patent Office: 1,471 Days
• Field of Search: 707802
• CPC Class Codes: G06F 16/20 of structured data, e.g. relational data
• CPC Class Codes: G06F 16/21 Design, administration or maintenance of databases
• CPC Class Codes: G06F 16/284 Relational databases
• CPC Class Codes: G06F 16/951 Indexing; Web crawling techniques
• CPC Class Codes: G06F 3/0481 based on specific properties of the displayed interaction object or a metaphor-based environment, e.g. interaction with desktop elements like windows or icons, or assisted by a cursor's changing behaviour or appearance
• CPC Class Codes: G06F 3/0484 for the control of specific functions or operations, e.g. selecting or manipulating an object, an image or a displayed text element, setting a parameter value or selecting a range
• CPC Class Codes: G06F 7/00 Methods or arrangements for processing data by operating upon the order or content of the data handled logic circuits H03K19/00
• CPC Class Codes: Y10S 707/912 Applications of a database

Abstract

In one embodiment, a software system automatically generates a fully functional user interface (UI) based upon any underlying schema within a relational database management system (RDBMS). The UI derives from an automated interrogation of the schema, and comprises all mode displays (e.g., browse, search, edit, add) for all tables, along with integrated mechanisms for representing, navigating and managing relationships across tables. It utilizes a hierarchical “context stack” for suspending the working state of a particular table while “drilling down” to work with related-table information and (potentially) return relevant changes to the base table. The UI presentation resolves cross-table relationships so as to supplant internal key fields from the primary table with corresponding descriptive fields derived from the related tables. Techniques are also provided to enhance and extend the internal representation of table structures, constraints, relationships and special requirements (“business rules”) for improved discovery of the schema structure via automated interrogation.

Claims

1. A method for displaying, within a user interface operating under control of a processor, an enhanced representation of data from a relational database, the relational database comprising machine-readable data representing a plurality of tables, constraints, and relationships and also operating under control of a processor, wherein the plurality of tables comprises a primary table and a foreign table, wherein each one of the primary table and the foreign table comprises at least one row, and wherein each row comprises a plurality of columns, the method comprising:

using a processor to automatically construct a representation of data from a row of the primary table (primary-table row) by;

using a processor to identify a foreign key (FK) value in an FK column in the primary-table row, wherein the FK column references the foreign table;

using a processor to locate a row in the foreign table (foreign-table row) whose primary key (PK) value matches the identified FK value;

using a processor to select a value from the foreign-table row other than the PK value based on at least one of the following attributes of the column in the foreign table that contains the selected value;

column name;

column datatype;

at least one column constraint;

or

column position within the foreign table;

using a processor to derive a description of the foreign-table row using the selected value; and

using a processor to augment or supplant the FK value with the description in constructing the representation; and

using a processor to display the constructed representation.

2. The method of claim 1, further comprising allowing, in the machine representation of the tables within the database, for explicit specification of instructions to derive the description of the foreign-table row.

3. The method of claim 2, wherein the explicit specification can optionally supplant the default derivation as determined in accordance with claim 1.

4. The method of claim 3, further comprising the scoping of explicit specifications, so as to enable both database-wide (global) and primary-table specific (local) use of the explicit specification.

9. The method of claim 1, wherein the derivation of the description is based on at least one of the following attributes of the foreign table:

column datatypes;

column names;

column constraints; and

the order of columns.

10. The method of claim 1, wherein the derivation of the description is based on the column datatypes within the foreign table.

11. The method of claim 1, wherein the derivation of the description is based on the column names within the foreign table.

12. The method of claim 1, wherein the derivation of the description is based on the column constraints within the foreign table.

13. The method of claim 1, wherein the derivation of the description is based on the order of columns within the foreign table.

14. The method of claim 1, wherein the derivation of the description comprises:

identifying an FK value in an FK column in the located foreign-table row, wherein the FK column in the located foreign-table row corresponds to a third table of the plurality of tables;

locating a row in the third table (third-table row) whose PK value matches the identified FK value in the located foreign-table row; and

using at least one value from the third-table row other than the PK value to derive the description.

15. The method of claim 2, wherein the derivation of the description comprises:

specifying an FK value in an FK column in the located foreign-table row, wherein the FK column in the located foreign-table row corresponds to a third table of the plurality of tables;

locating a row in the third table (third-table row) whose PK value matches the specified FK value in the located foreign-table row; and

using at least one value from the third-table row other than the PK value to derive the description.

5. A non-transitory machine-readable medium, on which there has been recorded machine-readable code for a program executable on a processor, said program comprising routines for displaying, within a user interface operating under control of a processor, an enhanced representation of data from a relational database, the relational database comprising machine-readable data representing a plurality of tables, constraints, and relationships and also operating under control of a processor, wherein the plurality of tables comprises a primary table and a foreign table, wherein each one of the primary table and the foreign table comprises at least one row, and wherein each row comprises a plurality of columns, the routines comprising:

a routine to automatically construct a representation of data from a row of the primary table (primary-table row) by;

a routine to identify a foreign key (FK) value in an FK column in the primary-table row, wherein the FK column references the foreign table;

a routine to locate a row in the foreign table (foreign-table row) whose primary key (PK) value matches the identified FK value;

a routine to select a value from the foreign-table row other than the PK value based on at least one of the following attributes of the column in the foreign table that contains the selected value;

column name;

column datatype;

at least one column constraint;

or

column position within the foreign table;

a routine to derive a description of the foreign-table row using the selected value; and

a routine to augment or supplant the FK value with the description in constructing the representation; and

a routine to display the constructed representation.

6. The non-transitory machine-readable medium of claim 5, further comprising a routine for allowing, in the machine representation of the tables within the database, for explicit specification of instructions to derive the description of the foreign-table row.

7. The non-transitory machine-readable medium of claim 6, wherein the explicit specification can optionally supplant the default derivation as determined in accordance with claim 5.

8. The non-transitory machine-readable medium of claim 6, further comprising a routine for the scoping of explicit specifications, so as to enable both database-wide (global) and primary-table specific (local) use of the explicit specification.

16. The non-transitory machine-readable medium of claim 6, wherein the derivation of the description is based on at least one of the following attributes of the foreign table:

column datatypes;

column names;

column constraints; and

the order of columns.

17. The non-transitory machine-readable medium of claim 6, wherein the derivation of the description is based on the column datatypes within the foreign table.

18. The non-transitory machine-readable medium of claim 6, wherein the derivation of the description is based on the column names within the foreign table.

19. The non-transitory machine-readable medium of claim 6, wherein the derivation of the description is based on the column constraints within the foreign table.

20. The non-transitory machine-readable medium of claim 6, wherein the derivation of the description is based on the order of columns within the foreign table.

21. The non-transitory machine-readable medium of claim 6, wherein the derivation of the description comprises:

identifying an FK value in an FK column in the located foreign-table row, wherein the FK column in the located foreign-table row corresponds to a third table of the plurality of tables;

locating a row in the third table (third-table row) whose PK value matches the identified FK value in the located foreign-table row; and

using at least one value from the third-table row other than the PK value to derive the description.

22. The non-transitory machine-readable medium of claim 7, wherein the derivation of the description comprises:

specifying an FK value in an FK column in the located foreign-table row, wherein the FK column in the located foreign-table row corresponds to a third table of the plurality of tables;

locating a row in the third table (third-table row) whose PK value matches the specified FK value in the located foreign-table row; and

using at least one value from the third-table row other than the PK value to derive the description.