A logical data model is a detailed, technology-independent description of the entities, attributes, keys and relationships that a business domain needs, expressed precisely enough to be validated and normalized but without committing to a particular database product. In data modeling for databases, it sits between the conceptual data model, which names the main business concepts, and the physical data model, which specifies tables, data types, indexes and storage for one engine.
The logical model answers the question what data exists and how it relates, not where or how it is stored. It is the layer where analysts and architects agree on business rules, such as whether an order may exist without a customer, before engineering choices introduce vendor-specific detail.
What a logical data model contains
A logical data model contains every element needed to describe the structure of the data completely and unambiguously. Each entity represents a class of things the business tracks, each attribute describes a property of that entity, and each relationship states how instances of two entities are connected, including its cardinality and optionality.
The following table lists the core components and what each one specifies at the logical level.
| Component | What it specifies | Example |
|---|---|---|
| Entity | A distinct business object with its own identity | Customer, Order, Product |
| Attribute | A named property with a logical domain | Order date, unit price |
| Primary key | The attribute set that uniquely identifies an instance | Order number |
| Foreign key | A reference that implements a relationship | Customer number in Order |
| Relationship | A connection with cardinality and optionality | One customer places many orders |
| Constraint | A business rule the data must satisfy | Quantity must be greater than zero |
Attributes are described by logical domains such as text, integer, decimal amount or date rather than engine-specific types such as VARCHAR(255) or NUMBER(10,2). This keeps the model portable and lets reviewers focus on meaning. Many-to-many relationships are usually resolved into associative entities at this stage, for example an Order line entity between Order and Product, because the association itself often carries attributes such as quantity.
How the logical model differs from conceptual and physical models
The three modeling levels differ in audience, detail and technology dependence. The conceptual model communicates scope to business stakeholders, the logical model fixes structure and rules, and the physical model tells a database engine exactly what to build.
| Aspect | Conceptual model | Logical model | Physical model |
|---|---|---|---|
| Main audience | Business stakeholders | Analysts and architects | Database engineers |
| Entities | Major concepts only | All entities, resolved | Tables and views |
| Attributes | Few or none | All, with logical domains | Columns with engine types |
| Keys | Usually absent | Primary and foreign keys | Keys, indexes, partitions |
| Normalization | Not applied | Typically to third normal form | Selective denormalization |
| Product dependence | None | None | Bound to one engine |
A frequent misconception treats the logical model as an ER diagram drawn with more boxes. The diagram is only one notation; the model itself also includes attribute definitions, domains, key rules and documented constraints, which are often held in a modeling tool repository or data dictionary rather than on the canvas.
Normalization and keys in the logical model
The logical model is the natural place to apply normalization, because redundancy and update anomalies are properties of the data structure, not of storage. Most practitioners normalize to third normal form or Boyce-Codd normal form, so that every non-key attribute depends on the key, the whole key and nothing but the key.
The table below summarizes the normal forms most commonly checked during logical design.
| Normal form | Rule | Anomaly it prevents |
|---|---|---|
| First normal form | Atomic values, no repeating groups | Lists hidden inside one attribute |
| Second normal form | No partial dependency on a composite key | Product name repeated per order line |
| Third normal form | No transitive dependency between non-key attributes | City stored with every customer postal code |
| Boyce-Codd normal form | Every determinant is a candidate key | Rare overlaps of candidate keys |
Key selection is also a logical decision. Architects identify candidate keys from business attributes, choose a primary key, and record alternate keys as uniqueness rules. Whether a surrogate key replaces a natural key is sometimes deferred to physical design, but documenting the natural business identifier in the logical model preserves the rule that duplicates must not exist.
Building and using a logical data model
A logical data model is built iteratively from requirements, business glossaries and existing data sources. Analysts typically start from the conceptual model, add attributes, resolve relationships, normalize, and then validate the result in workshops with domain experts.
- Confirm scope and entity definitions from the conceptual model and glossary.
- Add attributes with logical domains and mandatory or optional status.
- Define primary, alternate and foreign keys.
- Resolve many-to-many relationships into associative entities.
- Normalize and document business constraints.
- Review with stakeholders and version the model before physical design.
The decision table below shows situations where a logical model brings the greatest value and the reason in each case.
| Situation | Recommended approach | Reason |
|---|---|---|
| New transactional system | Full logical model before build | Fixes rules before code depends on them |
| Migration between engines | Reverse engineer, then abstract | Separates business structure from old vendor quirks |
| Data warehouse design | Logical model of sources and marts | Clarifies grain of fact and dimension tables |
| Integration of several applications | Shared canonical logical model | Gives one meaning to common entities |
| Small prototype | Lightweight model or none | Overhead may exceed the benefit |
For analytical systems, the logical layer often takes the shape of a dimensional model with fact and dimension entities arranged as a star schema or snowflake schema. The modeling principle stays the same: define grain, keys and relationships before choosing columnar storage or partitioning.
Limitations and common mistakes
A logical model is only useful while it stays synchronized with reality. Teams that treat it as a one-time deliverable soon find that the physical schema has drifted, and the model then misleads more than it informs.
- Leaking physical detail, such as indexes or engine data types, which ties the model to one product.
- Skipping definitions, leaving entity names that different teams interpret differently.
- Over-normalizing reference data into many tiny entities that add joins without protecting integrity.
- Ignoring optionality, so that the physical design cannot tell mandatory from optional relationships.
- No version control, which makes it impossible to trace when a business rule changed.
Another limitation is that a relational logical model fits document, graph and key-value stores only partly. In those systems, practitioners still model entities and relationships logically, but the physical design is driven by access patterns, so the gap between logical and physical structure is wider and must be documented explicitly.
Explained without jargon
Think of a logical data model as the architect’s floor plan for information. It shows which rooms exist, what each room holds and which doors connect them, but it does not say whether the house will be built from brick or timber.
In practice, it lists the things a business cares about, such as customers, orders and products, the details recorded about each, and the rules that link them. Because it ignores the brand of database, people can agree on it first and choose the technology later. The key idea is agreement on meaning before construction.
A typical use case
A regional hospital group plans to replace three separate appointment systems with one platform. Before selecting a vendor, its data architects produce a logical data model with about 40 entities, including Patient, Clinician, Appointment, Department and Referral, with every attribute assigned a logical domain and every relationship given cardinality.
During review, clinicians point out that one appointment can involve several clinicians, so the team replaces a simple foreign key with an associative entity called Appointment participant. They also agree that a referral is optional but, when present, must reference exactly one patient.
When the group later chooses a product that runs on a different relational engine than expected, the physical design takes about three weeks instead of months, because the structure and business rules were already settled. The migration scripts map each legacy field to the logical model, which becomes the single reference for data quality checks.