Object–Relational Impedance Mismatch

The previous lesson built a small mapper, and there the Loan type and the loan relation lined up one to one: every field matched a column, every relation matched a foreign key. That alignment comes from an easy example, not from a rule.

The object model and the relational model are products of two separate theories. In the object model, identity is reference, inheritance exists, links are two-way, and one object can hold other objects embedded inside it. In the relational model, identity is a key value, there is no inheritance, a link is built one-way through a foreign key, and a table is a flat list of tuples. The points where the two models do not line up are called the object–relational impedance mismatch. The mismatch is not a bug; it is a gap that needs to be closed. This lesson measures the four gaps in order.

The block below builds the database used in the first section of this lesson.

rm -f library.db
sqlite3 library.db <<'SQL'
CREATE TABLE member (member_id INTEGER PRIMARY KEY, first_name TEXT NOT NULL, last_name TEXT NOT NULL,
                  email TEXT, registered_at TEXT NOT NULL);
CREATE TABLE book (book_id INTEGER PRIMARY KEY, title TEXT NOT NULL, author TEXT NOT NULL,
                    publication_year INTEGER);
CREATE TABLE loan (loan_id INTEGER PRIMARY KEY, book_id INTEGER NOT NULL REFERENCES book(book_id),
                    member_id INTEGER NOT NULL REFERENCES member(member_id), pickup_date TEXT NOT NULL,
                    return_date TEXT);
INSERT INTO member VALUES (1,'Alice','Kane','[email protected]','2023-02-14'),
  (2,'Ben','Ortiz','[email protected]','2023-05-30'),(3,'Clara','Diaz',NULL,'2024-01-09');
INSERT INTO book VALUES (1,'Blindness','José Saramago',1995),(6,'Motherland Hotel','Yusuf Atılgan',NULL);
INSERT INTO loan VALUES (3,1,2,'2025-02-11',NULL),(11,6,2,'2025-06-11','2025-06-25');
SQL

Identity Mismatch

In a relation, two rows being “the same” means their primary keys are equal. In the object world there are two kinds of equality instead: being the same reference and carrying the same value. When a mapper converts a row into an object, these two equalities pull apart.

// identity-difference.mjs — reading the same row twice produces two separate objects
import { DatabaseSync } from "node:sqlite";
const db = new DatabaseSync("library.db");

const read = (id) => db.prepare("SELECT member_id, first_name, last_name FROM member WHERE member_id = ?").get(id);

const a = read(2);
const b = read(2);

console.log("same row (primary key):", a.member_id === b.member_id);
console.log("same object (reference):", a === b);

a.first_name = "Benedict";
console.log("a.first_name:", a.first_name, "| b.first_name:", b.first_name);

const cache = new Map();
const readCached = (id) => {
  if (!cache.has(id)) cache.set(id, read(id));
  return cache.get(id);
};
const c = readCached(2), d = readCached(2);
c.first_name = "Benedict";
console.log("same object with the identity map:", c === d, "| d.first_name:", d.first_name);

node identity-difference.mjs

same row (primary key): true
same object (reference): false
a.first_name: Benedict | b.first_name: Ben
same object with the identity map: true | d.first_name: Benedict

The two reads fetched the same row but produced two separate objects. A change made in one does not show up in the other; if both objects write back to the same row, one overwrites the other. The last line of the output shows the fix: the identity map built in the previous lesson represents every row with a single object for the lifetime of a request, and makes the two equalities coincide again.

This has a second consequence. An object not yet written to the database has no primary key. If you make the key the sole criterion for identity, two unsaved objects look equal to each other. This is why, in the mapping layer, new objects either come to life with an identifier generated by the application, or are compared only by reference equality until a key is assigned.

Inheritance Mismatch

If the library starts holding both print and audio works, a supertype and two subtypes appear in the object model: shared fields on top, specific fields underneath. In the relational model, there is no such thing as a subtype. The hierarchy can be brought down to tables in three ways.

rm -f inheritance.db
sqlite3 inheritance.db <<'SQL'
-- 1. Single table: all subtypes in one relation, with a discriminator column
CREATE TABLE single_table_work (
  work_id INTEGER PRIMARY KEY, kind TEXT NOT NULL, title TEXT NOT NULL, author TEXT NOT NULL,
  page_count INTEGER, duration_minutes INTEGER, narrator TEXT);
INSERT INTO single_table_work VALUES
  (1,'print','Blindness','José Saramago',352,NULL,NULL),
  (2,'print','The Disconnected','Oğuz Atay',724,NULL,NULL),
  (3,'audio','The Book of Sand','Jorge Luis Borges',NULL,214,'Nina Brooks'),
  (4,'audio','Yaban','Yakup Kadri',NULL,398,'Leo Marsh');

-- 2. Table per concrete type: shared columns repeat in every table
CREATE TABLE concrete_print (work_id INTEGER PRIMARY KEY, title TEXT NOT NULL,
  author TEXT NOT NULL, page_count INTEGER NOT NULL);
CREATE TABLE concrete_audio (work_id INTEGER PRIMARY KEY, title TEXT NOT NULL,
  author TEXT NOT NULL, duration_minutes INTEGER NOT NULL, narrator TEXT NOT NULL);
INSERT INTO concrete_print VALUES (1,'Blindness','José Saramago',352),(2,'The Disconnected','Oğuz Atay',724);
INSERT INTO concrete_audio VALUES (3,'The Book of Sand','Jorge Luis Borges',214,'Nina Brooks'),
  (4,'Yaban','Yakup Kadri',398,'Leo Marsh');

-- 3. Table per type: shared fields on top, subtype fields in the child relation
CREATE TABLE work (work_id INTEGER PRIMARY KEY, kind TEXT NOT NULL,
  title TEXT NOT NULL, author TEXT NOT NULL);
CREATE TABLE work_print (work_id INTEGER PRIMARY KEY REFERENCES work(work_id),
  page_count INTEGER NOT NULL);
CREATE TABLE work_audio (work_id INTEGER PRIMARY KEY REFERENCES work(work_id),
  duration_minutes INTEGER NOT NULL, narrator TEXT NOT NULL);
INSERT INTO work VALUES (1,'print','Blindness','José Saramago'),(2,'print','The Disconnected','Oğuz Atay'),
  (3,'audio','The Book of Sand','Jorge Luis Borges'),(4,'audio','Yaban','Yakup Kadri');
INSERT INTO work_print VALUES (1,352),(2,724);
INSERT INTO work_audio VALUES (3,214,'Nina Brooks'),(4,398,'Leo Marsh');
SQL

All three store the same four works. The difference shows up in the cost of a “fetch all works” request.

// inheritance-measure.mjs — query count and null-value count across three mapping strategies
import { DatabaseSync } from "node:sqlite";
const db = new DatabaseSync("inheritance.db");

function measure(name, queries) {
  let nullValues = 0, rows = 0;
  for (const s of queries) {
    for (const r of db.prepare(s).all()) {
      rows += 1;
      nullValues += Object.values(r).filter((v) => v === null).length;
    }
  }
  console.log(`${name.padEnd(22)} query=${queries.length}  row=${rows}  null_value=${nullValues}`);
}

measure("single table", ["SELECT * FROM single_table_work"]);
measure("concrete per type", ["SELECT * FROM concrete_print", "SELECT * FROM concrete_audio"]);
measure("per type (joined)", [
  `SELECT e.work_id, e.kind, e.title, e.author, b.page_count, s.duration_minutes, s.narrator
   FROM work e LEFT JOIN work_print b ON b.work_id = e.work_id
               LEFT JOIN work_audio  s ON s.work_id = e.work_id`,
]);

node inheritance-measure.mjs

single table           query=1  row=4  null_value=6
concrete per type      query=2  row=4  null_value=0
per type (joined)      query=1  row=4  null_value=6

The numbers give the character of the three strategies. Single table reads with one query but produces null values; every column specific to a subtype stays NULL on the other subtype’s rows. Subtype-specific fields cannot be made NOT NULL, because the constraint applies to every row. Table per concrete type produces no null values at all and allows the NOT NULL constraint; its cost is the query count, and every question asked through the supertype needs a union. Table per type reflects the hierarchy in the schema one to one; it asks for an outer join on read and an insert into two tables on write.

Table per concrete type costs one more constraint: a foreign key that refers to the whole hierarchy cannot be built.

sqlite3 inheritance.db <<'SQL'
PRAGMA foreign_keys = ON;
CREATE TABLE reservation (reservation_id INTEGER PRIMARY KEY,
  work_id INTEGER NOT NULL REFERENCES concrete_print(work_id), member_id INTEGER NOT NULL);
INSERT INTO reservation VALUES (1, 1, 3);
INSERT INTO reservation VALUES (2, 3, 3);
SQL

Runtime error near line 5: FOREIGN KEY constraint failed (19)

The reservation on the print work went through; the one on the audio work was rejected. A foreign key can point at only one relation; once the hierarchy is split across two relations, referential integrity is handed off to the application. If a reference to the supertype is needed, the table-per-type strategy is chosen, because there the work relation represents the whole hierarchy.

Relation Direction Mismatch

In an object graph, a link is one-way; if both directions are wanted, two separate references are kept, and the two are kept consistent by hand. In the relational model, a single foreign key column answers both directions.

// direction-difference.mjs — two directions on the object side, one foreign key on the relational side
import { DatabaseSync } from "node:sqlite";
const db = new DatabaseSync("library.db");

// Object graph: a two-way link. Both sides are held by hand.
const member = { id: 2, firstName: "Ben", loans: [] };
const loan = { id: 99, bookId: 6, member: null };

loan.member = member;                   // only one direction was set
console.log("loan.member.id:", loan.member.id);
console.log("member.loans.length:", member.loans.length);

member.loans.push(loan);                // the second direction is added by hand
console.log("after the second direction is set:", member.loans.length);

// Relational side: a single column carries both directions.
db.exec("CREATE TEMP TABLE temp AS SELECT * FROM loan");
db.prepare("INSERT INTO temp VALUES (?,?,?,?,NULL)").run(99, 6, 2, "2025-07-01");
const forward = db.prepare("SELECT member_id FROM temp WHERE loan_id = 99").get();
const backward = db.prepare("SELECT loan_id FROM temp WHERE member_id = 2 ORDER BY loan_id").all();
console.log("forward direction (loan -> member):", forward.member_id);
console.log("backward direction (member -> loan):", backward.map((r) => r.loan_id).join(", "));

node direction-difference.mjs

loan.member.id: 2
member.loans.length: 0
after the second direction is set: 1
forward direction (loan -> member): 2
backward direction (member -> loan): 3, 11, 99

On the object side, setting one direction left the other empty; on the relational side, a single row answered both questions. The mapping decision that closes this gap is choosing the owner of the relation: which side’s write changes the foreign key, and the other side only reads. If no owner is chosen, two updates coming from the two sides contradict each other, and which one lands on disk depends on call order.

In many-to-many relations the mismatch is even more visible. The link built with two lists on the object side has no relational counterpart; the junction table introduced in the SQL Fundamentals course is required. If the junction table has its own attributes (such as a pickup date), it is no longer just a link but an entity in its own right, and it must be reflected as an entity in the object model too.

Granularity Mismatch

In the object model, every concept can be its own type. If a member’s address is represented with an Address type, that type has no identity; two addresses are the same if they carry the same fields. Types with no identity, defined only by their value, are called value objects.

In the relational model, every table has a primary key. Give a value object a table, and you have invented an identity it does not have; do not give it one, and you embed its fields inside the owning relation. The second decision is called embedding, and it is usually the right one: the member relation carries the address_city, address_district, and address_postal_code columns, and the mapper builds the Address type from these three columns.

The criterion for the decision is sharing. If a value object is shared by more than one owner and needs to be updated from a single point, embedding is not enough; the concept is then really an entity and deserves an identity. In the library example, the address is specific to the member and gets embedded; the branch, by contrast, is shared by books and staff, and stays in its own relation.

Summary

• The object–relational impedance mismatch shows up at four points — identity, inheritance, relation direction, and granularity — and each one is closed by a separate mapping decision.
• Reading the same row twice produced two separate objects; the identity map restored the one-to-one link between a row and an object.
• The three inheritance strategies were measured: single table produced 1 query and 6 null values, table per concrete type produced 2 queries and 0 null values, and table per type produced 1 query and 6 null values.
• In the table-per-concrete-type strategy, a foreign key referring to the whole hierarchy could not be built; referential integrity was handed off to the application.
• The relation direction mismatch is closed by choosing an owner; the granularity mismatch is closed by embedding the value object into the owning relation.

Next Step

So far, the decisions that close the mismatch have been scattered through the calling code: which strategy was chosen, which side owns a relation, which field is embedded — this information repeats everywhere a query is written. The structure that gathers this information in one place, leaving the caller an interface that speaks only in domain concepts, is called the repository pattern. The next lesson builds this interface, and shows that the persistence detail is truly hidden by testing the business logic without a database, through a fake implementation of the repository.