Database Part Table Junctioning

• US 20090248710A1
• Filed: 03/27/2008
• Published: 10/01/2009
• Est. Priority Date: 03/27/2008
• Status: Active Grant

• Current Assignee: Microsoft Technology Licensing LLC (Microsoft Corporation)
• Original Assignee: Microsoft Corporation
• Inventors: Gardner, Scott T., Bould, Jason A., McCormack, Michael J., Miller, Andrew R., Chauhan, Sumit
• Granted Patent: US 7,895,174 B2
• Time in Patent Office: Days
• US Class Current: 1/1
• CPC Class Codes: G06F 16/288 Entity relationship models

Abstract

Embodiments are provided to define relationships for merging template or database part tables with one or more database tables in a computer system. A database application may utilize a schema to receive metadata associated with a target database part table in one or more markup language elements. The database application may then receive metadata associated with a source database table to be joined with the target table in the schema elements. The database application may then receive join type metadata in the schema elements. The join type metadata may specify whether fields from the source table are to be merged into the target table or whether fields from the target table are to be merged into the source table. In response to receiving the target table metadata and the source table metadata, the database application may then merge the target and source tables utilizing the join type metadata.

Claims

1. A method of utilizing a schema for defining a join relationship, the schema being utilized for merging a database part table with a database table by a database application executing on a computer system, comprising:

receiving, in the database application, target table metadata associated with a database part in a plurality of relationship elements in the schema, the database part comprising a target table;

receiving, in the database application, source table metadata associated with a database comprising a source table to be joined with the target table in the plurality of relationship elements in the schema, the source table metadata describing a relationship to a class of tables existing outside of the target table;

receiving, in the database application, join type metadata in the plurality of relationship elements in the schema, the join type metadata defining how data in the target table is to be merged with data in the source table; and

in response to receiving the target table metadata and the source table metadata, merging the target and source tables utilizing the join type metadata.

2. The method of claim 1, wherein receiving target table metadata in a plurality of relationship elements in the schema comprises receiving target table metadata in a target lookup field element in the schema, wherein the target table metadata in the target lookup field element is utilized to create a lookup column in the target table, wherein the lookup column contains values to be utilized as the contents of at least one field located in the source table.

3. The method of claim 2, wherein receiving target table metadata in a target lookup field element comprises receiving at least one of the following:

first metadata describing a class of tables associated with a lookup column field in the lookup column, second metadata describing a string used to identify a name for the lookup column field, third metadata describing a string used to provide a caption for the lookup column field in a user interface generated by the client application, fourth metadata describing a required property value for the lookup column field, fifth metadata describing an index value for the lookup column field, wherein the index value is utilized to determine whether the lookup column field is indexed, and sixth metadata describing whether the lookup column field allows multiple values.

4. The method of claim 1, wherein receiving target table metadata in a plurality of relationship elements in the schema comprises receiving target table metadata in at least one of a target table identification element and a target table name element in the schema.

5. The method of claim 1, wherein receiving source table metadata associated with a database in a source table to be joined with the target table in the plurality of relationship elements in the schema comprises receiving source table metadata in at least one of a source table identification element, a source table name element, a source field identification key element, and a source field identification lookup element in the schema.

6. The method of claim 1, wherein receiving join type metadata in the plurality of relationship elements in the schema comprises receiving at least one of first metadata specifying a source outer join in which records from the source table are merged into the target table and second metadata specifying a target outer join in which records from the target table are merged into the source table.

7. The method of claim 1, wherein receiving, in the database application, target table metadata associated with a database part in a plurality of relationship elements in the schema, comprises receiving target table metadata in a plurality of markup language elements.

8. The method of claim 1, wherein receiving, in the database application, join type metadata in the plurality of relationship elements in the schema, comprises receiving join type metadata in a plurality of relationship elements defining optional database relationships, wherein the optional database relationships provide a user of the database application with an option to accept or reject merging the source and target tables.

9. The method of claim 1, wherein receiving, in the database application, target table metadata associated with a database part in a plurality of relationship elements in the schema comprises receiving target table metadata associated with a database template.

10. A computer-readable storage medium comprising computer executable instructions which, when executed on a computer, will cause the computer to perform a method of communication between a database application and a database web service for retrieving metadata utilized in defining a join relationship for merging a database part table with a database table by the database application, the method comprising:

transmitting a metadata request to the web service from the database application, the metadata request comprising a class for the database part table and a class for the database table;

in response to the metadata request, receiving the metadata in the database application, the metadata comprising a plurality of relationship elements defined by a schema; and

merging the database part table with the database table utilizing the received metadata.

11. The computer-readable storage medium of claim 10, wherein transmitting a metadata request to the web service from the database application comprises transmitting a request to a junction catalog, the junction catalog comprising the metadata in a plurality of markup language files, the markup language files comprising the plurality of markup language elements defined by the schema.

12. The computer-readable storage medium of claim 10, wherein transmitting a metadata request to the web service from the database application comprises transmitting the database part table class and the database table class to a Uniform Resource Locator associated with the web service utilizing HyperText Transfer Protocol (HTTP).

13. The computer-readable storage medium of claim 10, wherein receiving the metadata in the database application comprises:

receiving first metadata associated with a target table;

receiving second metadata associated with a source table to be joined with the target table; and

receiving third metadata defining how data records in the target table are to be merged with data records in the source table, wherein in response to receiving the first metadata and the second metadata, the database application utilizes the third metadata to merge the target and source tables.

14. The computer-readable storage medium of claim 10, wherein transmitting a metadata request to the web service from the database application comprises transmitting a metadata request to the web service from a database client application.

15. The computer-readable storage medium of claim 10, wherein transmitting a metadata request to the web service from the database application comprises transmitting a metadata request to the web service from a database server application.

16. The computer-readable storage medium of claim 10, wherein merging the database part table with the database table utilizing the received metadata comprises merging a database template table with the database table utilizing the received metadata.

17. A method for displaying on a display device in a computer system, a user interface for defining a join relationship for merging a database part table with a database table in a database application executing on the computer system, comprising:

displaying a dropdown box in the user interface for choosing a database table from among a plurality of database tables as a source table to merge with a database part table, the database part table comprising a target table, wherein the source table comprises a plurality of fields; and

receiving a selection of the source table from among the plurality of database tables to merge with the target table, wherein selecting the source table creates a relationship with the target table, wherein the relationship comprises a target table lookup field which refers to a name associated with the source table, the source table name defined within one or more markup language elements in a schema, and wherein the relationship is utilized by the database application to merge the target and source tables.

18. The method of claim 17 further comprising:

displaying a dropdown box in the user interface for selecting a field from among the plurality of fields in the source table to be utilized as the target table lookup field for the relationship in response to a determination by the database application that at least two duplicate source table field identifications exist in the source table and

receiving the selection of the field from among the plurality of fields in the source table to be utilized as the target table lookup field by the database application.

19. The method of claim 17, wherein displaying a dropdown box in the user interface for choosing a database table from among a plurality of database tables as a source table to merge with a database part table comprises displaying a dropdown box in the user interface for choosing a database table from among a plurality database tables as a source table to merge with a database template.

20. The method of claim 17 further comprising displaying the dropdown box each time a relationship between the target table and the source table is created in the user interface.