This article describes the Object-relational mapping technique and its implementation in the Novulo software. It explains the need for ORM when combining object oriented programming with popular database products such as SQL and discusses its implementation in the Novulo development platform.
Most modern business application development projects use object-oriented technology such as Java or C# to build the application software while and a relational database system to store data. Data management tasks in object-oriented (OO) programming are typically implemented by manipulating objects that are almost always non-scalar values, while a relational database can only store and manipulate scalar values. To overcome this problem, O/R mapping is utilized to allow object data manipulation over a relational database.
The goal of ORM implementation is straightforward: converting data between incompatible type systems in relational databases and object-oriented programming languages. With ORM, objects can be stored in a relational database for easy retrieval while preserving the properties of objects and their relationships. This way, the objects outlive the execution of the program that created them, making them persistent.
There are two approaches for solving the incompatibility between object-oriented programming and relation databases. The programmer must either convert the object values into groups of simpler values for storage in the database and convert them back upon retrieval, or only use simple scalar values within the program. Object-relational mapping is used to implement the first approach.
In object-oriented programming, it would be possible to create data management tasks for every type of object (class). However, this is a tedious and error-prone task that slows down the development process. O/R mapping, however, reduces the lines of code programmed, making the software more robust (the fewer the lines, the fewer the errors that will occur).
OOP was first introduced to better cope with increasingly complex software while still maintaining a high level of quality. It strongly emphasizes discrete, reusable units of programming logic. The methodology focuses on data rather than processes, with programs composed of self-sufficient modules (objects), each containing all the information needed to manipulate its own data structure.
Objects in OOP are non-scalar values. A scalar variable can hold only one value at a time, whereas a non-scalar value (array, list, record, etc.) can hold multiple values. For example, an address book entry might hold zero or more phone numbers and zero or more email-addresses. Modeled in an object-oriented implementation, this might be represented by a "person" object with slots to hold data: the person's name, a list of phone numbers, and a list of email-addresses. The list of phone numbers itself would contain "phone number" objects and so on. In OOP, this address book entry is a single value that can have several methods associated with it, such as a method to return the preferred phone number.
A relational database groups data using common attributes found in the data set. The idea behind this approach is that data can be stored in a table, with the columns holding attributes for each row (record). For example, a data set of employees for a company can be grouped by birth year, last name, or salary. Typically for a relational database management system (DBMS), attributes are stored as scalar values (birth year, last name, salary might be stored as integer, character string and float). As a result, relational databases are currently the primary choice in storing financial records, manufacturing and logistical information, personnel data, and much more.
The OOP example of an address book entry immediately clarifies the problem to be addressed by ORM. Since a single address book entry can hold multiple phone numbers and multiple email addresses, there is no way to store the information correctly using scalar values. Storing the information in a single address book table would allow for only one phone number or email address per entry.
The solution to this problem is referencing. By using unique identifiers for each address book entry, multiple phone numbers can refer to the same address book entry using its unique identifier (Id). This reference is commonly called a "foreign key." With referencing, the example address book entry could be stored in a relational DBMS using the following tables:
In an object model, different types of multiplicity in relations are typically used. The object-relational mapping must allow for each of these to be stored in a relational database.
A solution that would avoid the need for ORM is to store information in an object-oriented database management system (OODBMS). An OODMBS is designed specifically for working with object-oriented values, and using it would eliminate the need to convert data to its relational form. Object-oriented databases, however, have yet to come into widespread use, due to a series of limitations that have yet to be overcome. One of their main limitations is that switching from an SQL DBMS to a purely object-oriented DBMS means loss of the capability to create SQL (Structured Query Language) queries, a tried and tested method for retrieving ad-hoc combinations of data. SQL databases form the predominant storage method for almost all software applications. Besides the widespread knowledge of SQL, the ability to interface more easily with other data sources is an important reason to choose implementation of ORM over and OODBMS.
Object Oriented Programming has become a standard technique for implementing software applications. It is supported in almost all modern programming languages (most prominently: Visual Basic .NET, C# and Java). The widespread use of relational database management systems encourages many developers to implement ORM in their applications, creating a virtual object database that can be used in the system. The popularity of relational (SQL) databases ensures that the object-relational mapping techniques will remain in active use for years to come.
Development in Novulo uses an object-oriented approach that generates object-oriented code and uses a relational DBMS (Novulo offers an alternate choice of DBMS, but MSSQL is standard). This implies that Novulo software uses object-relational mapping for data-management methods in its object classes. In most software, ORM is simply a means to an end, but in Novulo, its integration into the application model provides some unique features both in development and the resulting user-interface.
The Novulo Architect features a data model view, which demonstrates the tables and references (foreign keys) between the different tables, as deducted from the application model. Alternate design choices in the Architect's page editor can cause alternate constructions in the data model. This allows Architect users to create relations in the database without having to consider multiplicity; the right choice is instantly clear from the chosen interface composition. When placed in the interface detail for a project, a dropdown element that allows the user to choose an existing relation will trigger a one-to-many relation in the data model between the relevant tables. Some interface elements will trigger new tables themselves, such as the creation of a "link grid," which implies a new table linking other tables in a many-to-many relation. A project team table linking employees to projects is a good example of this construction: one employee can be involved in multiple projects, and a project can have multiple employees in a project team.
The dynamic data model in Novulo application models allows retrieving data from referenced objects. An object can have direct attributes (such as an invoice number for an invoice) and references to related objects (such as the relation that is the client for a certain project). In the latter example, when querying the project’s attributes, Novulo treats the foreign key (client) as an attribute of the project, granting access to the client's attributes through simple reference.
Suppose the following simple data model is derived from a Novulo application model:
This implies that a project has a reference to a relation, which in turn has a reference to an employee. The name of the foreign key attribute describes the nature of the relation. Through smart use of this derived knowledge of the data model, Novulo allows the application designer to intuitively choose related data from referenced objects. In a grid showing projects, the designer can simply choose the initials of the employee who is the account manager of the relation that is the client for the displayed project. All data management tasks are handled by Novulo's own ORM implementation, so that the complex SQL query that would be needed to retrieve the described information can be simplified to a very simple Novulo expression: "client.accountmanager.initials"
The expression language also allows the use of predefined, developer-created functions. One could choose to implement a function returning the employees preferred phone number and then automatically use that function in an expression. It is the clever use of the knowledge gained from the data model that allows the innovative intuitive approach to modeling in Novulo.
The expression language is not only used in the development process. It also plays an active part in the generated application interface. Each user can modify layout and data displayed in grids, adding new columns to overviews or filtering data based on user-defined constraints; The Novulo Expression language and clever ORM implementation grant maximum flexibility as standard application behavior!
The use of object-relational mapping in Novulo removes the need to query the data model, and instead offers methods for smart retrieval of information from the object model itself. There is no need to concern the developer with complex query constructions; the Novulo software effectively handles the mapping to the relational database, offering the freedom of querying the object model while using a stable and widely used DBMS.
The ORM implementation in Novulo empowers both the developer and end-user, streamlining data retrieval with the application model. The powerful features of the Novulo expression language and smart optimization for storage and retrieval in a relational database offer increased speed (of development, application use and data-retrieval), with improved stability and maximized compatibility.
ORM in Novulo