CDM vs LDM vs PDM: The Complete Data Modeling Guide for Interview Success

Walk into any data architect or data engineer interview at an enterprise company and you'll hear some version of this question within the first 15 minutes: "Can you explain the difference between conceptual, logical, and physical data models?"

Most candidates know the textbook definitions. The ones who get offers know why each level exists, who it's for, what decisions it forces you to make, and — critically — how to demonstrate this knowledge in ERWIN or any other modeling tool.

This guide covers all of that, with a running example of an e-commerce system so you can see how the same domain looks across all three levels.

The three-level data modeling methodology

The three-level approach (also called the ANSI/SPARC three-schema architecture) was formalized in the 1970s and has remained the standard ever since. Each level answers a different question:

Conceptual Data Model (CDM): the whiteboard level

The CDM contains only entities (the major nouns of your domain) and relationships (how they connect). No attributes. No keys. No data types. Think of it as the diagram you'd draw on a whiteboard while interviewing a business stakeholder.

For an e-commerce system, the CDM might contain:

• Customer — people who buy things
• Order — a purchase transaction
• Product — items for sale
• Category — how products are organized
• Address — shipping locations

And these relationships: a Customer places many Orders. An Order contains many Products (M:N — resolved in LDM). A Product belongs to one Category. A Customer has many Addresses.

Customer ──(places)──► Order ──(contains M:N)──► Product
    │                                                   │
    └──(has)──► Address        Category ──(groups)──────┘

Logical Data Model (LDM): the architect's blueprint

The LDM takes the CDM and adds substance:

• Attributes — what data each entity stores (Customer Name, Email, Phone)
• Primary keys — what uniquely identifies each row (surrogate or natural)
• Foreign keys — which propagate automatically in ERWIN when you draw relationships
• Cardinality — exactly how many: one-to-many, one-to-one, mandatory vs optional
• M:N resolution — the Order ↔ Product many-to-many becomes an OrderLine bridge table
• Normalization — typically to 3NF

Order                     OrderLine                 Product
─────────────────         ─────────────────         ─────────────────
PK  order_id         1──M PK  order_line_id    M──1 PK  product_id
FK  customer_id           FK  order_id              FK  category_id
FK  address_id            FK  product_id                product_name
    order_date                quantity                   sku
    order_status              unit_price_at_sale         unit_price
    total_amount              line_total                 stock_qty

Notice that the LDM is database-agnostic. We're not saying VARCHAR(50) or BIGINT — we're saying "String" and "Number". The LDM can be implemented on PostgreSQL, Oracle, SQL Server, or MySQL.

Physical Data Model (PDM): what the database actually sees

The PDM targets a specific database engine. Everything becomes concrete:

• Number → BIGINT GENERATED ALWAYS AS IDENTITY (PostgreSQL)
• String → VARCHAR(50) NOT NULL
• Money → NUMERIC(12,2) NOT NULL (never FLOAT — rounding errors)
• Datetime → TIMESTAMPTZ DEFAULT NOW()
• Indexes on every FK column
• Check constraints for enums: CHECK (order_status IN ('Pending','Shipped','Delivered'))
• Sequences, identity columns, tablespaces

CREATE TABLE customer (
  customer_id  BIGINT GENERATED ALWAYS AS IDENTITY,
  first_name   VARCHAR(50)  NOT NULL,
  last_name    VARCHAR(50)  NOT NULL,
  email        VARCHAR(254) NOT NULL,
  created_at   TIMESTAMPTZ  DEFAULT NOW(),
  CONSTRAINT pk_customer PRIMARY KEY (customer_id),
  CONSTRAINT uq_customer_email UNIQUE (email)
);

CREATE INDEX idx_customer_email ON customer(email);

In ERWIN: switching between views

ERWIN maintains one model file that contains both the Logical and Physical layers simultaneously. You switch views using the dropdown at the top right: Model → Model Type → Logical or Physical.

When you switch to Physical, ERWIN applies your naming standard (converting "Customer ID" to "customer_id"), resolves your abstract data types to the target DB's types, and shows indexes and storage options that aren't visible in Logical.

The Forward Engineering dialog (Actions → Forward Engineer → Schema) then generates the complete SQL DDL from the PDM. You can preview it, choose which objects to include, and either save to a .sql file or push directly to a connected database.

Interview model answers

"What's the difference between CDM, LDM, and PDM?"

"The CDM is the whiteboard level — just entities and relationships, no attributes, audience is business stakeholders. The LDM adds attributes, primary/foreign keys, cardinality, and normalization — it's database-agnostic and the audience is data architects. The PDM targets a specific database, adds vendor-specific data types, indexes, constraints, and tablespaces — it's what generates the actual DDL."

"Why not skip straight from requirements to physical?"

"Because the most expensive mistakes in data modeling happen early. A CDM forces business alignment before you commit to a structure. An LDM forces normalization and key decisions before you're locked into a specific database. Each level catches a different class of error. Skipping levels means finding those errors in production."