You are previewing The Data Model Resource Book, Volume 3: Universal Patterns for Data Modeling.
O'Reilly logo
The Data Model Resource Book, Volume 3: Universal Patterns for Data Modeling

Book Description

This third volume of the best-selling "Data Model Resource Book" series revolutionizes the data modeling discipline by answering the question "How can you save significant time while improving the quality of any type of data modeling effort?" In contrast to the first two volumes, this new volume focuses on the fundamental, underlying patterns that affect over 50 percent of most data modeling efforts. These patterns can be used to considerably reduce modeling time and cost, to jump-start data modeling efforts, as standards and guidelines to increase data model consistency and quality, and as an objective source against which an enterprise can evaluate data models.

Praise for The Data Model Resource Book, Volume 3

"Len and Paul look beneath the superficial issues of data modeling and have produced a work that is a must for every serious designer and manager of an IT project."

Bill Inmon, World-renowned expert, speaker, and author on data warehousing and widely recognized as the "father of data warehousing"

"The Data Model Resource Book, Volume 3: Universal Patterns for Data Modeling is a great source for reusable patterns you can use to save a tremendous amount of time, effort, and cost on any data modeling effort. Len Silverston and Paul Agnewhave provided an indispensable reference of very high-quality patterns for the most foundational types of datamodel structures. This book represents a revolutionary leap in moving the data modeling profession forward."

Ron Powell, Cofounder and Editorial Director of the Business Intelligence Network

"After we model a Customer, Product, or Order, there is still more about each of these that remains to be captured, such as roles they play, classifications in which they belong, or states in which they change. The Data Model Resource Book, Volume 3: Universal Patterns for Data Modeling clearly illustrates these common structures. Len Silverston and Paul Agnew have created a valuable addition to our field, allowing us to improve the consistency and quality of our models by leveraging the many common structures within this text."

Steve Hoberman, Best-Selling Author of Data Modeling Made Simple

"The large national health insurance company I work at has actively used these data patterns and the (Universal Data Models) UDM, ahead of this book, through Len Silverston’s UDM Jump Start engagement. The patterns have found their way into the core of our Enterprise Information Model, our data warehouse designs, and progressively into key business function databases. We are getting to reuse the patterns across projects and are reaping benefits in understanding, flexibility, and time-to-market. Thanks so much."

David Chasteen, Enterprise Information Architect

"Reusing proven data modeling design patterns means exactly that. Data models become stable, but remain very flexible to accommodate changes. We have had the fortune of having Len and Paul share the patterns that are described in this book via our engagements with Universal Data Models, LLC. These data modeling design patterns have helped us to focus on the essential business issues because we have leveraged these reusable building blocks for many of the standard design problems. These design patterns have also helped us to evaluate the quality of data models for their intended purpose. Many times there are a lot of enhancements required. Too often the very specialized business-oriented data model is also implemented physically. This may have significant drawbacks to flexibility. I’m looking forward to increasing the data modeling design pattern competence within Nokia with the help of this book."

Teemu Mattelmaki, Chief Information Architect, Nokia

"Once again, Len Silverston, this time together with Paul Agnew, has made a valuable contribution to the body of knowledge about datamodels, and the act of building sound data models. As a professional data modeler, and teacher of data modeling for almost three decades, I have always been aware that I had developed some familiar mental "patterns" which I acquired very early in my data modeling experience. When teaching data modeling, we use relatively simple workshops, but they are carefully designed so the students will see and acquire a lot of these basic "patterns" — templates that they will recognize and can use to interpret different subject matter into data model form quickly and easily. I’ve always used these patterns in the course of facilitating data modeling sessions; I was able to recognize "Ah, this is just like . . .," and quickly apply a pattern that I’d seen before. But, in all this time, I’ve never sat down and clearly categorized and documented what each of these “patterns’’ actually was in such a way that they could be easily and clearly communicated to others; Len and Paul have done exactly that. As in the other Data Model Resource Books, the thinking and writing is extraordinarily clear and understandable. I personally would have been very proud to have authored this book, and I sincerely applaud Len and Paul for another great contribution to the art and science of data modeling. It will be of great value to any data modeler."

William G. Smith, President, William G. Smith & Associates, www.williamgsmith.com

"Len Silverston and Paul Agnew’s book, Universal Patterns for Data Modeling, is essential reading for anyone undertaking commercial datamodeling. With this latest volume that compiles and insightfully describes fundamental, universal data patterns, The Data Model Resource Book series represents the most important contribution to the data modeling discipline in the last decade."

Dr. Graeme Simsion, Author of Data Modeling Essentials and Data Modeling Theory and Practice

"Volume 3 of this trilogy is a most welcome addition to Len Silverston’s two previous books in this area. Guidance has existed for some time for those who desire to use pattern-based analysis to jump-start their data modeling efforts. Guidance exists for those who want to use generalized and industry-specific data constructs to leverage their efforts. What has been missing is guidance to those of us needing guidance to complete the roughly one-third of data models that are not generalized or industry-specific. This is where the magic of individual organizational strategies must manifest itself, and Len and Paul have done so clearly and articulately in a manner that complements the first two volumes of The Data Model Resource Book. By adding this book to Volumes 1 and 2 you will be gaining access to some of the most integrated data modeling guidance available on the planet."

Dr. Peter Aiken, Author of XML in Data Management and data management industry leader VCU/Data Blueprint

Table of Contents

  1. Copyright
  2. Advance Praise for The Data Model Resource Book, Volume 3
  3. Credits
  4. Foreword
  5. Acknowledgments
  6. About the Authors
  7. 1. Introduction
    1. 1.1. Why Is There a Need for This Book?
    2. 1.2. Extending the Discipline of Data Modeling
    3. 1.3. What Is a Pattern and What Is a Universal Pattern?
    4. 1.4. What Is the Significance of Patterns?
    5. 1.5. Approach of This Book
    6. 1.6. The Different Pattern Levels
    7. 1.7. Who Is the Intended Audience for This Book?
    8. 1.8. What Is in This Book
    9. 1.9. Other Patterns for Data Modeling
    10. 1.10. Conventions and Standards Used in This Book
      1. 1.10.1. Entities
      2. 1.10.2. Subtypes and Supertypes
      3. 1.10.3. Attributes
      4. 1.10.4. Relationships
        1. 1.10.4.1. Relationship Optionality
        2. 1.10.4.2. Relationship Cardinality
        3. 1.10.4.3. Foreign Key Relationships
        4. 1.10.4.4. Associative Entities to Handle Many-to-Many Relationships
      5. 1.10.5. Exclusive Arcs
      6. 1.10.6. Example Data in Illustration Tables
      7. 1.10.7. Data Modeling Notation
    11. 1.11. Summary
    12. 1.12. References
  8. 2. Setting Up Roles: What Parties Do
    1. 2.1. What Is the Significance of This Type of Pattern?
    2. 2.2. What Is in This Chapter?
    3. 2.3. What Is a Declarative Role?
    4. 2.4. Level 1 Declarative Role Pattern
      1. 2.4.1. Why Do We Need This Pattern?
      2. 2.4.2. How Does This Pattern Work?
      3. 2.4.3. When Should This Pattern Be Used?
      4. 2.4.4. What Are the Weaknesses of the Pattern?
      5. 2.4.5. Synopsis
    5. 2.5. Level 2 Declarative Role Pattern
      1. 2.5.1. Why Do We Need This Pattern?
      2. 2.5.2. How Does This Pattern Work?
      3. 2.5.3. When Should This Pattern Be Used?
      4. 2.5.4. What Are the Weaknesses of the Pattern?
      5. 2.5.5. Synopsis
    6. 2.6. Level 3 Declarative Role Pattern
      1. 2.6.1. Why Do We Need This Pattern?
      2. 2.6.2. How Does This Pattern Work?
      3. 2.6.3. When Should This Pattern Be Used?
      4. 2.6.4. What Are the Weaknesses of the Pattern?
      5. 2.6.5. Synopsis
    7. 2.7. Summary of Patterns
    8. 2.8. References
  9. 3. Using Roles: How Parties Are Involved
    1. 3.1. What Is the Significance of This Type of Pattern?
    2. 3.2. What Is in This Chapter?
    3. 3.3. What Is a Contextual Role?
    4. 3.4. Level 1 Contextual Role Pattern, Attributes
      1. 3.4.1. Why Do We Need This Pattern?
      2. 3.4.2. How Does This Pattern Work?
      3. 3.4.3. When Should This Pattern Be Used?
      4. 3.4.4. What Are the Weaknesses of the Pattern?
      5. 3.4.5. Synopsis
    5. 3.5. Level 1 Contextual Role Pattern, Relationships
      1. 3.5.1. Why Do We Need This Pattern?
      2. 3.5.2. How Does This Pattern Work?
      3. 3.5.3. When Should This Pattern Be Used?
      4. 3.5.4. What Are the Weaknesses of the Pattern?
      5. 3.5.5. Synopsis
    6. 3.6. Level 2 Contextual Role Pattern
      1. 3.6.1. Why Do We Need This Pattern?
      2. 3.6.2. How Does This Pattern Work?
      3. 3.6.3. When Should This Pattern Be Used?
      4. 3.6.4. What Are the Weaknesses of the Pattern?
      5. 3.6.5. Synopsis
    7. 3.7. Level 2 Contextual Role Pattern, PARTY Only Alternative
      1. 3.7.1. Why Do We Need This Pattern?
      2. 3.7.2. How Does This Pattern Work?
      3. 3.7.3. When Should This Pattern Be Used?
      4. 3.7.4. What Are the Weaknesses of the Pattern?
      5. 3.7.5. Synopsis
    8. 3.8. Level 3 Contextual Role Pattern
      1. 3.8.1. Why Do We Need This Pattern?
      2. 3.8.2. How Does This Pattern Work?
      3. 3.8.3. When Should This Pattern Be Used?
      4. 3.8.4. What Are the Weaknesses of the Pattern?
      5. 3.8.5. Synopsis
    9. 3.9. Hybrid Contextual Role Pattern
      1. 3.9.1. Why Do We Need This Pattern?
      2. 3.9.2. How Does This Pattern Work?
      3. 3.9.3. When Should This Pattern Be Used?
      4. 3.9.4. What Are the Weaknesses of the Pattern?
      5. 3.9.5. Synopsis
    10. 3.10. Summary of Patterns
    11. 3.11. References
  10. 4. Hierarchies, Aggregations, and Peer-to-Peer Relationships: The Organization of Similar Data
    1. 4.1. What Is the Significance of This Type of Pattern?
    2. 4.2. What Is in This Chapter?
    3. 4.3. What Is a Recursive Relationship and How Is Data Organized by Recursive Relationships?
    4. 4.4. Level 1 Recursive Pattern
      1. 4.4.1. Why Do We Need This Pattern?
      2. 4.4.2. How Does This Pattern Work?
      3. 4.4.3. When Should This Pattern Be Used?
      4. 4.4.4. What Are the Weaknesses of the Pattern?
      5. 4.4.5. Synopsis
    5. 4.5. Level 2 Recursive Pattern
      1. 4.5.1. Why Do We Need This Pattern?
      2. 4.5.2. How Does This Pattern Work?
      3. 4.5.3. When Should This Pattern Be Used?
      4. 4.5.4. What Are the Weaknesses of the Pattern?
      5. 4.5.5. Synopsis
    6. 4.6. Level 2 Expanded Recursive Pattern
      1. 4.6.1. Why Do We Need This Pattern?
      2. 4.6.2. How Does This Pattern Work?
      3. 4.6.3. When Should This Pattern Be Used?
      4. 4.6.4. What Are the Weaknesses of the Pattern?
      5. 4.6.5. Synopsis
    7. 4.7. Level 3 Recursive Pattern
      1. 4.7.1. Why Do We Need This Pattern?
      2. 4.7.2. How Does This Pattern Work?
      3. 4.7.3. When Should This Pattern Be Used?
      4. 4.7.4. What Are the Weaknesses of the Pattern?
      5. 4.7.5. Synopsis
    8. 4.8. Level 3 Recursive Pattern with Rules
      1. 4.8.1. Why Do We Need This Pattern?
      2. 4.8.2. How Does This Pattern Work?
      3. 4.8.3. When Should This Pattern Be Used?
      4. 4.8.4. What Are the Weaknesses of the Pattern?
      5. 4.8.5. Synopsis
    9. 4.9. Summary of Patterns
    10. 4.10. References
  11. 5. Types and Categories: the Classification of Data
    1. 5.1. What Is the Significance of This Type of Pattern?
    2. 5.2. What Is in This Chapter?
    3. 5.3. What Are Types, Categorizations, and Taxonomies?
    4. 5.4. Level 1 Classification Pattern
      1. 5.4.1. Why Do We Need This Pattern?
      2. 5.4.2. How Does This Pattern Work?
      3. 5.4.3. When Should This Pattern Be Used?
      4. 5.4.4. What Are the Weaknesses of the Pattern?
      5. 5.4.5. Synopsis
    5. 5.5. Level 2 Classification Pattern
      1. 5.5.1. Why Do We Need This Pattern?
      2. 5.5.2. How Does This Pattern Work?
      3. 5.5.3. When Should This Pattern Be Used?
      4. 5.5.4. What Are the Weaknesses of the Pattern?
      5. 5.5.5. Synopsis
    6. 5.6. Level 3 Classification Pattern
      1. 5.6.1. Why Do We Need This Pattern?
      2. 5.6.2. How Does This Pattern Work?
      3. 5.6.3. When Should This Pattern Be Used?
      4. 5.6.4. What Are the Weaknesses of the Pattern?
      5. 5.6.5. Synopsis
    7. 5.7. Level 3 Classification Pattern with Rollups and Schemes
      1. 5.7.1. Why Do We Need This Pattern?
      2. 5.7.2. How Does This Pattern Work?
      3. 5.7.3. When Should This Pattern Be Used?
      4. 5.7.4. What Are the Weaknesses of the Pattern?
      5. 5.7.5. Synopsis
    8. 5.8. Summary of Patterns
    9. 5.9. References
  12. 6. Status: The States of Data
    1. 6.1. What Is the Significance of This Type of Pattern?
    2. 6.2. What Is in This Chapter?
    3. 6.3. What Is a Status?
    4. 6.4. Level 1 Status Pattern
      1. 6.4.1. Why Do We Need This Pattern?
      2. 6.4.2. How Does This Pattern Work?
      3. 6.4.3. When Should This Pattern Be Used?
      4. 6.4.4. What Are the Weaknesses of the Pattern?
      5. 6.4.5. Synopsis
    5. 6.5. Level 2 Status Pattern, Current Status
      1. 6.5.1. Why Do We Need This Pattern?
      2. 6.5.2. How Does This Pattern Work?
      3. 6.5.3. When Should This Pattern Be Used?
      4. 6.5.4. What Are the Weaknesses of the Pattern?
      5. 6.5.5. Synopsis
    6. 6.6. Level 3 Status Pattern
      1. 6.6.1. Why Do We Need This Pattern?
      2. 6.6.2. How Does This Pattern Work?
      3. 6.6.3. When Should This Pattern Be Used?
      4. 6.6.4. What Are the Weaknesses of the Pattern?
      5. 6.6.5. Synopsis
    7. 6.7. Level 4 Status Pattern
      1. 6.7.1. Why Do We Need This Pattern?
      2. 6.7.2. How Does This Pattern Work?
      3. 6.7.3. When Should This Pattern Be Used?
      4. 6.7.4. What Are the Weaknesses of the Pattern?
      5. 6.7.5. Synopsis
    8. 6.8. Status Category Pattern
      1. 6.8.1. Why Do We Need This Pattern?
      2. 6.8.2. How Does This Pattern Work?
      3. 6.8.3. When Should This Pattern Be Used?
      4. 6.8.4. What Are the Weaknesses of the Pattern?
      5. 6.8.5. Synopsis
    9. 6.9. Status Type with Multi Rollup and Rules Pattern
      1. 6.9.1. Why Do We Need This Pattern?
      2. 6.9.2. How Does This Pattern Work?
      3. 6.9.3. When Should This Pattern Be Used?
      4. 6.9.4. What Are the Weaknesses of the Pattern?
      5. 6.9.5. Synopsis
    10. 6.10. Summary of Patterns
    11. 6.11. References
  13. 7. Contact Mechanisms: How to Get in Touch
    1. 7.1. What Is the Significance of This Type of Pattern?
    2. 7.2. What Is in This Chapter?
    3. 7.3. What Is a Contact Mechanism?
    4. 7.4. Level 1 Contact Mechanism Pattern
      1. 7.4.1. Why Do We Need This Pattern?
      2. 7.4.2. How Does This Pattern Work?
      3. 7.4.3. When Should This Pattern Be Used?
      4. 7.4.4. What Are the Weaknesses of the Pattern?
      5. 7.4.5. Synopsis
    5. 7.5. Level 2 Contact Mechanism Pattern
      1. 7.5.1. Why Do We Need This Pattern?
      2. 7.5.2. How Does This Pattern Work?
      3. 7.5.3. When Should This Pattern Be Used?
      4. 7.5.4. What Are the Weaknesses of the Pattern?
      5. 7.5.5. Synopsis
    6. 7.6. Level 3 Contact Mechanism Pattern
      1. 7.6.1. Why Do We Need This Pattern?
      2. 7.6.2. How Does This Pattern Work?
      3. 7.6.3. When Should This Pattern Be Used?
      4. 7.6.4. What Are the Weaknesses of the Pattern?
      5. 7.6.5. Synopsis
    7. 7.7. Level 4 Contact Mechanism Pattern
      1. 7.7.1. Why Do We Need This Pattern?
      2. 7.7.2. How Does This Pattern Work?
      3. 7.7.3. When Should This Pattern Be Used?
      4. 7.7.4. What Are the Weaknesses of the Pattern?
      5. 7.7.5. Synopsis
    8. 7.8. Contact Mechanism Pattern with Geographic Boundary
      1. 7.8.1. Why Do We Need This Pattern?
      2. 7.8.2. How Does This Pattern Work?
      3. 7.8.3. When Should This Pattern Be Used?
      4. 7.8.4. What Are the Weaknesses of the Pattern?
      5. 7.8.5. Synopsis
    9. 7.9. Contact Mechanism with Flexible Address Parts Pattern
      1. 7.9.1. Why Do We Need This Pattern?
      2. 7.9.2. How Does This Pattern Work?
      3. 7.9.3. When Should This Pattern Be Used?
      4. 7.9.4. What Are the Weaknesses of the Pattern?
      5. 7.9.5. Synopsis
    10. 7.10. Other Common Contact Mechanism Data
      1. 7.10.1. Non-Solicitation
      2. 7.10.2. Instructions
      3. 7.10.3. Directions
      4. 7.10.4. Telephone Extensions
      5. 7.10.5. Synopsis
    11. 7.11. Summary of Patterns
    12. 7.12. References
  14. 8. Business Rules: How Things Should Work
    1. 8.1. What Is the Significance of This Type of Pattern?
    2. 8.2. What Is in This Chapter?
    3. 8.3. What Is a Business Rule?
    4. 8.4. Level 2 Business Rules Pattern
      1. 8.4.1. Why Do We Need This Pattern?
      2. 8.4.2. How Does This Pattern Work?
      3. 8.4.3. When Should This Pattern Be Used?
      4. 8.4.4. What Are the Weaknesses of the Pattern?
      5. 8.4.5. Synopsis
    5. 8.5. Level 3 Business Rules Pattern
      1. 8.5.1. Why Do We Need This Pattern?
      2. 8.5.2. How Does This Pattern Work?
      3. 8.5.3. When Should This Pattern Be Used?
      4. 8.5.4. What Are the Weaknesses of the Pattern?
      5. 8.5.5. Synopsis
    6. 8.6. Business Rules with Party Roles
      1. 8.6.1. Why Do We Need This Pattern?
      2. 8.6.2. How Do These Patterns Work?
      3. 8.6.3. When Should This Pattern Be Used?
      4. 8.6.4. What Are the Weaknesses of the Pattern?
      5. 8.6.5. Synopsis
    7. 8.7. Summary of Patterns
    8. 8.8. References
  15. 9. Using the Patterns
    1. 9.1. What Is in This Chapter?
    2. 9.2. The Scenario
    3. 9.3. Prototype Models, Scope Statements
      1. 9.3.1. The Scenario for These Models
      2. 9.3.2. How Do These Models Work?
      3. 9.3.3. Why Do We Do It This Way?
      4. 9.3.4. What Are the Strengths of Using Patterns for the Solution?
      5. 9.3.5. What Are the Weaknesses of Using Patterns for the Solution?
      6. 9.3.6. Synopsis
    4. 9.4. Application Data Models
      1. 9.4.1. The Scenario for This Model
      2. 9.4.2. How Do These Models Work?
      3. 9.4.3. Why Do We Do It This Way?
      4. 9.4.4. What Are the Strengths of Using Patterns for the Solution?
      5. 9.4.5. What Are the Weaknesses of Using Patterns for the Solution?
      6. 9.4.6. Synopsis
    5. 9.5. Enterprise Data Models
      1. 9.5.1. The Scenario for This Model
      2. 9.5.2. How Do These Models Work?
      3. 9.5.3. Why Do We Do It This Way?
      4. 9.5.4. What Are the Strengths of Using Patterns for the Solution?
      5. 9.5.5. What Are the Weaknesses of Using Patterns for the Solution?
      6. 9.5.6. Synopsis
    6. 9.6. Data Warehouse Models
      1. 9.6.1. The Scenario for This Model
      2. 9.6.2. Data Warehouse Data Models—Relational Approach
        1. 9.6.2.1. How Do These Models Work?
        2. 9.6.2.2. Why Do We Do It This Way?
        3. 9.6.2.3. What Are the Strengths of Using Patterns for the Solution?
        4. 9.6.2.4. What Are the Weaknesses of Using Patterns for the Solution?
        5. 9.6.2.5. Synopsis
      3. 9.6.3. Data Warehouse/Data Mart Data Models—Star Schemas
        1. 9.6.3.1. How Do These Models Work?
        2. 9.6.3.2. Why Do We Do It This Way?
        3. 9.6.3.3. What Are the Strengths of Using Patterns for the Solution?
        4. 9.6.3.4. What Are the Weaknesses of Using Patterns for the Solution?
        5. 9.6.3.5. Synopsis
    7. 9.7. Master Data Management
      1. 9.7.1. The Scenario for This Model
      2. 9.7.2. How Does This Model Work?
      3. 9.7.3. Why Do We Do It This Way?
      4. 9.7.4. What Are the Strengths of Using Patterns for the Solution?
      5. 9.7.5. What Are the Weaknesses of Using Patterns for the Solution?
      6. 9.7.6. Synopsis
    8. 9.8. Other Thoughts Regarding Using the Patterns
      1. 9.8.1. Physical Database Design
      2. 9.8.2. Other Applications for Patterns
      3. 9.8.3. Other Considerations When Using Generalized Patterns
    9. 9.9. Summary of Using the Patterns
    10. 9.10. References
  16. 10. Socializing the Patterns
    1. 10.1. What Is the Significance of Socializing the Patterns?
    2. 10.2. What Is in This Chapter?
    3. 10.3. Experiences Using and Socializing These Patterns
    4. 10.4. What Makes the Difference In Socializing the Patterns?
    5. 10.5. Understanding Motivations—Why Would Someone Use or Not Use the Patterns?
    6. 10.6. Creating a Clear, Common, Compelling Purpose and Vision for Using the Patterns
    7. 10.7. Developing Trust so People Can Rely on the Patterns
    8. 10.8. Managing Resistance and/or Conflict Regarding Patterns
    9. 10.9. Other Comments about Socializing the Patterns
      1. 10.9.1. Patterns May Need to Be Socialized in Many Types of Circumstances
      2. 10.9.2. What about Upper-Level Management Commitment?
      3. 10.9.3. What Is the Return on Investment Regarding Using These Patterns?
    10. 10.10. Summary
    11. 10.11. References