Logical data model

On this page 7

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.

ComponentWhat it specifiesExample
EntityA distinct business object with its own identityCustomer, Order, Product
AttributeA named property with a logical domainOrder date, unit price
Primary keyThe attribute set that uniquely identifies an instanceOrder number
Foreign keyA reference that implements a relationshipCustomer number in Order
RelationshipA connection with cardinality and optionalityOne customer places many orders
ConstraintA business rule the data must satisfyQuantity must be greater than zero
Core components of a logical data model

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.

AspectConceptual modelLogical modelPhysical model
Main audienceBusiness stakeholdersAnalysts and architectsDatabase engineers
EntitiesMajor concepts onlyAll entities, resolvedTables and views
AttributesFew or noneAll, with logical domainsColumns with engine types
KeysUsually absentPrimary and foreign keysKeys, indexes, partitions
NormalizationNot appliedTypically to third normal formSelective denormalization
Product dependenceNoneNoneBound to one engine
Comparison of the three data modeling levels

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 formRuleAnomaly it prevents
First normal formAtomic values, no repeating groupsLists hidden inside one attribute
Second normal formNo partial dependency on a composite keyProduct name repeated per order line
Third normal formNo transitive dependency between non-key attributesCity stored with every customer postal code
Boyce-Codd normal formEvery determinant is a candidate keyRare overlaps of candidate keys
Normal forms checked during logical design

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.

  1. Confirm scope and entity definitions from the conceptual model and glossary.
  2. Add attributes with logical domains and mandatory or optional status.
  3. Define primary, alternate and foreign keys.
  4. Resolve many-to-many relationships into associative entities.
  5. Normalize and document business constraints.
  6. 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.

SituationRecommended approachReason
New transactional systemFull logical model before buildFixes rules before code depends on them
Migration between enginesReverse engineer, then abstractSeparates business structure from old vendor quirks
Data warehouse designLogical model of sources and martsClarifies grain of fact and dimension tables
Integration of several applicationsShared canonical logical modelGives one meaning to common entities
Small prototypeLightweight model or noneOverhead may exceed the benefit
When a logical model is most valuable

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.