Stars: A Pattern Language for Query-Optimized Schemas

By: S. Peterson
Published in: PLoPD1
Pages: 163-177
Category: Database

Summary: An easy-to-query schema for decision-support systems.

Url: http://c2.com/ppr/stars.html

Pattern: Query Optimized Database

Pages: 164-166

Online transaction processing (OLTP) systems are usually optimized for recording business transactions. Develop a new database optimized for easy querying. The new database will probably be implemented on a different machine from the one that hosts the OLTP database.

• Once you have a list of reconstituted entities to model your business area, use Key Business Activities and Influences to focus on these events.

Pattern: Whole Business Entities

Pages: 166-168

To understand a schema for your business, use easily recognized names for the objects in Query Optimized Database. Find entities in your domain that are directly relevant to your problem by examining the reports you use to monitor your business. Make a list of these entities.

Pattern: Key Business Activities and Influences

Pages: 168-170

You're using Whole Business Entities. Characterize entities by how they are related to each other. By defining these relationships, you're defining the role of each entity.

Pattern: Transaction History

Pages: 170-173

You're using Key Business Activities and Influences. For each activity, create a fact table with all the information and the transaction history for the activity.

Pattern: People, Places, and Things

Pages: 173-175

You're using Key Business Activities and Influences. Create a table for each person, place, or thing that has a part in the transactions you're examining. These tables should model the entity.

Pattern: Time

Pages: 175-176

To measure changes in business activity, data from a range of specified times must be analyzed. Create a dimension table containing units of time that correspond to some significant event in your business.

Pattern: Dimension Roll-Up

Pages: 176-177

You've applied Transaction History. You want to roll up the level of activity in your business so you can see the big picture for some group of records in a table. Create a table to represent the larger organization to encompass your business dimension. This table should include all information related to the thing to be included on a report. These things should already be listed in Whole Business Entities