CSE Roadmap

Core tier · about 2 hours · 6 parts

Part 2 of 6 · 2 min read

The object-relational mismatch

A relational schema spreads one logical record across several tables; a document stores it as one nested tree.

Think of a filled-out order form clipped together with its own line items. Compare that to the same order split across separate filing drawers. One drawer holds the order header. Another holds every line item. A third holds the customer. Each piece is cross-referenced by order number.

In plain words: application code usually treats one order as a single object. That object holds the customer, the line items, and the total together. A relational schema stores the same order differently. The order becomes a row in orders. Its line items become separate rows in order_items. A join is needed to bring them back together on read. This gap between the in-memory object and the split-up relational storage is called the object-relational mismatch. A document database closes that gap. It stores the order as one tree, matching the in-memory object directly. No join is needed to read it back whole.

A worked example. Order 9001 belongs to customer 501. It holds two line items. One is a book priced at 24.99. The other is a different book priced at 18.50. As a relational schema, this is one row in orders plus two rows in order_items, joined by order_id. As a document, it is one JSON object with an embedded array of two line items. That array is read or written as a single unit.

The key lines.

-- Relational: one order, its items split into a separate table
SELECT o.order_id, o.customer_id, oi.book_id, oi.quantity, oi.unit_price
FROM orders o
JOIN order_items oi ON oi.order_id = o.order_id
WHERE o.order_id = 9001;
// Document: the same order as one self-contained tree
{
  "order_id": 9001,
  "customer_id": 501,
  "items": [
    {"book_id": 1, "quantity": 1, "unit_price": 24.99},
    {"book_id": 2, "quantity": 1, "unit_price": 18.50}
  ]
}

Object-relational mismatch, document model

The object-relational mismatch is the gap between an application's in-memory objects and a relational schema. In-memory objects naturally nest related data together. A relational schema spreads that same data across separate tables, joined by foreign keys. The document model closes this gap for data that naturally nests. It stores a record as one self-contained tree of fields, arrays and sub-objects. That tree is read and written as a single unit.

Example: order 9001 and its two line items, read as one JSON tree instead of a joined row from orders and order_items, is the document model closing the object-relational mismatch for this one order.