Pattern: Overflow Table

 

Abstract

Overflow tables are a technique to preserve correctness for entities that contain a variable length set of dependent subentities and only a fixed number of spaces for such subentities as they have to be mapped to fixed length database records.

Context

You have followed the Denormalization pattern’s advice and have denormalized a relation.

Problem

What do you do with those orders that have more positions than the number that you did integrate into the Order table?

Forces

      Correctness forces you to find a solution that allows you to access positions that do not fit on the Order page. A possible solution should not mess up the client code and should also be designed for good performance. The time/space tradeoff cited above is present here as well.

Solution

Use a second table, a so called overflow table, that contains another physical database page full of OrderItems. Identify the OrderItemOverflow table with a dumb number and link it to following or previous OrderItemOverflow records by fields that contain the predecessor and the following record that might contain more OrderItems.

Consequences

      Correctness: You have solved you correctness problem at the cost of more complex code.

      Time: You have achieved maximum possible speed for those orders that fit into Order/OrderItem table.

      Space: The space consumption is less than optimal for such orders that use only a small percentage of an Overflow Table’s record.

Related Patterns

You should use the Physical Views pattern to encapsulate the messy conditional code that arises from handling the double linked list of OrderItemOverflow records. Denert describes Denormalization as a case study on physical database design in [Den91, pp. 285-288].