SECTION III
Conceptual, Logical, and Physical Data Models
Section III explores the three different levels of models: conceptual, logical, and physical. A conceptual data model (CDM) represents the business need within a defined scope, a logical data model (LDM) the detailed business solution, and the physical data model (PDM) the detailed technical solution. Chapter 8 focuses on the CDM, Chapter 9 the LDM, and Chapter 10 the PDM.
In addition to these three levels of detail, there are also two different modeling mindsets: relational and dimensional. Relational data modeling is the process of capturing how the business works by precisely representing business rules, while dimensional data modeling is the process of capturing how the business is monitored by precisely representing navigation.
The major difference between relational and dimensional data models is in the meaning of the relationship lines – on a relational data model a relationship communicates a business rule and on dimensional data model the relationship communicates a navigation path. On a relational data model for example, we can represent the business rule “A Customer must have at least one Account”. On a dimensional data model we can display the measure Gross Sales Amount along with all of the navigation paths that a user needs to see Gross Sales Amount at, such as by day, month, year, region, account, and customer. The dimensional data model is all about viewing measures at different levels of granularity.
The following table summarizes these three levels of detail and two modeling mindsets, leading to six different types of models:
|
|
|
Mindset | |
|
|
|
Relational |
Dimensional |
|
Types of models |
CDM |
Key concepts and their business rules, such as a “Each Customer may place one or many Orders.” |
Key concepts focused around one or more measures, such as “I want to see Gross Sales Amount by Customer.” |
|
LDM |
All attributes required for a given application or business process, neatly organized into entities according to strict business rules and independent of technology, such as “Each Customer ID value must return at most one Customer Last Name.” |
All attributes required for a given reporting application, focused on measures and independent of technology, such as “I want to see Gross Sales Amount by Customer and view the customer’s first and last name.” | |
|
PDM |
The LDM modified for a specific technology, such as database or access software. For example, “To improve retrieval speed, we need a non-unique index on Customer Last Name.” |
The LDM modified for a specific technology, such as database or access software. For example, “Because there is a need to view Gross Sales Amount at a Day level, and then by Month and Year, we should consider combining all calendar attributes into a single table to improve retrieval performance.” | |
By the end of this section you will be able to create relational and dimensional conceptual, logical, and physical data models in ER/Studio.