If You Know SQL: One Store Designed Both Ways

Riverbend Bike Shop's store as SQL tables beside the same store as MongoDB documents, the words that carry over from one to the other, and the relational habits that don't.

Journeyman · MongoDB · SQL · Updated

By

After this lesson you can

  • Match relational words such as table, row, column, primary key, foreign key and JOIN to their MongoDB counterparts
  • Explain what changes when orders and order_lines become one document, from embedded lines to references nobody checks

Key takeaways

  • A table becomes a collection, a row a document, a column a field, and a primary key the _id.
  • A child table that's always read with its parent, such as order_lines, becomes an array inside the parent's document.
  • Data that many records share, such as customers and products, stays in its own collection, linked by an _id stored as a reference.
  • MongoDB doesn't check a reference the way a foreign key does, so an order can point to a product that doesn't exist.
  • Where SQL joins tables when it reads, a document copies a few fields, such as a product's name, so one read is enough.

Optional: this lesson is for readers who already know SQL. Nothing later in the course depends on it.

If you've built apps on SQL Server, PostgreSQL or MySQL, sooner or later you'll join a team that uses MongoDB too, or move an app from one to the other. The quickest way to bring what you know along is to see one store designed both ways. The examples use the small set of Riverbend Bike Shop's data that Transactions, and Why You'll Need Them Less started, with its own names and ids, not the catalog from the earlier chapters: three customers, six products and three orders. The SQL is standard SQL, and its output comes from SQL Server.

The store as tables

A relational design gives each kind of thing its own table and stores each fact once, which is called normalization. customers and products are plain tables, each with a primary key. An order is split across two tables: orders, one row per order, and order_lines, one row per item in it:

CREATE TABLE orders (
  order_id    INT PRIMARY KEY,
  customer_id INT NOT NULL,
  status      VARCHAR(20) NOT NULL,
  ordered_on  DATE NOT NULL,
  CONSTRAINT fk_orders_customer FOREIGN KEY (customer_id) REFERENCES customers (customer_id)
);

CREATE TABLE order_lines (
  order_id    INT NOT NULL,
  line_number INT NOT NULL,
  product_id  INT NOT NULL,
  price       DECIMAL(10, 2) NOT NULL,
  quantity    INT NOT NULL,
  PRIMARY KEY (order_id, line_number),
  CONSTRAINT fk_lines_order FOREIGN KEY (order_id) REFERENCES orders (order_id),
  CONSTRAINT fk_lines_product FOREIGN KEY (product_id) REFERENCES products (product_id)
);

The foreign keys are rules the database enforces: a line can only name an order and a product that exist. Reading Maya's order 1001 back means joining four tables:

SELECT o.order_id, c.name AS customer, p.name AS product, l.price, l.quantity
FROM orders o
  JOIN customers c ON c.customer_id = o.customer_id
  JOIN order_lines l ON l.order_id = o.order_id
  JOIN products p ON p.product_id = l.product_id
WHERE o.order_id = 1001
ORDER BY l.line_number;
order_id  customer  product           price  quantity
--------  --------  ----------------  -----  --------
1001      Maya      Beam 400 Light    34.00  2
1001      Maya      Trailhead Helmet  59.00  1

The same store as documents

MongoDB starts from a different question: what's read together? An order's lines are never shown without their order, so they move inside it, as an array. Customers and products are shared by many orders, so they stay in collections of their own, and each order keeps their _ids as references:

db.orders.findOne({ _id: 1001 })
{
  _id: 1001,
  customerId: 7,
  status: 'placed',
  orderedOn: ISODate('2026-11-02T00:00:00.000Z'),
  lines: [
    {
      productId: 3,
      name: 'Beam 400 Light',
      price: Decimal128('34.00'),
      quantity: 2
    },
    {
      productId: 2,
      name: 'Trailhead Helmet',
      price: Decimal128('59.00'),
      quantity: 1
    }
  ]
}

Two tables became one document, and four joins became one read. Each line also copies the product's name, as Copying a Few Fields: the Extended Reference Pattern showed, so the order page needs no trip to products.

Two views of Riverbend's order 1001 side by side. On the left, four tables, customers, orders, order_lines and products, with arrows from each foreign key column to the key it names. On the right, one order document with its two lines inside it as an array, and two dashed arrows from customerId and productId out to the customers and products collections.

The words that map

Relational MongoDB
database database
table collection
row document
column field
primary key _id
foreign key a reference: another document's _id, stored in a field
a child table, such as order_lines an array embedded in the parent, such as lines
JOIN embedding, so no join is needed; or $lookup in a pipeline

The habits that don't carry over

Nobody checks a reference. A foreign key refuses a line for product 99, which doesn't exist:

INSERT INTO order_lines (order_id, line_number, product_id, price, quantity)
VALUES (1001, 3, 99, 10.00, 1);
Msg 547, Level 16, State 1, Line 1
The INSERT statement conflicted with the FOREIGN KEY constraint "fk_lines_product". The conflict occurred in database "riverbend", table "dbo.products", column 'product_id'.
The statement has been terminated.

MongoDB takes an order for the same missing product without a word:

db.orders.insertOne({
  _id: 1004, customerId: 7, status: "placed", orderedOn: ISODate("2026-11-17"),
  lines: [ { productId: 99, name: "Mystery Saddle", price: NumberDecimal("10.00"), quantity: 1 } ]
})
{ acknowledged: true, insertedId: 1004 }

In MongoDB, keeping references right is your app's job.

The shape is yours to enforce. Every row of a table has every column. Documents in one collection can differ, unless you add a validator, as Schema Validation with $jsonSchema shows.

Copies are on purpose. A normalized design stores a product's name once. The order document copies it, so when Riverbend renames a product, old orders keep the name the customer bought. That's usually right for an order, but every copy is a place to update when it should follow the original.

Changes across tables are less common. In SQL, a transaction that touches four tables is everyday work. In MongoDB, a good shape keeps most changes inside one document, as Transactions, and Why You'll Need Them Less explains.

Check yourself

2 questions to try on the lesson page.

So what

You can now read a relational design and see its document shape: child tables fold in, shared data stays separate, and a few fields get copied. The habits to leave behind are the ones the database used to enforce for you.

Key terms

Related