Relational and Dimensional Physical Data Models

Recall from this section introduction that 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. There are both relational and dimensional physical data models.

You have seen examples of both relational and dimensional conceptual and logical data models (recall Figures 8.3 and 8.5 from the chapter on conceptual data modeling and Figures 9.1 and 9.2 from the chapter on logical data modeling). Figures 10.1 and 10.2 show these two examples at a physical level. Let’s go through how each of these are built, starting with relational.

Relational PDM Example

The relational PDM includes entities with their definitions, relationships, and columns along with their definitions. Note that often the term table is used instead of the term entity, and column instead of attribute, on the physical data model. Figure 10.1 contains part of a financial relational PDM. Compromises were made to the logical counterpart of this model to most likely improve data retrieval performance or to make it easier for developers to extract, transform, and load (ETL) data.

Figure 10.1 Financial relational PDM subset

Business Rules (listed in the order we would typically walk someone through the model):

·         Each Customer Account may contain one or many Account Balances.

·         Each Account Balance must belong to one Customer Account.

Dimensional PDM Example

To understand and document our reporting requirements, we can also build a dimensional PDM, such as the example in Figure 10.2. This model, called a star schema which we’ll cover shortly, is similar to its logical counterpart, except that each dimension from the dimensional LDM has been flattened on the model from Figure 9.2 into one table.

Figure 10.2 Financial dimensional PDM