Object-relational database

An object-relational database (ORD) or object-relational database management system (ORDBMS) is a relational database management system that allows developers to integrate the database with their own custom data types and methods. The term object-relational database is sometimes used to describe external software products running over traditional DBMSs to provide similar features; these systems are more correctly referred to as object-relational mapping systems.

Whereas RDBMS or SQL-DBMS products focused on the efficient management of data drawn from a limited set of data types (defined by the relevant language standards), an object-relational DBMS allows software developers to integrate their own types and the methods that apply to them into the DBMS. The goal of ORDBMS technology is to allow developers to raise the level of abstraction at which they view the problem domain.

Comparison to RDBMS

In an RDBMS, it would be fairly common to see SQL statements like this:

  CREATE TABLE Customers  (
      Id          CHAR(12)    NOT NULL PRIMARY KEY,
      Surname     VARCHAR(32) NOT NULL,
      FirstName   VARCHAR(32) NOT NULL,
      DOB         DATE        NOT NULL
   );

   SELECT InitCap(Surname) || ', ' || InitCap(FirstName)
     FROM Customers
    WHERE Month(DOB) = Month(getdate())
      AND Day(DOB) = Day(getdate())

which some OO fans would describe as overly complex logic. Furthermore, most current SQL databases allow the creation of custom functions, which would allow the query to be expressed as:

   SELECT Formal(Id)
     FROM Customers
    WHERE Birthday(Id) = Today()

In an object-relational database, one might see something like this, where the data types and expressions such as BirthDay() are user-defined.

   CREATE TABLE Customers (
     Id           Cust_Id     NOT NULL  PRIMARY KEY,
     Name         PersonName  NOT NULL,
     DOB          DATE        NOT NULL
   );

   SELECT Formal( C.Name )
     FROM Customers C
    WHERE BirthDay ( C.DOB ) = TODAY;

Another advantage to the object-relational model is that the database can make use of the relationships between data to easily collect related records. In an address book application, an additional table would be added to the ones above to hold zero or more addresses for each user. Using a traditional RDBMS, collecting information for both the user and their address requires a "join":

    SELECT InitCap(C.Surname) || ', ' || InitCap(C.FirstName), A.city
      FROM Customers C, Addresses A
     WHERE A.Cust_Id=C.Id -- the join
       AND A.city="New York"

The same query in an object-relational database is much simpler:

   SELECT Formal( C.Name )
     FROM Customers C
    WHERE C.address.city="New York" -- the linkage is 'understood' by the ORDB

History

Many of the concepts in ORDBMS's were pioneered in the Postgres database, which eventually led to the open source PostgreSQL which is available today and gaining mind share. The first of the major database vendors to add object-relational support to their products was Informix, which they did by purchasing the Illustra ORDBMS written by a number of former Postgres employees.

Most modern DBMS products, such as IBM's DB2, Oracle database, and Microsoft SQL Server, make claims to support this technology and do so with varying degrees of success.

External links

• An interesting discussion about object-oriented vs. relational databases (http://slashdot.org/article.pl?sid=03/09/23/2016224&threshold=4&mode=nested)
• PolePosition Benchmark (http://www.polepos.org) -- shows the performance trade-offs for solutions in the object-relational impedance mismatch context.
• RDBMS != Object Store (http://slashdot.org/comments.pl?sid=79683&cid=7039736&threshold=-1&mode=nested) -- great explanation about differences of relational databases and object stores.de:Objektrelationale Datenbank