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.
Subscribe via E-mail
- SQLRockstar on Yet Another Odd Job Criterion
- Karen Lopez on Yet Another Odd Job Criterion
- Alex on And They Wonder Why There are no Women Working There…
- Sue on And They Wonder Why There are no Women Working There…
- Big Challenges in Data Modeling: Ethics & Data Modeling April 24th | Securing SQL Server on Big Challenges in Data Modeling: Ethics & Data Modeling–24 April
- August 2014
- July 2014
- June 2014
- May 2014
- April 2014
- March 2014
- February 2014
- January 2014
- December 2013
- November 2013
- October 2013
- September 2013
- August 2013
- July 2013
- June 2013
- May 2013
- April 2013
- March 2013
- February 2013
- January 2013
- December 2012
- November 2012
- October 2012
- September 2012
- August 2012
- July 2012
- June 2012
- May 2012
- April 2012
- March 2012
- February 2012
- January 2012
- December 2011
- November 2011
- October 2011
- September 2011
- August 2011
- July 2011
- June 2011
- May 2011
- April 2011
- March 2011
- February 2011
- January 2011
- December 2010
- November 2010
- September 2010
- August 2010
- July 2010