Dynamic modeling of data in relational databases

Abstract

The disclosed embodiments provide a system that facilitates making updates to a data model, wherein the data model is associated with data items stored in a relational database system. In particular, updates to the data model are automatically propagated to the relational database system so that the structure of the relational database system remains consistent with the data model. This is accomplished by automatically executing queries corresponding to the updates against the relational database system. For example, changes to fields and/or tables in the data model can be automatically propagated to a relational database system by executing “Create,” “Drop,” “Alter,” and/or “Add” statements against corresponding fields and/or tables in the relational database system.

What is claimed is: 
     
         1 . A computer-implemented method for processing data, comprising:
 executing an application that accesses a data model including one or more tables, wherein the one or more tables comprise a set of scalar fields and a derived field associated with at least one of a summary, a formula, and a lookup;   storing the scalar fields in a relational database;   using the data model to generate a first query for the relational database to obtain data associated with the derived field using one or more joins of one or more of the scalar fields; and   enabling use of the first query to provide the data to a user during use of the application by the user.   
     
     
         2 . The computer-implemented method of  claim 1 , further comprising:
 using the application to obtain an update to the data model from the user;   generating a second query for the relational database corresponding to the update; and   enabling use of the second query to propagate the update to the relational database during use of the application by the user.   
     
     
         3 . The computer-implemented method of  claim 2 , wherein the update is associated with at least one of:
 creation of a new field;   removal of an existing field;   creation of a new table; and   removal of an existing table.   
     
     
         4 . The computer-implemented method of  claim 1 , wherein generating the first query to obtain data associated with the derived field involves:
 using the data model to create one or more views of the one or more tables; and   generating the query from the one or more views.   
     
     
         5 . The computer-implemented method of  claim 4 , wherein using the data model to create the one or more views of the one or more tables involves:
 identifying one or more fields associated with the query;   determining necessary joins associated with the one or more fields;   transforming joined tables associated with the necessary joins; and   building the one or more views from the transformed joined tables.   
     
     
         6 . The computer-implemented method of  claim 5 , wherein the necessary joins comprise nested joins. 
     
     
         7 . The computer-implemented method of  claim 1 , wherein the query comprises a Structured Query Language (SQL) query. 
     
     
         8 . The computer-implemented method of  claim 1 , wherein the data is provided to the user through a user interface of the application. 
     
     
         9 . A system for processing data, comprising:
 an application configured to:
 enable access to a data model including one or more tables, wherein the one or more tables comprise a set of scalar fields and a derived field associated with at least one of a summary, a formula, and a lookup; and 
 store the scalar fields in a relational database; and 
   a query builder configured to:
 use the data model to generate a first query for the relational database to obtain data associated with the derived field using one or more joins of one or more of the scalar fields; and 
 enable use of the first query to provide the data to a user during use of the application by the user. 
   
     
     
         10 . The system of  claim 9 ,
 wherein the application is further configured to obtain an update to the data model from the user, and   wherein the query builder is further configured to:
 generate a second query for the relational database corresponding to the update; and 
 enable use of the second query to propagate the update to the relational database during use of the application by the user. 
   
     
     
         11 . The system of  claim 10 , wherein the update is associated with at least one of:
 creation of a new field;   removal of an existing field;   creation of a new table; and   removal of an existing table.   
     
     
         12 . The system of  claim 9 , wherein generating the first query to obtain data associated with the derived field involves:
 using the data model to create one or more views of the one or more tables; and   generating the query from the one or more views.   
     
     
         13 . The system of  claim 12 , wherein using the data model to create the one or more views of the one or more tables involves:
 identifying one or more fields associated with the query;   determining necessary joins associated with the one or more fields;   transforming joined tables associated with the necessary joins; and   building the one or more views from the transformed joined tables.   
     
     
         14 . The system of  claim 13 , wherein the necessary joins comprise nested joins. 
     
     
         15 . A non-transitory computer-readable storage medium storing instructions that when executed by a computer cause the computer to perform a method for processing data, the method comprising:
 executing an application that accesses a data model including one or more tables, wherein the one or more tables comprise a set of scalar fields and a derived field associated with at least one of a summary, a formula, and a lookup;   storing the scalar fields in a relational database;   using the data model to generate a first query for the relational database to obtain data associated with the derived field using one or more joins of one or more of the scalar fields; and   enabling use of the first query to provide the data to a user during use of the application by the user.   
     
     
         16 . The non-transitory computer-readable storage medium of  claim 15 , the method further comprising:
 using the application to obtain an update to the data model from the user;   generating a second query for the relational database corresponding to the update; and   enabling use of the second query to propagate the update to the relational database during use of the application by the user.   
     
     
         17 . The non-transitory computer-readable storage medium of  claim 15 , wherein generating the first query to obtain data associated with the derived field involves:
 using the data model to create one or more views of the one or more tables; and   generating the query from the one or more views.   
     
     
         18 . The non-transitory computer-readable storage medium of  claim 17 , wherein using the data model to create the one or more views of the one or more tables involves:
 identifying one or more fields associated with the query;   determining necessary joins associated with the one or more fields;   transforming joined tables associated with the necessary joins; and   building the one or more views from the transformed joined tables.   
     
     
         19 . The non-transitory computer-readable storage medium of  claim 18 , wherein the necessary joins comprise nested joins. 
     
     
         20 . The non-transitory computer-readable storage medium of  claim 15 , wherein the derived field is associated with at least one of a summary, a formula, and a lookup.