What are Conceptual, Logical and Physical Data Models?

Like all good data architects, I want to define the terms I use on this blog, speaking engagements, and on my projects.  And like most data professionals, I’ve discovered that the industry has multiple, often conflicting, definitions of data modeling terms, which I find sadly ironic.  My friend Graeme Simsion has even done research in this naming conflict.

My uses of conceptual, logical, and physical come from the Information Engineering (IE) methods of data modeling.  Other uses and definitions arise from the database schema and academic world. Neither is wrong; it’s just that they are different.   The industry as a whole tends to use the IE definitions, so I tend to stick to them because they are used by the vast majority of practitioner data modelers and other team members.

Conceptual Data Model

A conceptual data model is a summary-level data model that is most often used on strategic data projects.  It typically describes an entire enterprise.  Due to its highly abstract nature, it may be referred to as a conceptual model.

Common characteristics of a conceptual data model:

• Enterprise-wide coverage of the business concepts.  Think Customer, Product, Store, Location, Asset.
• Designed and developed primarily for a business audience
• Contains around 20-50 entities (or concepts) with no or extremely limited number of attributes described. Sometimes architects try to limit it to printing on one page.
• Contains relationships between entities, but may or may not include cardinality and nullability.
• Entities will have definitions.
• Designed and developed to be independent of DBMS, data storage locations or technologies.  In fact, it would address digital and non-digital concepts. This means it would model paper records and artifacts as well as database artifacts.

Logical Data Model

A logical data model is a fully-attributed data model that is independent of DBMS, technology, data storage or organizational constraints.  It typically describes data requirements from the business point of view.  While common data modeling techniques use a relational model notation, there is no requirement that resulting data implementations must be created using relational technologies.

Common characteristics of a logical data model:

• Typically describes data requirements for a single project or major subject area.
• May be integrated with other logical data models via a repository of shared entities
• Typically contains 100-1000 entities, although these numbers are highly variable depending on the scope of the data model.
• Contains relationships between entities that address cardinality and nullability (optionality) of the relationships.
• Designed and developed to be independent of DBMS, data storage locations or technologies.  In fact, it may address digital and non-digital concepts.
• Data attributes will typically have datatypes with precisions and lengths assigned.
• Data attributes will have nullability (optionality) assigned.
• Entities and attributes will have definitions.
• All kinds of other meta data may be included (retention rules, privacy indicators,  volumetrics, data lineage, etc.) In fact, the diagram of a logical data model may show only a tiny percentage of the meta data contained within the model.

A logical data model will normally be derived from and or linked back to objects in a conceptual data model.

Physical Data Model

A physical data model is a fully-attributed data model that is dependent upon a specific version of a data persistence technology.  The target implementation technology may be a relational DBMS, an XML document, a NoSQL data storage component, a spreadsheet or any other data implementation option.

Common characteristics of a physical data model:

• Typically describes data requirements for a single project or application. Sometimes even a portion of an application.
• May be integrated with other physical data models via a repository of shared entities
• Typically contains 10-1000 tables, although these numbers are highly variable depending on the scope of the data model.
• Contains relationships between tables that address cardinality and nullability (optionality) of the relationships.
• Designed and developed to be dependent on a specific version of a DBMS, data storage location or technology. 
• Columns will  have datatypes with precisions and lengths assigned.
• Columns will have nullability (optionality) assigned.
• Tables and columns will have definitions.
• Will also include other physical objects such as views, primary key constraints, foreign key constraints, indexes, security roles, store procedures, XML extensions, file stores, etc.
• The diagram of a physical data model may show only a tiny percentage of the meta data contained within the model.