Object-Relational Model

Overview

The Object-Relational (OR) model takes the relational model as its foundation — tables, tuples, SQL, ACID transactions — and extends it with object-oriented features: user-defined composite types, arrays, inheritance between tables, and methods/functions attached to types. It's an evolutionary bridge, not a replacement: you keep everything the relational model does well and add expressiveness for complex data. PostgreSQL is the textbook example (it was literally born from the Berkeley POSTGRES research project explicitly designed to add object-oriented concepts to relational databases); Oracle also has substantial object-relational extensions (object types, VARRAYs, nested tables).

How it works — a worked example

In PostgreSQL, you can define a composite (structured) type and use it as a column, plus use arrays and table inheritance directly in SQL:

sql
CREATE TYPE address AS (street text, city text, zip text);

CREATE TABLE customers (
  id serial PRIMARY KEY,
  name text,
  home_address address,           -- structured/composite column
  phone_numbers text[]            -- array column, not a separate join table
);

CREATE TABLE premium_customers (
  loyalty_tier text
) INHERITS (customers);           -- table inheritance

A query can now do SELECT (home_address).city FROM customers; or SELECT * FROM customers WHERE 'billing' = ANY(phone_numbers); — nested and multi-valued data live directly in a row without a separate normalized join table, while you still get full SQL, indexes, and transactions.

Edge cases and trade-offs

• Reduces (but doesn't eliminate) the impedance mismatch: composite types and arrays let a row look more like an application object, but you're still fundamentally working within SQL and tables — not full object semantics like OIDs or arbitrary method dispatch as in the pure OO model (3.4).
• Table inheritance has real query implications: a query on the parent customers table by default also returns rows from premium_customers (unless you use ONLY customers), which can surprise developers used to purely relational semantics.
• Array/composite columns can violate normalization on purpose: storing phone_numbers text[] directly in a row is a deliberate denormalization for convenience/performance, trading strict 1NF purity for fewer joins — a call to make carefully, since indexing and querying inside arrays is less mature than plain columns.
• Not the same as adding a JSON column: object-relational extensibility (custom types, inheritance, user-defined functions/operators) is a schema-level feature designed and typed up front, distinct from a semi-structured JSON/JSONB column bolted onto a table for flexible, evolving structure (which is closer in spirit to the Document model, 3.6, even though PostgreSQL supports JSONB too).

Key takeaways / interview Q&A

Q: How does the Object-Relational model differ from the pure Object-Oriented model? A: OR databases are still fundamentally relational — tables, SQL, ACID — merely extended with OO-flavored features like composite types, arrays, and inheritance; OO databases discard tables entirely in favor of objects with OIDs and native method dispatch as the storage unit.

Q: Give a concrete PostgreSQL feature that makes it "object-relational" rather than purely relational. A: User-defined composite types, array-typed columns, and table inheritance (CREATE TABLE ... INHERITS (...)) — all layered on top of standard relational tables and SQL.