Relational or Document? Modeling the Same Bookstore Two Ways

Version note: The SQL (Structured Query Language) examples in this post were run on SQL Server 2025 (17.0, Cumulative Update 10), the document examples with MongoDB 8.3 and mongosh, and the example in the PostgreSQL note on PostgreSQL 18.6.

Guess First: Was the Order Spreadsheet a Document All Along?

Look once more at the order spreadsheet we have been normalizing since the start of the series: each row is an order, and the order's books, prices and quantities sit inside the same row. This looks a lot like an order document in a document database: the order and its lines in a single record. Guess: is moving to a document model a return to the flat table we spent five posts escaping?

It isn't, but the difference between the two is smaller than it first seems. The spreadsheet's problem was not that the order and its lines sat together; it was that the lines were parallel comma lists whose alignment could break, and that customer, book and author data was copied onto every row. A good document model solves the first structurally, and the second only if you think about it. In this post we build the same Bookstore requirements in a document model, compare it with the relational model, and discuss the right place of a JSON (JavaScript Object Notation) column in a relational database.

Thinking About the Same Requirements in a Document Model

The first question of relational modeling was "what is the data?": entities, attributes, dependencies. Access patterns only came to the table in the physical model, while choosing indexes. Document modeling turns the question around: "how will I access the data?" MongoDB's data modeling documentation states this as a principle; in our own words: data that is read together should be stored together. The access pattern list we noted in the 1st post of the series becomes a direct input of the model here: the order detail screen reads the order with its lines, the book page reads the book with its authors, and reviews are read separately, page by page.

db.books.insertMany([
  { _id: 26, isbn: "9798900000268", title: "Dune",
    authors: [{ authorId: 1, name: "Frank Herbert" }], listPrice: Decimal128("390.50") },
  { _id: 30, isbn: "9798900000305", title: "The Left Hand of Darkness",
    authors: [{ authorId: 2, name: "Ursula K. Le Guin" }], listPrice: Decimal128("660.00") },
  { _id: 41, isbn: "9798900000411", title: "A Wizard of Earthsea",
    authors: [{ authorId: 2, name: "Ursula Le Guin" }], listPrice: Decimal128("200.50") }
]);

db.orders.insertMany([
  { _id: 1001, orderedAt: ISODate("2026-03-02T00:00:00Z"), status: "Delivered",
    customer: { customerId: 1, name: "Ada Lovelace" },
    shippingAddress: { line: "Moda Cd. 12", district: "Kadikoy", city: "Istanbul" },
    items: [{ bookId: 26, title: "Dune", quantity: 1, unitPrice: Decimal128("390.50") }],
    total: Decimal128("390.50") },
  { _id: 1003, orderedAt: ISODate("2026-03-09T00:00:00Z"), status: "Delivered",
    customer: { customerId: 1, name: "Ada Lovelace" },
    shippingAddress: { line: "Moda Cd. 12", district: "Kadikoy", city: "Istanbul" },
    items: [
      { bookId: 30, title: "The Left Hand of Darkness", quantity: 1, unitPrice: Decimal128("660.00") },
      { bookId: 26, title: "Dune", quantity: 2, unitPrice: Decimal128("351.45") }
    ],
    total: Decimal128("1362.90") }
]);

printjson(db.orders.findOne({ _id: 1003 }));

{
  _id: 1003,
  orderedAt: ISODate('2026-03-09T00:00:00.000Z'),
  status: 'Delivered',
  customer: {
    customerId: 1,
    name: 'Ada Lovelace'
  },
  shippingAddress: {
    line: 'Moda Cd. 12',
    district: 'Kadikoy',
    city: 'Istanbul'
  },
  items: [
    {
      bookId: 30,
      title: 'The Left Hand of Darkness',
      quantity: 1,
      unitPrice: Decimal128('660.00')
    },
    {
      bookId: 26,
      title: 'Dune',
      quantity: 2,
      unitPrice: Decimal128('351.45')
    }
  ],
  total: Decimal128('1362.90')
}

The order's lines are an array; each line is a subdocument with its own fields. The alignment problem of the spreadsheet's four parallel lists does not exist here, because book, quantity and price sit together in the same subdocument. The order detail screen is a single read: work that needs three joins between orders, order_items, books and customers in the relational model. Prices are Decimal128; the FLOAT trap from the previous post applies just as much in the document world.

The Aggregate Boundary: What to Embed, What to Reference

The real design decision of a document model is which data is embedded in the same document and which lives in a separate collection and is referenced by its identity. Each decision in the example has a reason.

The lines are embedded because they belong to the order: they cannot exist without it (the weak entity from the 3rd post of the series), their number is bounded, and they are always read with the order. More importantly, the consistency of the order total with its lines is an invariant, and in MongoDB a write to a single document is atomic. If the order and its lines are in the same document, protecting this invariant needs no transaction. In DDD (Domain-Driven Design) this boundary is called the aggregate; the "Aggregate Tasarımı" post of the Domain-Driven Design series describes the aggregate as a consistency boundary, and in a document model this boundary usually coincides with the document boundary.

The customer is a reference, but their name and shipping address are copied. The customer lives independently of the order and has other orders; embedding them in every order would bring back the "Ada's phone" anomaly from the 5th post. The shipping address, however, is not a copy but a snapshot: where the order went must not change even if the customer moves. The line's title and unitPrice are of the same kind; the snapshot-versus-copy distinction we drew in the 8th post matters even more in a document model, because copying is so easy here.

Reviews are a separate collection. A book's reviews can grow without limit, and the documentation states that a MongoDB document can be at most 16 mebibytes. Even if you never reach the limit, a book document carrying hundreds of reviews on every read makes every request that shows the book card more expensive.

Querying and indexing embedded data is possible too. For the question "who bought Dune?" you can put an index on the field inside the array and query it:

db.orders.createIndex({ "items.bookId": 1 });

db.orders.find(
  { "items.bookId": 26 },
  { _id: 1, "customer.name": 1, orderedAt: 1 }
).forEach(printjson);

{
  _id: 1001,
  orderedAt: ISODate('2026-03-02T00:00:00.000Z'),
  customer: {
    name: 'Ada Lovelace'
  }
}
{
  _id: 1003,
  orderedAt: ISODate('2026-03-09T00:00:00.000Z'),
  customer: {
    name: 'Ada Lovelace'
  }
}

The index on items.bookId is a multikey index: a separate index entry is created for every element of the array. It is the counterpart of the index on order_items.book_id in the relational model.

Document Models Have Anomalies Too

Book documents carry their authors' names, because the book page always shows them together. But the data quality problem from the 6th post came along in the example too: the same author is "Ursula K. Le Guin" in one book and "Ursula Le Guin" in the other. Since the author's identity (authorId) is the same, fixing it is possible; but the fix has to be made not in one place but in every document the author appears in:

const r = db.books.updateMany(
  { "authors.authorId": 2 },
  { $set: { "authors.$[a].name": "Ursula K. Le Guin" } },
  { arrayFilters: [{ "a.authorId": 2 }] }
);
printjson({ matched: r.matchedCount, modified: r.modifiedCount });

{
  matched: 2,
  modified: 1
}

Two books matched, one changed. This is exactly the update anomaly from the 5th post; a document model is not expected to remove it, you just pay its cost. According to the MongoDB documentation, updateMany is atomic for each document separately but not as a whole operation; someone reading during the update may see two different names for the author. Because author names rarely change and the book page is read very often, this is a deliberate trade-off: the document-model version of the denormalization decision from the 8th post. The title in the orders was not updated, and must not be; it is a record of the moment of sale.

Normalizing in a Document Model and Running $lookup on Every Read

Someone coming to the document model with relational habits may split everything into separate collections: orders in one collection, lines in another, books in a third. MongoDB supports this; the joining tool is $lookup in the aggregation pipeline:

db.order_lines.insertMany([
  { orderId: 1003, bookId: 30, quantity: 1, unitPrice: Decimal128("660.00") },
  { orderId: 1003, bookId: 26, quantity: 2, unitPrice: Decimal128("351.45") }
]);

db.orders_split.insertOne({ _id: 1003, customerId: 1, status: "Delivered" });

db.orders_split.aggregate([
  { $match: { _id: 1003 } },
  { $lookup: { from: "order_lines", localField: "_id", foreignField: "orderId", as: "lines" } },
  { $unwind: "$lines" },
  { $lookup: { from: "books", localField: "lines.bookId", foreignField: "_id", as: "book" } },
  { $project: { _id: 0, title: { $first: "$book.title" }, quantity: "$lines.quantity" } }
]).forEach(printjson);

{
  title: 'The Left Hand of Darkness',
  quantity: 1
}
{
  title: 'Dune',
  quantity: 2
}

The result is correct, but you now hold the disadvantages of both models. The order detail is no longer a single read but a pipeline with two $lookup stages. Because lines and order are in different documents, the consistency of the total with the lines cannot be protected by single-document atomicity; MongoDB has multi-document transactions, but its documentation says they cost more than single-document writes and that a model with embedded data often removes the need for them. And the relational database's foreign keys, JOIN optimizer and constraints are not here. If you are joining on every read in a document database, either the model or the choice of database is wrong.

Schemaless Is Not Modelless

The word "schemaless", often used for document databases, means the database does not enforce a schema; it does not mean the application needs no schema. The schema is still there; only its location changes: it gets scattered into the code, into the assumptions of every service that reads the data. MongoDB lets you keep the schema in the database too if you want:

db.runCommand({
  collMod: "orders",
  validator: { $jsonSchema: {
    bsonType: "object",
    required: ["customer", "items", "total"],
    properties: {
      items: { bsonType: "array", minItems: 1, items: {
        bsonType: "object",
        required: ["bookId", "quantity", "unitPrice"],
        properties: {
          quantity: { bsonType: "int", minimum: 1 },
          unitPrice: { bsonType: "decimal" }
        } } }
    } } }
});

try {
  db.orders.insertOne({ _id: 1006, customer: { customerId: 2, name: "Alan Turing" },
    items: [{ bookId: 26, quantity: 0, unitPrice: 390.5 }], total: Decimal128("0") });
} catch (e) {
  print(e.errmsg);
  const item = e.errInfo.details.schemaRulesNotSatisfied[0]
    .propertiesNotSatisfied[0].details[0].details[0];
  printjson(item.propertiesNotSatisfied.map(p => ({ property: p.propertyName, reason: p.details[0].reason })));
}

Document failed validation
[
  {
    property: 'quantity',
    reason: 'comparison failed'
  },
  {
    property: 'unitPrice',
    reason: 'type did not match'
  }
]

The line with a quantity of zero and a double price was rejected. This validator is the counterpart of a CHECK and a column type in a relational table. The real benefit of a flexible schema is being able to add a new field without rewriting old documents; it is not permission to stop thinking about the schema. Which fields are mandatory, what the types are, and which data is embedded and which referenced need the same care as the decisions we made for the relational model in this series. The depth of document modeling, schema patterns and index strategies are in the MongoDB series.

JSON in a Relational Database: The Right Place and the Limit

The question is not always "one or the other". Relational databases support JSON as a data type, and it has two legitimate uses. We saw the first in the 9th post: keeping metadata specific to a book's format, which nobody joins on, in a SQL Server 2025 native json column, constraining it with CHECK and indexing the frequently filtered field through a computed column. The second goes the other way: keep the data relational and give it to the API (Application Programming Interface) as a document. With SQL Server 2025's JSON_OBJECT and JSON_ARRAYAGG functions, the order document can be produced directly from normalized tables:

SELECT JSON_OBJECT(
           'orderId': o.order_id,
           'orderedAt': o.ordered_at,
           'status': o.status,
           'customer': JSON_OBJECT('customerId': c.customer_id,
                                   'name': c.first_name + N' ' + c.last_name),
           'items': (SELECT JSON_ARRAYAGG(JSON_OBJECT('bookId': oi.book_id, 'title': b.title,
                                                      'quantity': oi.quantity,
                                                      'unitPrice': oi.unit_price))
                     FROM dbo.order_items AS oi
                     JOIN dbo.books AS b ON b.book_id = oi.book_id
                     WHERE oi.order_id = o.order_id)
       ) AS order_document
FROM dbo.orders AS o
JOIN dbo.customers AS c ON c.customer_id = o.customer_id
WHERE o.order_id = 1;

{"orderId":1,"orderedAt":"2025-01-02T09:19:25","status":"Delivered","customer":{"customerId":17,"name":"Bjarne Stroustrup"},"items":[{"bookId":47,"title":"Murder on the Orient Express","quantity":2,"unitPrice":290.90},{"bookId":19,"title":"Frankenstein","quantity":1,"unitPrice":460.90}]}

(1 rows affected)

The read side is shaped like a document, the write side is normalized and constrained. The details of these functions are in the "SQL Server 2025 ile T-SQL'e Gelenler" post of the SQL Temelleri series. In PostgreSQL the counterpart is the jsonb type with a GIN (Generalized Inverted Index) index; with the containment operator @> you can search for a part of the document:

CREATE TABLE books (
    book_id  integer PRIMARY KEY,
    title    text    NOT NULL,
    details  jsonb   NOT NULL DEFAULT '{}'
);
CREATE INDEX ix_books_details ON books USING gin (details);

INSERT INTO books VALUES
    (26, 'Dune',             '{"format": "ebook", "fileSizeMb": 4.2, "drm": false}'),
    (28, 'Foundation',       '{"format": "ebook", "fileSizeMb": 7.8, "drm": true}'),
    (34, 'Hyperion',         '{"format": "audiobook", "narrator": "Grace Hopper"}');

SELECT book_id, title, details ->> 'fileSizeMb' AS file_size_mb
FROM books
WHERE details @> '{"format": "ebook", "drm": false}';

 book_id | title | file_size_mb 
---------+-------+--------------
      26 | Dune  | 4.2
(1 row)

The operators and index types of jsonb are covered in detail in the PostgreSQL series. The limit is the same in both databases: the moment a field inside the JSON becomes the subject of a join, a foreign key or a report, that field has earned the right to be a column. A JSON column should stay a small document island inside the relational model; it should not replace the model itself.

A Decision Table: Relational or Document?

The decision is made by the shape of the data and of access, not by the database's popularity. The table below sums up which side comes out ahead on each of four questions:

For the Bookstore the answer is mostly relational: catalog, orders, payments and reports are tightly related, and the reports of the previous post showed how real the need for ad-hoc queries is. But the order history screen, the format-specific part of the product catalog or a read model fed by events are good candidates to be kept in document shape. The SQL-or-NoSQL discussion on the scaling side is in the "Veri Katmanını Ölçeklemek: Replication, Sharding ve SQL mi NoSQL mi?" post of the System Design series.

In Production

Start the document model from the access pattern list and keep the list together with the model. Each embedding decision rests on an access pattern; when the access pattern changes, the model should change too. A new request like "show the book's last five reviews on the book page" may require embedding a copy of the latest reviews in the book document.

Watch growing arrays. Every place where an embedded array can grow without limit (reviews, log lines, followers) sooner or later turns into the document size limit or into unnecessary data carried on every read. Before embedding, ask "what is the upper bound of this array?"

Write the update path for every copied field. Copying the author name into book documents means an updateMany that runs when the author name changes. It is the document-model counterpart of the consistency table from the 8th post: who updates the copy, and when?

Put the validator in from the start. When a validator is added to a collection later, existing documents are not checked at that moment; bad data stays there until someone tries to update it. The earlier the rules go in, the less data there is to clean.

Common Pitfalls

Reading "schemaless" as "modelless". The database not enforcing a schema does not mean the application runs without one; it only means the rules are scattered across the code.

Normalizing in a document model and running $lookup on every read. This loses the guarantees of a relational database while keeping the cost of joins.

Embedding everything in a single document. Embedding all of a customer's orders in the customer document means an array that grows without limit and rewriting a huge document on every order. An order is an aggregate with its own life cycle; it should be a separate document.

Updating a snapshot. A script that updates the titles in orders when a book's title changes alters the content of past invoices. Whether a copied field is a copy or a record of that moment should be written in the model.

Using a JSON column as an escape hatch in a relational database. Putting everything into a JSON column to "decide later" brings back the EAV (Entity-Attribute-Value) problems of the 9th post with more modern syntax.

A Small Experiment

★☆☆ Add Alan Turing's order 1002 (The Pragmatic Programmer, 1150.00) to the orders collection in a form that passes the validator. Which fields did you write from the customer reference and which as a snapshot?

★★☆ Design a reviews collection and meet the book page's "last five reviews and average rating" need. Do you keep the average in the book document or compute it every time? Use the criteria from the 8th post.

★★★ Turn the query in j1.sql into a view and write a second query that returns all of a customer's orders as a single JSON array. How would you have to change the model to serve the same screen with a single read in MongoDB, and what would that cost on the write side?

We modeled the same Bookstore as relational, dimensional and document. In the last post of the series we bring it all together: starting from a new set of requirements (pre-orders, coupons, stock reservations), we go through the entity list, the ER (Entity-Relationship) diagram, dependencies and normal forms to the physical schema, and finish with a schema review checklist.