Relationships (JOINs vs embedding/references)

Two Ways to Connect Data

Real data is connected. In our food delivery app, a user places many orders, each order comes from one restaurant, and each order contains several items. This connection is called a relationship.

PostgreSQL and MongoDB connect data differently:

Think of a library vs a lunchbox. A library keeps every book in one place and you collect the ones you need (JOIN). A lunchbox packs everything for one meal together, ready to open (embedding).

Relationship Types

PostgreSQL: Foreign Keys and JOINs

Each kind of thing gets its own table. A foreign key is a column that stores another table's id, and the database checks that id actually exists.

1users            orders                       order_items
2┌────┬───────┐   ┌─────┬─────────┬────────┐   ┌──────────┬─────────────────┬─────┐
3│ id │ name  │   │ id  │ user_id │ rest_id│   │ order_id │ dish            │ qty │
4├────┼───────┤   ├─────┼─────────┼────────┤   ├──────────┼─────────────────┼─────┤
5│ 42 │ Riya  │ ← │ 901 │ 42      │ 7      │ ← │ 901      │ Chicken Biryani │ 2   │
6└────┴───────┘   └─────┴─────────┴────────┘   │ 901      │ Cold Coffee     │ 1   │
7                                              └──────────┴─────────────────┴─────┘

To show one order with its user and items, you JOIN the tables: match rows where the ids line up.

-- relationship defined once
CREATE TABLE orders (
id      SERIAL PRIMARY KEY,
user_id INT NOT NULL REFERENCES users (id),       -- foreign key
rest_id INT NOT NULL REFERENCES restaurants (id)
);

-- read it together
SELECT o.id, u.name AS customer, r.name AS restaurant, oi.dish, oi.qty
FROM orders o
JOIN users u        ON u.id = o.user_id
JOIN restaurants r  ON r.id = o.rest_id
JOIN order_items oi ON oi.order_id = o.id
WHERE o.id = 901;

What you gain: each fact is stored once. If Riya changes her name, one row changes and every order shows the new name. Foreign keys block broken links, like an order for a user who doesn't exist.

What you lose: every read that needs connected data pays for the JOIN. With many large tables, JOINs get expensive.

MongoDB Option 1: Embedding

Embedding means putting related data inside the parent document. An order's items are only ever read with that order, so they go inside it:

1{
2  _id: 901,
3  userId: 42,
4  restaurant: { id: 7, name: "Biryani House" },     ← small copy for display
5  items: [
6    { dish: "Chicken Biryani", qty: 2, price: 249 },
7    { dish: "Cold Coffee",     qty: 1, price: 149 }
8  ],
9  total: 647
10}

One read returns the whole order. No JOIN needed.

Copying the restaurant name into the order is called denormalization: storing the same fact in more than one place to make reads faster. Here it's even correct: the order should keep the name and price at the time it was placed, even if the menu changes later.

MongoDB Option 2: References

A reference stores another document's _id, like a foreign key. Use it when the related data is big, shared, or changes on its own. A user has thousands of orders and edits their profile separately, so orders store userId instead of the full user.

To read them together, use $lookup (MongoDB's JOIN-like step) or Mongoose's populate:

db.orders.aggregate([
{ $match: { _id: 901 } },
{ $lookup: {
    from: "users",            // collection to join
    localField: "userId",     // field in orders
    foreignField: "_id",      // field in users
    as: "user"                // result goes into this array
} }
]);

$lookup works, but MongoDB isn't built around it the way PostgreSQL is built around JOINs. If most of your queries need it, the data is probably relational and PostgreSQL may fit better.

Trade-offs

Interview Angle

Common mistakes:

• Treating MongoDB like SQL: one collection per table, references everywhere, $lookup on every query.
• Embedding data that grows without limit, like every order inside the user document.

Summary

1PostgreSQL → separate tables + foreign keys → JOIN when reading → each fact stored once
2Mongo embed     → nest inside the parent → one read → copies may go stale
3Mongo reference → store the _id → $lookup / populate → extra step
4Embed when      → read together + small
5Reference when  → large, shared, or changes on its own

Embed or reference is the core decision in MongoDB design. Next, we'll turn it into clear rules, including the 16 MB document limit, in Mongo Schema Design.