System and methods including automatic linking of tables for improved relational database modeling with interface

• US 5,615,367 A
• Filed: 05/25/1993
• Issued: 03/25/1997
• Est. Priority Date: 05/25/1993
• Status: Expired due to Term

• Current Assignee: Borland Software Corporation (Open Text Corporation)
• Original Assignee: Borland International, Inc. (Open Text Corporation)
• Inventors: Bennett, John G., Gee, Perry A., Gayraud, Charles E.
• Primary Examiner(s): Kulik, Paul V.
• Application Number:
  US08/067,202
• Time in Patent Office: 1,400 Days
• Field of Search: 395/600, 395/155, 395/156, 395/157, 395/160, 395/161, 395/148
• US Class Current: 1/1
• CPC Class Codes: G06F 16/252 between a Database Management System and a front-end application
• CPC Class Codes: Y10S 707/99943 Generating database or data structure, e.g. via user interface

Abstract

A system includes a relational database management system (RDBMS) having a data modeling component. A "data model" is a graphical representation of the relationship between tables one may use in a design document. "Design documents" allow a user to customize how his or her data are presented, including presenting information in formats which are not tabular and including formats which link together different tables (so that information stored in separate tables appears to the user to come from one place). Methods are described for automatically linking tables to be placed in a data model by comparing unique keys (e.g., primary key or other unique identifier) of one table with indexes (or indexable fields) of another table. Based upon the comparison, the system automatically suggests an appropriate link (if any) for the tables.

Claims

1. In an information processing system, a method for automatically linking information tables, each table including at least one information field, the method comprising:

(a) receiving user input for selecting first and second information tables to link;

(b) determining a unique key for one of the two tables;

(c) automatically determining by said system a foreign key for the other of the two tables which satisfies said unique key; and

(d) if a foreign key is available, automatically linking said first and second tables through the foreign key.

2. The method of claim 1, further comprising:

(e) if said unique key is from the first table and said foreign key is from the second table and is also a unique key, indicating to a user a one-to-one link.

3. The method of claim 2, wherein step (e) includes:

displaying on a screen device an indicator for each of said first and second tables; and

indicating a one-to-one link with a single arrow-head line drawn from one identifier to the other.

4. The method of claim 1, further comprising:

(e) if said unique key is from the first table and said foreign key is from the second table and is not a unique key, indicating to a user a one-to-many link.

5. The method of claim 4, wherein step (e) includes:

displaying on a screen device an identifier for each of said first and second tables; and

indicating a one-to-many link as a double arrow-head line drawn from one identifier to the other.

6. The method of claim 1, further comprising:

(e) if said unique key is from the second table and said foreign key is from the first table and is not a unique key, indicating to, a user a many-to-one link.

7. The method of claim 6, wherein step (e) includes:

displaying on a screen device an identifier for each of said first and second tables; and

indicating a many-to-one as a single arrow-head line drawn from the identifier for the first table to the identifier for the second table.

8. The method of claim 1, further comprising:

(e) if a foreign key is not available, determining at least one field of the other table which shares a common domain with at least one field of the unique key.

9. The method of claim 1, further comprising:

(e) if a foreign key is not available, determining at least one indexable field of the other table which may serve as the foreign key.

10. The method of claim 9, wherein step (e) includes:

locating at least one indexable field of the second table having a data type compatible with said unique key.

11. The method of claim 10, wherein step (e) further includes:

if more than one said at least one indexable field having a compatible data type are available, selecting one having at least one field name which corresponds to at least one field name of the unique key.

12. The method of claim 11, wherein said selecting at least one indexable field having at least one field name which corresponds includes:

matching a text substring of said at least one indexable field with a text substring of at least one field name of the unique key.

13. The method of claim 9, wherein after step (b) further comprises:

if a unique key cannot be automatically determined, selecting with an input device at least one field from one of said tables; and

and locating at least one field of the other table which shares a common domain with said selected at least one field.

14. The method of claim 1, wherein said unique key includes a key selected from at least one candidate key.

15. The method of claim 1, wherein said unique key includes a primary key.

16. The method of claim 1, wherein said unique key includes an index which uniquely identifies each record of its table.

17. The method of claim 1, wherein step (a) includes determining a unique key by first attempting to locate a primary key, if no primary key is located, then attempting to locate other candidate keys, if no other candidate keys are located, then attempting to locate other unique indexes.

18. The method of claim 1, wherein step (a) includes:

displaying a list of available tables on a screen device;

selecting from the list at least two tables; and

drawing on the screen device a line from one selected table to another.

19. The method of claim 18, wherein a table that the line is drawn from becomes said first table and a table that the line is drawn to becomes said second table.

20. The method of claim 1, further comprising:

repeating steps (a)-(d) with a new table as one of said first and second information tables, and an existing table as the other of said first and second information tables.

21. A system for modeling data in a relational database management system comprising:

a computer having a processor, a memory, and a screen device;

interface means for receiving user input for requesting a link from a first information table to a second information table; and

means, responsive to said request means, for automatically determining by said system a relationship which links said first and second information tables.

22. The system of claim 21, wherein said interface means includes:

means for selecting a plurality of tables from a list of available information tables;

means for displaying on the screen device an identifier for each selected tables; and

means for selecting first and second identifiers for requesting a link from corresponding first and second information tables.

23. The system of claim 22, wherein said means for selecting includes input means for drawing a line from one identifier to another, wherein an identifier that the line is drawn from becomes said first identifier and an identifier that the line is drawn to becomes said second identifier.

24. The system of claim 21, wherein said determining means includes:

means for determining a unique key of one of said first and second information tables and determining a foreign key of the other which satisfies said unique key.

25. The system of claim 24, further comprising:

means for indicating a one-to-one relationship between said first and second tables if the foreign key which satisfies the determined unique key is also a unique key.

26. The method of claim 24, further comprising:

means for indicating a one-to-many relationship between said first and second tables if the foreign key which satisfies the unique key is not a unique key.

27. The system of claim 21, wherein said determining means includes:

means for determining a unique key from one of said first and second tables; and

means for determining a foreign key for the other of said first and second tables which satisfies said unique key, said means including means for creating a foreign key relationship if none exists.

28. The system of claim 27, wherein said means for creating a foreign key relationship includes:

means for determining an indexable field of the other of said first and second tables which has a data type compatible with said unique key.

29. A system for linking information stored in data objects, each object including at least one field for storing data, the system comprising:

a storage device for storing a plurality of data objects;

a screen device for representing selected ones of the data objects;

input means for receiving user input for selecting first and second data objects to link;

means for determining a unique key for one of said first and second data objects; and

means, responsive to said input means, for automatically linking by said system said first and second data objects through a foreign key which satisfies said unique key.

30. The system of claim 29, wherein said data objects include database tables.

31. The system of claim 29, wherein said input means includes a pointing device for positioning a cursor on the screen device and for generating user signals at a desired location on the screen device.

32. The system of claim 29, wherein means for determining a unique key includes:

means for determining at least one candidate key available for a data object; and

means for selecting a primary key from said at least one candidate key.

33. The system of claim 29, wherein means for linking includes:

means for determining at least one indexable field of the other table which satisfies the unique key.

34. The system of claim 29, wherein means for linking includes:

means for determining a link type between said first and second data objects; and

user feedback means, operably coupled to the screen device, for indicating the link type to a user.

35. The system of claim 34, wherein said link type is a selected one of one-to-one, one-to-many, and many-to-one.