Levels of Data Modeling

Data modeling proceeds through three levels, each a different lens on the same business reality at an increasing degree of detail. You start with what the business cares about, then pin down structure, and finally commit to one database engine.

1. Conceptual — the entities and relationships, in business terms.
2. Logical — attributes, data types, keys, and normalization, still engine-independent.
3. Physical — a concrete schema tuned for one DBMS, including types, indexes, and storage.

The value of separating these levels is that decisions made for the wrong reasons (a stakeholder conversation contaminated by index choices, or an index choice driven by a half-remembered business rule) get pushed to where they belong. Each level is a checkpoint you can validate before adding more commitment.

Conceptual data model

A conceptual data model is a high-level overview of the data structure. It identifies the main entities and the relationships between them, and deliberately leaves out attributes, data types, and constraints. Because it is technology-independent and speaks in business terms, it is the right artifact to put in front of stakeholders to confirm that the data requirements match the business goals.

• Represents core entities (real-world objects) and their relationships.
• Omits detailed attributes and data types.
• Stays technology-independent, focused purely on business concepts.

For an e-commerce platform, a conceptual model might hold Customer, Order, and Product, with relationships such as a Customer places an Order and an Order contains a Product.

Logical data model

A logical data model adds structure without committing to an engine. It defines the attributes of each entity and their general data types (text, number, date), specifies primary and foreign keys to wire entities together, and applies normalization to cut redundancy and protect integrity. It is more detailed than the conceptual model but still technology-agnostic.

• Defines attributes and their general data types for each entity.
• Specifies primary keys and foreign keys to express relationships.
• Applies normalization to reduce redundancy and preserve integrity.
• Remains engine-independent — structure, not implementation.

In the e-commerce example, Customer gains CustomerID, Name, and Email. An Order carries CustomerID and ProductID as foreign keys, linking each order back to the customer who placed it and the product it is for.

Physical data model

The physical data model is the final stage, where the logical model is reshaped to fit one specific database management system. It states exactly how data is stored — concrete column types, indexes, partitioning, and access methods — and is tuned for performance on the chosen engine.

• Uses DBMS-specific data types (for example VARCHAR(255) and INT in SQL).
• Considers indexing and partitioning for query performance.
• Maps relationships onto real database constructs — tables, columns, foreign-key constraints.
• Captures storage detail such as file structures and disk layout.

In the e-commerce example, Customer becomes a real Customer table with VARCHAR(255) for Name and Email and an index on CustomerID to speed lookups.

The three levels side by side

Common pitfalls

Treating the conceptual model as a database schema

The conceptual model is for the conversation with the business, not for the engine. If you start arguing about column types or indexes while drawing it, you have collapsed three levels into one and lost the checkpoint that catches business-rule mistakes early.

Assuming a foreign key is automatically indexed

Declaring a FOREIGN KEY constraint creates the constraint, not necessarily a usable index on the referencing column — and the behavior is engine-specific, so do not generalize one engine's rule to another:

• PostgreSQL: a PRIMARY KEY is backed by an index automatically, but a plain FOREIGN KEY column gets no index. You must add it yourself (for example CREATE INDEX ON orders (customer_id);). Without it, joins on that column and ON DELETE CASCADE cleanups do sequential scans. (Verified empirically on PostgreSQL 14.7: a plain FK column has no supporting index.)
• MySQL / InnoDB: the opposite — InnoDB requires an index on the referencing column and creates one automatically if a suitable index does not already exist. Per the MySQL manual: “Such an index is created on the referencing table automatically if it does not exist.” So on InnoDB you generally do not need to add the FK index by hand.

The takeaway: this is a physical-model concern, and the right answer depends on your DBMS. Confirm your engine's behavior rather than carrying a habit from one database to another.

Over-normalizing the physical model

Normalization belongs in the logical model. At the physical level, read patterns may justify a deliberate, documented denormalization. Skipping that judgment in either direction — never denormalizing, or denormalizing reflexively — is a design decision made by default rather than on purpose.

Source

Adapted and expanded from the GeeksforGeeks article Levels of Data Modeling (conceptual, logical, and physical levels and the e-commerce example). Engine-specific indexing behavior verified against the MySQL 8.0 Reference Manual (FOREIGN KEY constraints — InnoDB auto-creates the referencing-column index) and against PostgreSQL 14.7 behavior (a plain foreign-key column receives no automatic index), consistent with the PostgreSQL documentation.

🎯 STRICT STANDOUT: Why / mental model / when-not / worked / failure / hostile panel — Levels of Data Modeling

Why this concept exists (judgment layer)

Conceptual / logical / physical separation keeps stakeholder language, normalized structure, and engine tuning from contaminating each other. The Postgres-vs-MySQL FK index pitfall is a pure physical-level trap that ships as a production outage.

Mental model (install this intuition)

Conceptual = nouns & verbs only. Logical = keys, types, NF, still portable. Physical = VARCHAR sizes, indexes, partitioning, storage — for one DBMS. Wrong-level decisions (arguing B-tree height in a business workshop) destroy checkpoints.

Worked example with numbers or traced steps

Conceptual: Customer places Order contains Product
Logical: Order(OrderID PK, CustomerID FK, ProductID FK)
Physical PG: CREATE INDEX ON orders(customer_id);  -- NOT auto from FK
Physical MySQL InnoDB: FK auto-creates index on referencing col
Prod bug: PG ON DELETE CASCADE on unindexed FK → seq scan delete of 50M orders

When NOT to use / named alternative

Do not skip logical and jump conceptual→CREATE TABLE for multi-team systems. Do not over-normalize the physical model against a known hot read path without measuring. Do not assume FK ⇒ index across engines.

Failure mode & ops fingerprint

Fingerprint: CASCADE deletes take minutes on Postgres; EXPLAIN shows Seq Scan on orders.customer_id; MySQL developer moves to PG and forgets to add FK indexes. Also: conceptual ER treated as deployable schema with multivalued ovals still present.

Hostile-panel drills (defend the decision)

Q1. Does PostgreSQL auto-index foreign keys?
Model answer: No. PK/UNIQUE get indexes; plain FK columns do not. You must CREATE INDEX yourself for join/CASCADE performance.

Q2. What belongs in logical but not conceptual?
Model answer: Attributes, domains/types, primary/foreign keys, normalization decisions — still engine-independent.

Q3. When is denormalization a physical decision?
Model answer: When a measured access pattern needs fewer joins and you accept write amplification / anomaly risk; document it at physical level, not as 'skip modeling.'