Skip to content
Tutorials Center
My learning

Topics

Lesson 9 of 79 ยท Data Models and ER Modelling

Object-Oriented and Object-Relational Databases

Compare OODBMS and ORDBMS: objects, identity, user-defined types, inheritance and methods, plus how to mimic them in plain SQL with live examples you can run.

Programmers think in objects: a Customer has a name, an address that is itself a small structure, a list of phone numbers and methods such as fullName(). Relational tables think in flat rows of simple values. Moving data between the two worlds is tedious, and the mismatch even has a name, the impedance mismatch. In the 1980s and 1990s researchers asked an obvious question: why not store objects directly?

Two answers came out of that question. Object-oriented database systems (OODBMS) rebuilt the database around the object model. Object-relational database systems (ORDBMS) kept tables and SQL and added object features to them. The second answer won in the market, and almost every relational product you use today, PostgreSQL and Oracle above all, is an object-relational system in disguise. This lesson explains both approaches and shows how to get the same effects in plain SQL. It builds on the overview in Data Models.

Two ways to store objects

OODBMS Objects are the model. Queries navigate references.
ORDBMS Tables stay. Types, arrays and inheritance are added.
ORM A library maps objects to plain tables outside the database.

The market chose the second and third.

1 / 2

The object model in a nutshell

An object database is built from a handful of ideas borrowed from programming languages:

  • Object identity. Every object has an identifier (an OID) that is independent of its values. Two customers with the same name and address are still two objects. In a relational table identity comes from a key, which is a value; in an object database it comes from the system.
  • Complex values. Attributes can be structures, sets, lists or references to other objects. An order can hold its list of lines inside itself.
  • Encapsulation and methods. Data is accessed through operations defined with the type, such as order.total().
  • Classes and inheritance. A class describes a kind of object. A subclass inherits attributes and methods and adds its own.
  • Object references. An object points at another object directly. Queries follow pointers instead of joining on keys.

The standard for the model was the ODMG specification with its query language OQL, which looked like SQL with path expressions such as order.customer.address.city.

OODBMS: strengths and why it stayed niche

Pure object databases that followed the ODMG standard are excellent when an application is an object graph that is navigated far more than it is queried: CAD drawings, simulation models, some embedded systems. Following a pointer is a single memory-style hop, much cheaper than a join.

They stayed niche for several reasons. There was no single standard that vendors fully implemented, so every system was a lock-in. Ad-hoc, set-oriented queries and reporting were weaker than in SQL, where a mature optimizer, views, constraints and decades of tools already existed. Application languages changed, and the database was tied to one language's object layout. Finally, the huge installed base of relational data and skills did not want to be thrown away.

ORDBMS: extend the relational model

The object-relational approach, standardized in SQL:1999 and later versions, keeps relations as the core and relaxes the rule that every column holds an atomic value (the rule that first normal form enforces in the strict relational model). It adds:

Feature What it gives you Example in PostgreSQL
Structured (composite) types One column holding several fields CREATE TYPE address AS (street text, city text)
Collection types Arrays or multisets in a column phones text[]
Type inheritance Subtypes reuse a supertype's attributes defined by the SQL standard, rare in products
Table inheritance A child table gets the parent's columns CREATE TABLE student (...) INHERITS (person)
References A column that points at a row REF types in the standard (rare in practice)
User-defined functions Methods that run in queries CREATE FUNCTION full_name(person) ...
Semi-structured values Documents, XML, binary data jsonb, xml, bytea

Here is how they look in PostgreSQL. This is read-only because the browser database is SQLite, which has none of these features; the next sections show how to imitate them.

SQL
-- PostgreSQL (read-only)
CREATE TYPE address AS (
  street text,
  city   text,
  zip    text
);

CREATE TABLE person (
  id     serial PRIMARY KEY,
  name   text NOT NULL,
  home   address,                 -- a structured value in one column
  phones text[]                   -- a collection in one column
);

CREATE TABLE student (
  school text,
  year   int
) INHERITS (person);              -- student has every person column too

INSERT INTO person (name, home, phones)
VALUES ('Mia', ROW('4 Oak St', 'Leeds', 'LS1 4AB'), ARRAY['0113 555 0100']);

SELECT name, (home).city FROM person;      -- parentheses select a field
SELECT * FROM person;                      -- also returns student rows
SELECT * FROM ONLY person;                 -- only direct person rows

Notice what changed and what did not. You still write SQL, rows still live in tables and you can still join and aggregate. The type system became richer, and inheritance lets a query on person see its subtypes, which is called polymorphic querying.

Mimicking structured values in plain SQL

When a database lacks composite types, the usual stand-in is a JSON text column, which SQLite can read with json_extract. The first example stores an address and a list of phones inside a single column, then queries into them.

SQL Structured and collection values stored as JSON

Press Run (or Ctrl + Enter) to see the result here.

You get the convenience of a bundled value, but not the guarantees. SQLite does not check that home really has a city field, and the database cannot enforce a foreign key inside the JSON. A real composite type checks its fields, and in PostgreSQL you can index them. Use this stand-in for prototypes and for truly document-like data, and normalize into tables once structure and rules matter.

Mimicking inheritance with tables and views

Table inheritance in an ORDBMS is a convenience. The relational equivalent is a supertype table that holds shared attributes and a subtype table for each specialization, linked by the same key. A view recreates the polymorphic query over all subtypes, an idea you already met when mapping generalization in ER-to-relational mapping.

SQL Supertype and subtype tables with a polymorphic view

Press Run (or Ctrl + Enter) to see the result here.

The kind column plays the role of a type tag, and the CHECK constraint limits which subtypes exist. Unlike PostgreSQL's INHERITS, this design keeps real primary and foreign keys across the hierarchy, which is why it is the recommended pattern even in PostgreSQL. (Table inheritance there has a known limitation: unique and foreign key constraints do not span a parent and its children.)

Methods and encapsulation

In an object database, behavior is stored with the type. In an ORDBMS the nearest feature is the user-defined function, which can be used in a query like a method:

SQL
-- PostgreSQL (read-only)
CREATE FUNCTION full_address(a address) RETURNS text
LANGUAGE sql IMMUTABLE
AS $$ SELECT a.street || ', ' || a.city || ' ' || a.zip $$;

SELECT name, full_address(home) FROM person;

In plain SQL, a view or a stored-logic object (see Triggers and Stored Logic) does the same job. The idea is to define the rule once, next to the data, and let every program reuse it.

OODBMS, ORDBMS or ORM?

OODBMS ORDBMS Relational + ORM
Core structure Object graph Tables with richer types Plain tables
Query language OQL or language API SQL with extensions SQL generated by a library
Identity System OID Primary key (and sometimes OID) Primary key
Navigation Pointer hops Joins Joins or lazy loading
Ad-hoc queries Weaker Strong Strong
Typical use CAD, embedded, niche Most large RDBMS products Most web applications

In practice the choice is rarely "pure object database". Most teams use a relational engine and map objects with an ORM, or use PostgreSQL's richer types and jsonb where a column naturally holds structure.

Impedance mismatch and ORM

The word impedance comes from electrical engineering: connect two circuits whose electrical properties differ and part of the signal is lost. The object-relational impedance mismatch is the loss you get when an object-oriented program must keep its data in a relational database. The two worlds disagree in several ways:

Difference In the program In the database
Types Rich types: objects, lists, enums, decimals A fixed set of column types; no lists inside a cell
Structure Nested objects: an Order holds a list of Line objects Flat rows in separate tables, linked by keys
Identity Two variables are the same object if they refer to one memory address Two rows are the same if their primary keys are equal
Inheritance Classes extend classes No inheritance; it has to be encoded (see the section on mimicking inheritance above)
Navigation Follow references: order.customer.name Joins
Processing style One object at a time, in loops Whole sets of rows at a time

The last row explains the mismatch that bites first. A SQL query returns a set, but a loop in Java or Python wants one row at a time. The bridge is a cursor: a handle on a query result that hands out rows one by one with a fetch operation. Embedded SQL, JDBC, ODBC and Python's sqlite3 all work this way. Cursors are convenient, but they push work into the program that the database could do faster, which is the first trap.

Python Row-at-a-time cursor loop versus one set-based statement

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Both styles do the same work (the final price has been raised twice), but the loop sends about two thousand statements (the counter also includes the implicit transaction BEGIN and COMMIT) and moves every row through the program, while the set-based version needs one UPDATE. With a network between program and server, the difference is large.

Object-relational mappers

An object-relational mapper (ORM), such as Hibernate, JPA, SQLAlchemy, Django's ORM or Eloquent, automates the translation. You declare a class and say which table it maps to; the ORM generates the SQL for save, find and delete, builds objects from rows, and keeps an identity map so that loading the same primary key twice gives you the same object. Relationships between classes become foreign keys, and references between objects become joins, loaded either eagerly or lazily (when you first touch the property).

An ORM removes boilerplate and hides much of the mismatch, but it cannot remove it. The leak is easy to spot: lazy loading in a loop causes the N+1 query problem. One query loads N parent objects, and then touching a property of each parent runs N more queries. The example below builds a tiny lazy-loading mapper by hand to show it.

Python A hand-made ORM and the N+1 problem

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

With four authors the first version runs five queries and the second runs one. With ten thousand authors it would be ten thousand and one. ORMs offer switches for eager loading (joinedload, select_related, with()) exactly because of this, and a good habit is to log the SQL your ORM generates while you develop.

What to take away

  • Use an ORM for routine create, read, update and delete work, but still know the SQL it produces.
  • Keep bulk changes and reports set-based: a single UPDATE or an aggregate query beats any loop over objects.
  • Design the tables for the data first, then map the objects; the other order produces schemas that serve one program only.
  • Object-relational features (arrays, composite types, JSON columns) shrink the gap from the database side, as shown earlier in this lesson.

Check your understanding

Quick check ยท 6 questions

of 6 answered You scored / 6 ยท perfect!

  1. 1. What is the impedance mismatch?

    Application code works with nested objects and references, while relational tables hold flat rows of atomic values. Converting back and forth is the mismatch.
  2. 2. Which statement best describes an ORDBMS?

    An object-relational system extends the relational model rather than replacing it.
  3. 3. In an object database, what does an object identifier (OID) provide?

    Two objects with identical values are still different objects, because identity is assigned by the system.
  4. 4. Why is the supertype and subtype table design with a view recommended even where table inheritance exists?

    With separate tables sharing a key, constraints work across the hierarchy, whereas PostgreSQL table inheritance does not share unique or foreign key constraints between parent and children.
  5. 5. A page loads 50 authors and then, inside a loop, touches a lazily loaded books property of each one. How many queries does the ORM run?

    This is the N+1 problem: one query for the authors plus one per author. Eager loading (a join) fixes it.
  6. 6. Which of these is part of the impedance mismatch?

    Row-at-a-time loops against set-oriented queries is one of the core differences, along with nested structure, identity and inheritance.

Summary

  • Object databases store objects with identity, complex values, methods and inheritance; the impedance mismatch is what motivated them.
  • Pure OODBMS products stayed niche because of weak standards, weaker ad-hoc queries and language lock-in.
  • ORDBMS products add structured types, arrays, inheritance and user-defined functions to SQL, and PostgreSQL and Oracle are the best-known examples.
  • In plain SQL you can imitate the ideas with JSON columns, supertype/subtype tables and views.
  • Most applications today use a relational engine plus an ORM, with object-relational features where they help.
  • The impedance mismatch covers types, structure, identity, inheritance, navigation and processing style; cursors bridge sets to loops, and ORMs automate the mapping but can cause the N+1 query problem unless you load eagerly.

Next, read about temporal and spatial databases, or go back to the relational model.