2.5From logical design to physical design

The normalization covered in earlier sections is a matter of "logical design," pursuing the correctness of eliminating update anomalies. In actual operation, however, an over-normalized schema runs into the real-world wall of degraded read performance from excessive JOINs. At the final stage, a database designer must judge whether to translate the logical design directly into a physical storage scheme, or to deliberately denormalize part of it to prioritize performance. This section covers the distinction between logical and physical design, and denormalization as the final design judgment.

2.5.1The distinction between logical and physical design

• Logical design is the process of expressing business requirements as an E-R diagram and a normalized relational schema. It is the stage that defines what to manage, independent of any particular DBMS product or concrete storage scheme.
• Physical design is the process of translating the logical design into actual tables, indexes, partitions, and storage areas (tablespaces, and so on). It is the stage that defines how to store data and access it quickly, taking performance requirements, data-volume estimates, and access-pattern analysis as inputs.

2.5.2The denormalization trade-off

• Denormalization is the deliberate reintroduction of redundant data into some tables, for performance reasons, of information that normalization had separated out (for example, redundantly copying a product name into the order-line table, even though it should properly be obtained via a JOIN to the product table each time). It is a trade-off that reduces the number of JOINs and improves read performance, at the cost of deliberately accepting the risk of update anomalies.
• Denormalization is not "breaking normalization out of ignorance"—the correct procedure is to first establish a normalized logical design, and then, as a later-stage optimization judgment, deliberately choose where to break it based on performance requirements (read frequency, data volume, tolerable latency). A redundant column must be designed together with an operational mechanism (such as a trigger) that keeps it in sync with the original data on update.

A database designer at an e-commerce company operates a normalized "order line" table (order-line ID, order ID, product ID, quantity) and a "product" table (product ID, product name, unit price). The homepage's "order history" screen is a high-frequency read operation, accessed tens of thousands of times a day, and every access joins the "order line" table with the "product" table to fetch the product name—causing a performance problem where the response is delayed by several seconds under peak load. The designer first considers denormalizing to avoid the JOIN, just for this screen: adding a redundant product-name column to the "order line" table, copying in the product name as of the moment the order was finalized. A key judgment arises here. Product names can change in the future, but if the business requires that "order history should display the product name as it was at the time of the order," this denormalization is actually the correct design (the copied product name carries a different meaning—a snapshot at that point in time—so it need not be updated even if the product name later changes). If instead the requirement were "the order history should always display the current product name," the denormalized column would additionally need a trigger or update process to keep it synchronized whenever the product name changes, and the complexity and bug risk of that synchronization process would have to be weighed. This designer judges that, since order history is by nature (including legally) a record of "what was true at the time of the order," denormalizing it as a snapshot that requires no synchronization is the most sensible design, and further adds an index, as physical design, on the frequently queried order-ID column—resolving the performance problem through both the normalization trade-off and physical design working together.

2.5.3Section summary

• Logical design defines what to manage (a normalized E-R diagram and relational schema); physical design defines how (tables, indexes, partitions)
• Denormalization is deliberate redundancy chosen with full understanding of normalization; it is applied selectively, as a step after normalization, based on performance requirements
• When redundant data carries its own distinct meaning, such as a snapshot, denormalization that requires no synchronization can be the correct design