Dynamic modeling of data in relational databases
View Patent ↗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.
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, and wherein the derived field comprises identifying information of at least one related field or table in the data model or in a relational database;
storing the scalar fields in the 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 based at least in part on the identifying information; 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:
one or more processors configured to execute an application, the 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 wherein the derived field comprises identifying information of at least one related field or table in the data model or in a relational database; and
store the scalar fields in the 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 based at least in part on the identifying information; 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, and wherein the derived field comprises identifying information of at least one related field or table in the data model or in a relational database;
storing the scalar fields in the 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 based at least in part on the identifying information; 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.