Pattern: Denormalization



Denormalization is a technique to move from higher to lower normal forms of database modeling in order to speed up database access. You may apply Denormalization in the process of deriving a physical data model from a logical form.


The analysis you have started, concentrating on important views only, shows that an Order View is one of the most heavily used views in the database. Reading an Order and the OrderItems costs a join statement and several physical page accesses. Writing it costs several update statements. Further statistical analysis, again concentrating on the important parts of orders, yields that 90 percent of Orders have no more than 5 positions.


How can you manage to read and write most physical views with a single page database access when you have a parent/child (Order/OrderItem) relation?



Fill up the Order entity database page with OrderItems until you reach the next physical page limit. Physical pages are usually chunks of 2 or 4K depending on the database implementation.

The number of OrderItems should be near or greater than the number of OrderItems that reflects the order size in 80-95% of cases.


Related Patterns

Use an OverflowTable to deal with the other 5-20% of orders that have a mandatory number of OrderItems. Use PhysicalViews to encapsulate the code that is needed to handle the cases of page overflow.

Other Online Articles