CHAPTER 17

What are physical data models?

Let’s get Physical

Consider environment

Time to make it real

The highlighted row in Table 17.1 shows the focus of this chapter, which is the physical data model.

Table 17.1 The Physical Data Model is the focus of this chapter

A physical data model (PDM) takes the business solution defined on a logical data model to the next level of a technical solution. That is, once you solve the problem independent of software and hardware concerns, you can then make adjustments for software and hardware. This chapter will explain the most popular techniques for making adjustments to a business solution to create an efficient technical solution. I will explain the PDM and then discuss the techniques of denormalization, views, indexing, and partitioning. Although these techniques apply to both relational and dimensional models, their names may differ depending on which type of model they are applied to. I will explain these terminology differences in this chapter, as well. I will conclude with a discussion on how to adjust your physical data model to accommodate data value changes, and introduce the concept of a slowly changing dimension.

As in the previous chapter, PowerDesigner notes intrude as little as possible in this discussion. At the end of the chapter we describe the specific techniques introduced in the PowerDesigner PDM.

Physical Data Model Explained

The physical data model (PDM) is the logical data model modified for specific software or hardware. On the SAM, we might learn what the terms, business rules, and scope would be for a new order entry system. After understanding the need for an order entry system, we create a LDM representing the business solution. It contains all of the data elements and business rules needed to deliver the system. For example, the subject area model will show that a Customer places many Orders. The LDM will capture all of the details behind Customer and Order, such as the customer’s name, their address, and the order number. After understanding the business solution, we move on to the technical solution and build the PDM.

While building the PDM, we address the issues that have to do with specific hardware or software such as:

·         How can we retrieve this information in fewer than 5 seconds?

·         How can we make this information secure?

·         There is a lot of information here. What is the best way to manage storage space?

Note that in the early days of data modeling, when storage space was expensive and computers were slow, there were major modifications made to the PDM to make it work efficiently. In some cases, the PDM looked like it was for an entirely different application than the LDM. As technology improved, the PDM started looking more like the LDM. Faster and cheaper processors, cheaper and more generous disc space and system memory, and also specialized hardware, have all played their part. We should continue to see this trend as better hardware and software lead to fewer compromises on the PDM, resulting in a PDM that looks more like its LDM.

Denormalization Explained

Denormalization is the process of selectively violating normalization rules and reintroducing redundancy into the model (and therefore, the database). This extra redundancy can reduce data retrieval time, which is the primary reason for denormalizing. We can also denormalize to create a more user-friendly model. For example, we might decide to denormalize company information into an entity containing employee information, because usually when employee information is retrieved, company information is also retrieved.

The faster retrieval and user-friendliness, however, come with the price of extra redundancy on the model, which could in turn:

·         Cause update, delete, and insert performance to suffer

·         Introduce data quality problems

·         Take up more space

·         Stunt growth of the application.