A data model is usually built in three passes, each more concrete than the last. The conceptual model names the entities and how they relate - "a customer places orders, an order contains line items" - with no columns or types. The logical model fills in attributes, keys, and relationships in full but stays independent of any database engine. The physical model is the logical model realized on a specific platform: actual tables, data types, indexes, partitions. Conceptual answers what, logical answers what, precisely, physical answers how it's stored.

What the logical model captures

The logical model is where you get the structure right without worrying about performance. Entities become tables, attributes become columns, relationships become foreign keys, and you apply normalization - splitting data so every fact lives in exactly one place, eliminating the update anomalies that come from redundancy. A customer's address sits in a customer entity, not repeated on every order. The logical model says Customer has a customer_id and a signup_date; it doesn't yet say whether that's a BIGINT or a DATE, or whether the table is indexed.

What the physical model adds

The physical model makes the engine-specific decisions the logical model deliberately avoided. This is where you choose data types (VARCHAR(255) vs TEXT, DECIMAL(12,2) vs FLOAT), add indexes for the queries you actually run, partition large tables by date, set distribution and sort keys on an MPP warehouse, and frequently denormalize - collapsing the normalized logical structure back into wider tables or star schemas so reads don't pay for as many joins.

-- logical:  Customer ( customer_id PK, name, email, signup_date )

-- physical (PostgreSQL):
CREATE TABLE dim_customer (
  customer_id   BIGINT       PRIMARY KEY,
  name          VARCHAR(200) NOT NULL,
  email         VARCHAR(320),
  signup_date   DATE         NOT NULL
) PARTITION BY RANGE (signup_date);
CREATE INDEX idx_customer_email ON dim_customer (email);

The same logical model can produce different physical models on different engines - the logical design is portable, the physical design is tuned. In a warehouse the integration layer often stays close to a normalized logical model while the presentation layer's physical model is heavily denormalized; see EDW versus data marts.

Frequently asked questions

What's the difference between a logical and a physical data model? The logical model defines entities, attributes, keys, and relationships independent of any database; the physical model implements it on a specific engine with data types, indexes, partitioning, and storage settings.

Where does the conceptual model fit? It comes first - high-level entities and relationships only, no attributes or types - and is refined into the logical model.

Why normalize in logical but denormalize in physical? Normalization keeps the logical design free of redundancy and anomalies; denormalization in the physical model trades that purity for query speed by reducing joins.

Is the logical model engine-specific? No - that's the point. One logical model can be realized as different physical models on PostgreSQL, BigQuery, or Redshift, each tuned to its engine.