Automatic creation of data relationships

The disclosed implementations allow automatic and transparent creation of data relationships in a database application or other application in response to user generated trigger events. Related data records can be stored and displayed in layouts, screens, forms and user interfaces provided by the database application or other application. Additional or extended information corresponding to the related data can be stored and displayed.

TECHNICAL FIELD

The subject matter of this application is generally related to relational databases.

BACKGROUND

A relational database is a set of relations or a database built in a Relational Database Management System (RDBMS). A typical relational database is a collection of tables and other items for organizing and structuring data, and for forcing the database to conform to a set of requirements. A RDBMS is a set of software programs that controls the organization, storage and retrieval of data in a database. A typical RDBMS includes a modeling language to define a schema (relational model) of each table stored in the RDBMS, according to the RDBMS data model. Data is typically organized into data structures (e.g., fields, records, files). A database query language and report writer can be used to interactively enter and update the database, as well as interrogate it.

Examples of RDBMSs include: Oracle®, Microsoft® SQL Server, MySQL® and FileMaker® Pro. FileMaker® Pro is a cross-platform database application that integrates a database engine with a graphical user interface for allowing users to modify a database by dragging new elements into layouts, screens and forms that provide the user interface.

While RDBMSs are capable of managing complicated data relationships, there is a steep learning curve associated with creating data relationships using such applications. Users who are not familiar with the features of the RDBMS, or simply want to create a relational database without opening a manual, may avoid using the RDBMS altogether, or hire a professional to create the relational database which may be too expensive for some users.

SUMMARY

The disclosed implementations allow automatic and transparent creation of data relationships in a database application or other application in response to user generated trigger events. Related data records can be stored and displayed in layouts, screens, forms and user interfaces provided by the database application or other application. Additional or extended information corresponding to the related data can be stored and displayed.

In one aspect, a user can drag and drop an object (e.g., an icon) representing a first data source into a layout, screen, form or other user interface element associated with a second data source. When the object is dropped, a related records list is displayed which the user can manually populate with data from the first data source using a variety of data population tools. Relationship information is automatically and transparently (transparent to the user) generated and stored in one or more background tables. The background tables allow the user to display data from the first data source in a layout, screen, form or other user interface element associated with the second data source and vice-versa.

The disclosed implementations advantageously allow users to relate two or more data sources and records directly without having to manually create relationships between the data sources and manage values that connect related records. Related data is intelligently utilized to display or otherwise present related records on either side of a data relationship. Additional or extended information that relates to two or more specific data values can be stored and displayed in layouts, screens, forms and other user interfaces of a database application or other application.

Other implementations are disclosed for automatic creation of data relationships, including implementations directed to systems, methods, apparatuses, computer-readable mediums and user interfaces.

DETAILED DESCRIPTION

Database Application Overview

FIGS. 1A and 1Bare screen shots showing an example application100that can be used to present and manage data. For example, contact information and event information can be presented as data102and152inFIGS. 1A and 1B, respectively. In some implementations, data can originate from a datastore, such as an external data source. Typically, data102and152are stored in a relational database. A relational database stores data in one or more tables. A table can include one or more records that can be further categorized into one or more fields. A field can specify relevant information for a record. For example, data102specifying one or more contacts can be stored in a contacts table. Each contact in the table can be represented as a record, and relevant information (e.g., first name, last name, and phone number) can be specified by the appropriate field. As another example, data152specifying one or more events can be stored in an events table.

InFIGS. 1A and 1B, the contacts and events are unrelated data because neither table includes information relating contacts and events. As described below, the application100can automatically and transparently relate unrelated data in response to user-generated trigger events or actions of a user (e.g., drag and drop actions or other user interface-based actions).

In some implementations, a user of the application100can modify the presentation of data. For example, the application100includes a user interface region (e.g., region104) that allows the user to select which fields in the records of a particular table are displayed. In reference toFIG. 1A, the user can select one or more checkboxes in user interface region104to display fields for each record in the contacts table corresponding to date created, date modified, first name, last name, phone number, and combinations thereof. In the example shown, a first name, last name, and phone number are selected in user interface region104and the corresponding fields of the contacts table are presented as data102.

The application100can also include a region106that allows the user to select from different data sources (e.g., source tables). As illustrated byFIG. 1A, if the user selects the contact data source (as shown by selection box108), the contacts table is referenced and the contact data102is displayed in display area110. As illustrated byFIG. 1B, if the user selects an events data source (as shown by selection box112), the events table is referenced and the events data152is displayed in the display area110.

In some implementations, the display area110can be modified by a table button114and a form button116, respectively. For example, when the table button114is selected, the contents of the selected table can be displayed in a tabular format. As another example, as illustrated byFIGS. 2-7, when the form button116is selected, the contents of the selected record can be displayed as one or more values in a user interface form. Because the tables are referenced directly, any number of records, containing any number of fields can be referenced and displayed by the application100. For example, the user can create a data source named “locations” that includes an address field, a name field, a phone number field, or any other fields. Similarly other data sources containing any number of fields can be generated as appropriate. In other words, the application100can parse an arbitrary database table and generate an appropriate representation containing the fields of the database table.

Automatic Creation of Data Relationships

FIGS. 2-5are screen shots showing example operations that can be used to automatically and transparently relate data. In some implementations, the user of application100can use drag-and-drop or dialog-based functionality to automatically and transparently relate data. For example, when using drag-and drop functionality, the user can drag one set of data onto another set of data to generate relationships between the sets of data. As another example, when using dialog-based functionality, the user can generate relationships by creating a field that relates records from a second source with records in the current source. Because relationships are generated automatically and transparently, the user avoids the need to enter information directly into a data field or otherwise manually edit records of the database in order to relate specific records. In some implementations, the relationships can be created and maintained using database join operations (e.g., an SQL Join command). The database join operations can be used to create a new data structure for storing relationship information by joining two or more database tables, as described in reference toFIGS. 12 and 13.

FIG. 2illustrates an example drag-and-drop operation. In some implementations, the user of the application100can select a form view by pressing the form button116. The form view can include a user interface region202that displays information corresponding to a record. For example, the user interface region202can display the fields of a selected event. Additionally, the form view can include a user interface region204that allows the user to automatically generate relationships between two unrelated data sources, such as the contacts and events tables102,152described in reference toFIGS. 1A and 1B. As illustrated by arrow206, in some implementations the user can drag-and-drop an object (e.g., an icon or image) of the contacts data source onto the user interface region204which is part of the events form view.

Referring toFIG. 3, after the user drags-and-drops the contacts data source onto the user interface region204, the application100automatically generates and presents to the user a blank related records list302(e.g., the table named “Contacts”) which stores a list of contact records that are related to the current event record. In some implementations, the list302includes buttons304,306and308that allow the user to manipulate data in the list302. For example, button306allows the user the user to add a contact to the list302and button308allows the user to remove a contact from the list302.

In some implementations, if the user selects button304, a user interface form402is generated, as illustrated by the arrow404. The user interface form402displays available items from the appropriate data source. For example, the user interface form402displays the contacts in the contacts data source. The user interface form402allows the user to select one or more items (e.g., records) and add the items to the list302, as illustrated by arrow410. The user can select any number of records (e.g., the set of records406) and click on the button408to add the selected records to the list302. The user can also use a mouse or other pointing device to drag any number of records in user interface form402and drop the records onto the list302. Adding records to the related records list automatically and transparently relates the data. In the example shown, the selected contacts406are automatically related to the currently selected event.

The user can view automatically generated relationships using the application100. As illustrated byFIG. 5, the contacts added to the event are related to the event and thus can be displayed whenever event information is displayed. In the example shown, the added contacts are displayed in the list302. Note that Sam Tastic was not selected inFIG. 4, so he was not added to the list302inFIG. 5. In addition, as illustrated byFIG. 5, an add field button502may be added to the related record list302. The button502can be used to add additional fields to the related record list302. Adding additional fields to a related records list is described in more detail in reference toFIGS. 8 and 9.

FIGS. 2-5describe example operations that relate one or more contacts with a selected event. The application100, however, is also capable of relating one or more events with a selected contact, as described in reference toFIGS. 6 and 7.

Examples of Bi-Directional Data Relationships

FIGS. 6 and 7are screen shots showing example operations that can be used to automatically show related data. The examples described in reference toFIGS. 2-5can automatically create related data. The application100also allows the user to view previously related data. In previous examples, contacts were related with events. For example, in reference toFIG. 4, the contact Maria Garza was added to the event John and Jane's Wedding. Moreover, a substantially similar set of steps as described in reference toFIGS. 4-5can be used to add Maria Garza to the event Backyard Barbeque. As shown byFIGS. 6 and 7, this relationship is bi-directional. The user can select a data source and add it to the form (FIG. 6) and can view relationships that are associated with, or have been generated using, the selected data source (FIG. 7, which displays data that was related using steps like those shown inFIGS. 4-5). For example, the user can select the contacts data source (e.g., as illustrated by selection box108), and select a contact record (not shown). The application100can then display the selected contact in user interface region602. The user can then drag the events data source into the form604, as illustrated by arrow606. As illustrated byFIG. 7, the selected contact is displayed with the related events. For example, because contact Maria Garza was previously related to John and Jane's Wedding event and the Backyard Barbecue event, the respective events are displayed in related record list302. As another example, if contact Todd Smith were selected, because he is only related to John and Jane's Wedding event (e.g., as illustrated byFIG. 4), Todd Smith's related record list302would include John and Jane's Wedding event, but not the Backyard Barbecue event.

In some implementations, the action of dragging-and-dropping the events data source onto the form generates a user interface component (e.g., list302). The list302can then be populated with information corresponding to information related to the selected record. For example, the list302can include the events that the selected contact is already related to, either through the operations described in reference toFIGS. 2-5, or by using buttons304,306, or308. The user can select button304(e.g., in reference toFIG. 7) and add additional events to the contact. This operation can display the contact as attending the event in the case that the user selects the event. For example, after the user selects the added event, the event can appear in the list302of the respective contact.

Examples of Adding Additional Information to Related Records

FIGS. 8 and 9are screen shots showing example operations that can be used to automatically add additional fields to records in a related records list. In some implementations, it may be useful to avoid modifying a data source (e.g., the contacts table or the events table) because additional fields may not be relevant to all base records, but are relevant when considered in the presence of two or more related values. For example, in the absence of a contact being related to an event (or vice versa) an RSVP status is meaningless. However, adding an RSVP status for a particular contact (e.g., Maria Garza) with regard to a particular event (e.g., John and Jane's Wedding) may be useful. As another example, adding an additional field to store the quantity of a particular product (e.g., blue shirts) for a particular customer (e.g., Maria Garza) on a particular invoice may be useful. In other words, in some situations a user may want to keep track of information that is meaningful within the context of two pieces of information (e.g., an events and a contact), but not meaningful when stored in isolation. Rather than adding the field to the events table, or another similarly relevant table, the field can be added to a background table which cannot be directly accessed or modified by the user, as shown inFIG. 13B. Background tables are described in reference toFIGS. 12 and 13.

Referring toFIG. 5andFIG. 8, in some implementations the user can display related information in the related record list302(e.g., contacts related to a selected event). The user can select a user interface button (e.g., button502, shown in reference toFIG. 5) or use some other user interface operation (e.g., a pull down menu, keyboard short-cut, or a series of mouse clicks) on a user interface component, such as related records list302. Responsive to the selection, the application100generates a user interface form802that allows the user to generate additional fields for the selected records. For example, the user can select a field type from user interface component804including a text field, a number field, a choice field, a checkbox field, a media field, a file list field, a related record list field, or other field types. The user can also name the field as appropriate in user interface component806. Both the type of field and the name of the field can be stored in a database table. For example, a new table can be generated that includes both the type of field (e.g., checkbox) and the field name (e.g., RSVP). The generated table can be combined (e.g., joined using a MySQL® join command) with the appropriate background table (e.g., a table that specifies related fields) to specify how the table is extended with additional information.

Referring toFIG. 9, a second additional field is added to the related table. As described in reference toFIGS. 1A and 1B, the application allows the user to modify how the records are displayed. This is also true of the additional fields, as illustrated by user interface region902. For example, the user can select the checkbox to determine if the additional RSVP field is displayed or not. Moreover, as illustrated byFIG. 9, the user is adding a text field named Gift Given. This information is also stored along with the checkbox named RSVP (e.g., described in reference toFIG. 8). In other words, the application100can reference a single background (e.g., joined) table to generate an appropriate view of the source table with the additional fields. For example, as illustrated inFIG. 10, a portion1000of the application100is shown. The wedding event is displayed with related contacts in the related records list302. Additional fields corresponding to whether an RSVP has been received, and the gift received are also displayed in user interface component302.

Moreover, as illustrated byFIG. 10, the possible values of the fields correspond to the type of additional field added. For example, because the RSVP field1002is a checkbox field type, as illustrated byFIG. 8, the value of the RSVP field1002can be either true or false which corresponds to a checked or an unchecked checkbox, respectively. As another example, the Gift Given field1004includes ASCII text, as specified by the text field type chosen as described in reference toFIG. 9. These additional field values are stored with respect to the contact and the event.

Example Process for Automatic Creation of Data Relationships

FIG. 11is a flow chart showing an example process1100for automatically relating data. In some implementations, the process1100receives user input indicative of an intent to create data relationships, creates background tables based on the received user input, automatically and transparently creates relationships derived from the user input, and stores the related data in the created tables. In some implementations, the process1100is executed after the user performs one or more operations. For example, as described in reference toFIG. 2, the process1100can be executed after the user drags-and-drops (e.g., as illustrated by arrow206) the contacts data source onto the events form region204. As another example, the process1100can be executed after the user initiates a dialog box-based operation to relate and display one data source with another (e.g., contacts with events inFIG. 2).

In step1102, the process1100adds an empty related record list to the form. For example, in reference toFIG. 3, the process1100can generate an empty related record list (e.g., list302) for the contacts data source. In step1104, the application100automatically and transparently (e.g., transparent to the user) creates a data structure for storing information relating data from a first data source (e.g., a first source table) with data from a second data source (e.g., a second source table). In some implementations, the data structure is a background table. Background tables are described in more detail in reference toFIGS. 12 and 13. In some implementations, background tables cannot be directly accessed or modified by a user.

In step1106, the process1100presents a list of potentionally related records. In general, the process1100accesses the database displays a list of records from the data source. For example, in reference toFIG. 4, the application can present a list of potentially related records in user interface form402.

In step1108, the process1100receives input specifying one or more records form the related records list to add to the form. In the example ofFIG. 4, the user can select records406and add them to form302using the user interface button408. As another example, the user can drag the selected records406onto the form302.

In step1110, the process1100automatically and transparently creates relationship information based on the received input and stores the relationships in one or more background tables. For example, if the input directs the process1100to relate one or more records with one or more other records, the process1100can store the record IDs in a background table (e.g., a join table), as described in reference toFIG. 13A. As another example, if the input directs the application to add additional fields to one or more records, the process1100can also store the additional fields in one or more background tables.FIG. 13Billustrates an example background table1314including joined data and additional fields. In the example shown, additional “RSVP” field1316and “Gift Given” field1318are added to the background table1314. The additional field data would be available to be displayed as related records to Contacts records when showing which event each contact has RSVP'd to and what gift will be given at the event. Similarly, the additional field data would be available to be displayed as related records to Events records when showing which contacts will attend each event, and if the contacts RSVP'd and/or give gifts.

In some implementations, background tables can be used to store related data and additional or extended fields related to data records. For example, in reference toFIGS. 5 and 10, a background table can store both the contacts related to the event and additional RSVP and Gift Given fields (e.g., fields1002and1004, respectively).

Example Database Architecture

FIG. 12is a block diagram showing a generic database architecture1200that can be used to automatically relate data. In general, the database architecture1200provides a data abstraction between one or more source tables1206a-1206nand actions the user generates relating to the source tables1206. Typically, the data abstraction is transparent to the user of the application100.

In some implementations, the database architecture1200includes a database access manager1202. The database access manager1202provides an interface between the application100, or other applications, and the database1204. For example, the database access manager1202can handle database queries including structure query language (e.g., MySQL®) queries, and other types of queries. Moreover, the database access manager1202can handle the creation of new database tables. For example, a new table can be created through a create command (e.g., SQL CREATE TABLE statement). For example, as shown inFIG. 2, the user drags and drops one data source onto another data source, causing a background table to be automatically created to store related records. InFIG. 4, the user adds related records simply by selecting them and adding them to the list. All of these actions cause SQL-based commands to occur, in order to store the relationship between the data.

The database1204is used to store information, typically in the form of source tables. The database1204can include any number of source tables. In the example shown, the database1204includes source tables1206athrough1206n, where n is a positive integer. The database1204can also include any number of background tables. In the example shown, the database1204includes background tables1208athrough1208m, where m is a positive integer.

The background tables1208can store data relationships and additional or extended fields to related records. The background tables1208are automatically and transparently created by the database access manager1202in response to user triggered events (e.g., a drag and drop event). In some implementations, users of the application100are not allowed access to the background tables1208and thus cannot directly access or modify the contents of the background tables1208.

Example Background Table

FIG. 13Ais a block diagram showing an example background table1300that can be used to store relationship information. In some implementations, the background tables1208can be created by joining one or more source tables1206to specify data relationships. For example, consider two source tables1302and1304which include data102and152, respectively. In addition, the tables1302and1304include identifiers1302aand1304a, respectively, for each record. These identifiers are hidden from the user. Thus even though the tables1302and1304include identifiers, the application only displays data102and152, respectively.

As illustrated by arrows1306aand1306b, the application100can store the relationship information by performing a join operation on tables1302and1304(e.g., using an SQL Join command). This creates the background table1300where the contact IDs are associated with the events IDs. For example, the contact IDs1302acan be stored in the contact ID field1308and the event IDs1304acan be stored in the event ID field1310of the background table1300in order to relate the records from each source that the user has associated using actions like those described in reference toFIGS. 4-5.

In some implementations, the application100can also store added field information in background table1300. For example, the application100can add additional or extended fields (not shown) to the background table1300that specifies if a contact has sent a gift and/or RSVP to the host of the event. The values of the additional fields can be specified by user input. The values of the additional fields can be determined from the values entered into the related records list302, as illustrated byFIG. 10.

Example System Architecture

FIG. 14is a block diagram showing a generic computing architecture1400(e.g., a personal computer) that can be used to automatically relate data, as described in reference toFIGS. 1-13. The architecture1400generally includes one or more processor(s)1402, one or more storage device(s)1404(e.g., hard disk, optical disk, CD-ROM), one or more output devices1406(e.g., display device), one or more network interface(s)1408(e.g., Ethernet card, WAN adapter, USB port), one or more input devices1410(e.g., mouse, keyboard), and memory1412(e.g., RAM, ROM). The memory1412may include an operating system1416, a network communication module1418, one or more data processing application1420(e.g., an RDBMS), a database access manager1202, and one or more other applications1424. Each of these components can be operatively coupled to one or more buses1414for transferring and receiving instructions, addresses, data and control signals.

In some implementations, the database management system1420can be a cross-platform database application that integrates a database engine with a graphical user interface for allowing users to modify a database by dragging new elements into layouts, screens and forms that provide the user interface. The database management system1420interacts with the database access manager1422to implement the features and operations described in reference toFIGS. 1-13.

Various modifications may be made to the disclosed implementations and still be within the scope of the following claims.