Running Example

Throughout this paper we use a running example. It will help you understand the relation between the logical data model, the physical representation of data in tables and the views an application has on the data. It will also help you understand the framework by providing use cases.

Consider the excerpt of an order management system, shown in the lower part of Figure 2. It models the entities you need to process the invoice, shown in the upper left corner. The excerpt complies to the Third Normal Form (3NF); a level of factorization, data analysts often use [Dat94]. Suppose, you have used this logical data model to define your physical database tables. The system will work correctly, but you will encounter bad performance.

Profiling the system you detect many superfluous database operations. You also find slow database operations caused by large joins or by moving unnecessary large amounts of data. To increase performance you denormalize the physical data model. A statistical analysis of the database’s contents yields that ninety percent of the Orders have no more than five OrderItems. Therefore you decide to store the first five OrderItems in the Order table. To cover the remaining ten percent you create an OrderItemOverflow table as depicted in the upper right of Figure 2. Furthermore you integrate the Article attributes ArtPrice and ArtName into the Order table. The resulting database design allows to read ninety percent of the invoices with two database accesses: one to the Order table and on to the Customer table. Besides you have eliminated all joins.

Now assume, you have embedded SQL-statements within the application kernel code. To adapt the code to the new table schemes you have to rewrite large portions of it. Furthermore, handling overflow tables lets the SQL code explode. Worst of all, you have to repeat this procedure for every improvement of the database structure.

This pattern language will help you avoid such problems.

Figure 2 : Part of an Order Processing System

Notational Conventions

We use OMT  for object diagrams. Another Pattern references a related pattern. If a pattern reference is followed by a citation, such as [GOF95], you can find it in the cited paper.