Data Model and Schema

Two Ways to Shape Data

A data model is the shape a database expects your data to have. A schema is the exact definition of that shape: which properties exist and what type each one is.

PostgreSQL and MongoDB shape data differently:

• PostgreSQL stores data in tables, like a spreadsheet. Every row has the same columns. You define the schema first.
• MongoDB stores data as documents, which look like JSON objects. Documents in the same collection can have different fields, plus nested objects and lists.

Think of a printed form vs a blank notebook page. A form has fixed boxes, so every entry looks the same and nothing is missed. A notebook lets each page look different, which is flexible but easy to get messy.

Same Menu, Two Models

In our food delivery app, menu items vary. A pizza has a crust and sizes, a biryani has a spice level, a drink has a volume.

PostgreSQL: one fixed set of columns for every dish. Properties a dish doesn't have are NULL (empty). Lists like pizza sizes need a separate table.

CREATE TABLE menu_items (
id            SERIAL PRIMARY KEY,
restaurant_id INT  NOT NULL,
name          TEXT NOT NULL,
price         INT  NOT NULL CHECK (price >= 0),
spice_level   TEXT,          -- NULL for pizzas and drinks
crust         TEXT,          -- NULL for biryani and drinks
volume_ml     INT            -- NULL for food
);

1id | name            | price | spice_level | crust | volume_ml
21  | Margherita      | 299   | NULL        | thin  | NULL
32  | Chicken Biryani | 249   | medium      | NULL  | NULL
43  | Cold Coffee     | 149   | NULL        | NULL  | 300

MongoDB: each dish stores only the fields it needs. Pizza sizes live inside the pizza document as an array (a list).

db.menuItems.insertMany([
{ restaurantId: 7, name: "Margherita", price: 299, crust: "thin",
  sizes: [ { size: "small", price: 199 }, { size: "large", price: 449 } ] },
{ restaurantId: 7, name: "Chicken Biryani", price: 249, spiceLevel: "medium" },
{ restaurantId: 9, name: "Cold Coffee", price: 149, volumeMl: 300 }
]);

No empty columns and no extra table. But this flexibility has a catch.

Flexible Doesn't Mean No Schema

By default, MongoDB accepts any document:

1{ name: "Veg Burger", Price: 129 }     ← capital P, a different field
2{ name: "Fries",      price: "99" }    ← text instead of a number
3{ nme: "Masala Dosa", price: 119 }     ← typo, no name field

All three save without error, then break queries and pages later. So you still enforce a schema, just in a different place:

• In the app: Mongoose (shown above) rejects bad data before saving.
• In the database: a $jsonSchema validator checks every write, including scripts and other services.

db.createCollection("menuItems", {
validator: {
  $jsonSchema: {
    bsonType: "object",
    required: ["restaurantId", "name", "price"],
    properties: {
      name:  { bsonType: "string" },
      price: { bsonType: "int", minimum: 0 }
    }
  }
}
});

Adding a New Field

Say every dish now needs isVeg.

When to Choose Which

Middle path: PostgreSQL's JSONB column stores JSON inside a normal table. Keep name and price as strict columns and put dish-specific details in one details JSONB column.

Interview Angle

Common mistake: calling MongoDB "schemaless". The schema still exists; it just isn't enforced by the table definition.

Summary

1PostgreSQL → tables → rows → fixed columns → schema-on-write
2MongoDB    → collections → documents → flexible fields → schema-on-read
3Varied data → Postgres: NULL columns + extra tables | Mongo: fields + arrays in one document
4Mongo risk  → bad data saved unless Mongoose or a $jsonSchema validator checks it
5Middle path → Postgres JSONB

Menu items belong to restaurants, and orders belong to users. Next, we'll see how each database connects related data: JOINs vs embedding and references.