Data Model Resource Book

Len Silverston is the best-selling author of The Data Model Resource Book (Volumes 1 and 2), a speaker and data management consultant with more than 25 years of experience helping organizations integrate their information and systems. He is the owner and president of Universal Data Models, LLC. Paul Agnew is an author and consultant with more than 17 years of experience in the data management field in many different industries. He is an expert in data modeling, architecture, and integration. He is a senior partner at Universal Data Models, LLC (www.universaldatamodels.com).

• Foreword xxvChapter 1 Introduction 1Why Is There a Need for This Book? 1Extending the Discipline of Data Modeling 4What Is a Pattern and What Is a Universal Pattern? 5What Is the Significance of Patterns? 6Approach of This Book 6The Different Pattern Levels 7Who Is the Intended Audience for This Book? 14What Is in This Book 15Other Patterns for Data Modeling 17Conventions and Standards Used in This Book 18Entities 18Subtypes and Supertypes 19Attributes 20Relationships 22Relationship Optionality 23Relationship Cardinality 25Foreign Key Relationships 26Associative Entities to Handle Many-to-Many Relationships 26Exclusive Arcs 27Example Data in Illustration Tables 28Data Modeling Notation 29Summary 33References 33Chapter 2 Setting Up Roles: What Parties Do 35What Is the Significance of This Type of Pattern? 36What Is in This Chapter? 36What Is a Declarative Role? 37Level 1 Declarative Role Pattern 38Why Do We Need This Pattern? 40How Does This Pattern Work? 41When Should This Pattern Be Used? 44What Are the Weaknesses of the Pattern? 45Synopsis 46Level 2 Declarative Role Pattern 47Why Do We Need This Pattern? 48How Does This Pattern Work? 48When Should This Pattern Be Used? 53What Are the Weaknesses of the Pattern? 54Synopsis 55Level 3 Declarative Role Pattern 56Why Do We Need This Pattern? 56How Does This Pattern Work? 57When Should This Pattern Be Used? 63What Are the Weaknesses of the Pattern? 64Synopsis 65Summary of Patterns 65References 69Chapter 3 Using Roles: How Parties Are Involved 71What Is the Significance of This Type of Pattern? 72What Is in This Chapter? 72What Is a Contextual Role? 73Level 1 Contextual Role Pattern, Attributes 74Why Do We Need This Pattern? 74How Does This Pattern Work? 76When Should This Pattern Be Used? 78What Are the Weaknesses of the Pattern? 79Synopsis 80Level 1 Contextual Role Pattern, Relationships 80Why Do We Need This Pattern? 81How Does This Pattern Work? 81When Should This Pattern Be Used? 87What Are the Weaknesses of the Pattern? 87Synopsis 88Level 2 Contextual Role Pattern 88Why Do We Need This Pattern? 89How Does This Pattern Work? 89When Should This Pattern Be Used? 97What Are the Weaknesses of the Pattern? 98Synopsis 99Level 2 Contextual Role Pattern, PARTY Only Alternative 99Why Do We Need This Pattern? 100How Does This Pattern Work? 100When Should This Pattern Be Used? 106What Are the Weaknesses of the Pattern? 106Synopsis 107Level 3 Contextual Role Pattern 107Why Do We Need This Pattern? 108How Does This Pattern Work? 108When Should This Pattern Be Used? 114What Are the Weaknesses of the Pattern? 115Synopsis 116Hybrid Contextual Role Pattern 117Why Do We Need This Pattern? 117How Does This Pattern Work? 118When Should This Pattern Be Used? 123What Are the Weaknesses of the Pattern? 123Synopsis 124Summary of Patterns 124References 131Chapter 4 Hierarchies, Aggregations, and Peer-to-Peer Relationships: The Organization of Similar Data 133What Is the Significance of This Type of Pattern? 133What Is in This Chapter? 134What Is a Recursive Relationship and How Is Data Organized by Recursive Relationships? 134Level 1 Recursive Pattern 138Why Do We Need This Pattern? 138How Does This Pattern Work? 139When Should This Pattern Be Used? 144What Are the Weaknesses of the Pattern? 145Synopsis 145Level 2 Recursive Pattern 146Why Do We Need This Pattern? 146How Does This Pattern Work? 147When Should This Pattern Be Used? 152What Are the Weaknesses of the Pattern? 153Synopsis 154Level 2 Expanded Recursive Pattern 155Why Do We Need This Pattern? 155How Does This Pattern Work? 156When Should This Pattern Be Used? 162What Are the Weaknesses of the Pattern? 162Synopsis 163Level 3 Recursive Pattern 164Why Do We Need This Pattern? 165How Does This Pattern Work? 165When Should This Pattern Be Used? 170What Are the Weaknesses of the Pattern? 171Synopsis 172Level 3 Recursive Pattern with Rules 173Why Do We Need This Pattern? 173How Does This Pattern Work? 174When Should This Pattern Be Used? 179What Are the Weaknesses of the Pattern? 180Synopsis 180Summary of Patterns 180References 186Chapter 5 Types and Categories: the Classification of Data 187What Is the Significance of This Type of Pattern? 187What Is in This Chapter? 188What Are Types, Categorizations, and Taxonomies? 189Level 1 Classification Pattern 190Why Do We Need This Pattern? 190How Does This Pattern Work? 190When Should This Pattern Be Used? 195What Are the Weaknesses of the Pattern? 196Synopsis 197Level 2 Classification Pattern 197Why Do We Need This Pattern? 197How Does This Pattern Work? 198When Should This Pattern Be Used? 205What Are the Weaknesses of the Pattern? 206Synopsis 207Level 3 Classification Pattern 208Why Do We Need This Pattern? 208How Does This Pattern Work? 208When Should This Pattern Be Used? 219What Are the Weaknesses of the Pattern? 220Synopsis 221Level 3 Classification Pattern with Rollups and Schemes 222Why Do We Need This Pattern? 222How Does This Pattern Work? 223When Should This Pattern Be Used? 229What Are the Weaknesses of the Pattern? 230Synopsis 230Summary of Patterns 230References 235Chapter 6 Status: The States of Data 237What Is the Significance of This Type of Pattern? 238What Is in This Chapter? 239What Is a Status? 240Level 1 Status Pattern 244Why Do We Need This Pattern? 245How Does This Pattern Work? 246When Should This Pattern Be Used? 251What Are the Weaknesses of the Pattern? 253Synopsis 253Level 2 Status Pattern, Current Status 254Why Do We Need This Pattern? 255How Does This Pattern Work? 255When Should This Pattern Be Used? 260What Are the Weaknesses of the Pattern? 260Synopsis 261Level 3 Status Pattern 261Why Do We Need This Pattern? 261How Does This Pattern Work? 262When Should This Pattern Be Used? 267What Are the Weaknesses of the Pattern? 267Synopsis 268Level 4 Status Pattern 269Why Do We Need This Pattern? 269How Does This Pattern Work? 269When Should This Pattern Be Used? 277What Are the Weaknesses of the Pattern? 278Synopsis 279Status Category Pattern 280Why Do We Need This Pattern? 280How Does This Pattern Work? 280When Should This Pattern Be Used? 282What Are the Weaknesses of the Pattern? 285Synopsis 285Status Type with Multi Rollup and Rules Pattern 286Why Do We Need This Pattern? 286How Does This Pattern Work? 287When Should This Pattern Be Used? 293What Are the Weaknesses of the Pattern? 294Synopsis 294Summary of Patterns 294References 301Chapter 7 Contact Mechanisms: How to Get in Touch 303What Is the Significance of This Type of Pattern? 303What Is in This Chapter? 304What Is a Contact Mechanism? 305Level 1 Contact Mechanism Pattern 308Why Do We Need This Pattern? 309How Does This Pattern Work? 310When Should This Pattern Be Used? 321What Are the Weaknesses of the Pattern? 322Synopsis 323Level 2 Contact Mechanism Pattern 324Why Do We Need This Pattern? 325How Does This Pattern Work? 325When Should This Pattern Be Used? 342What Are the Weaknesses of the Pattern? 343Synopsis 344Level 3 Contact Mechanism Pattern 345Why Do We Need This Pattern? 346How Does This Pattern Work? 346When Should This Pattern Be Used? 361What Are the Weaknesses of the Pattern? 361Synopsis 362Level 4 Contact Mechanism Pattern 364Why Do We Need This Pattern? 364How Does This Pattern Work? 364When Should This Pattern Be Used? 378What Are the Weaknesses of the Pattern? 378Synopsis 379Contact Mechanism Pattern with Geographic Boundary 380Why Do We Need This Pattern? 381How Does This Pattern Work? 384When Should This Pattern Be Used? 389What Are the Weaknesses of the Pattern? 390Synopsis 390Contact Mechanism with Flexible Address Parts Pattern 391Why Do We Need This Pattern? 393How Does This Pattern Work? 394When Should This Pattern Be Used? 398What Are the Weaknesses of the Pattern? 398Synopsis 399Other Common Contact Mechanism Data 399Non-Solicitation 400Instructions 401Directions 402Telephone Extensions 402Synopsis 403Summary of Patterns 403References 410Chapter 8 Business Rules: How Things Should Work 411What Is the Significance of This Type of Pattern? 411What Is in This Chapter? 414What Is a Business Rule? 415Level 2 Business Rules Pattern 417Why Do We Need This Pattern? 417How Does This Pattern Work? 419When Should This Pattern Be Used? 442What Are the Weaknesses of the Pattern? 444Synopsis 444Level 3 Business Rules Pattern 446Why Do We Need This Pattern? 446How Does This Pattern Work? 447When Should This Pattern Be Used? 460What Are the Weaknesses of the Pattern? 461Synopsis 461Business Rules with Party Roles 462Why Do We Need This Pattern? 462How Do These Patterns Work? 463When Should This Pattern Be Used? 465What Are the Weaknesses of the Pattern? 465Synopsis 465Summary of Patterns 465References 468Chapter 9 Using the Patterns 469What Is in This Chapter? 470The Scenario 471Prototype Models, Scope Statements 472The Scenario for These Models 472How Do These Models Work? 473Why Do We Do It This Way? 478What Are the Strengths of Using Patterns for the Solution? 481What Are the Weaknesses of Using Patterns for the Solution? 482Synopsis 483Application Data Models 483The Scenario for This Model 484How Do These Models Work? 484Why Do We Do It This Way? 488What Are the Strengths of Using Patterns for the Solution? 490What Are the Weaknesses of Using Patterns for the Solution? 491Synopsis 492Enterprise Data Models 492The Scenario for This Model 495How Do These Models Work? 498Why Do We Do It This Way? 501What Are the Strengths of Using Patterns for the Solution? 501What Are the Weaknesses of Using Patterns for the Solution? 502Synopsis 502Data Warehouse Models 503The Scenario for This Model 504Data Warehouse Data Models — Relational Approach 506How Do These Models Work? 507Why Do We Do It This Way? 513What Are the Strengths of Using Patterns for the Solution? 514What Are the Weaknesses of Using Patterns for the Solution? 515Synopsis 515Data Warehouse/Data Mart Data Models — Star Schemas 516How Do These Models Work? 516Why Do We Do It This Way? 527What Are the Strengths of Using Patterns for the Solution? 528What Are the Weaknesses of Using Patterns for the Solution? 529Synopsis 530Master Data Management 530The Scenario for This Model 530How Does This Model Work? 534Why Do We Do It This Way? 543What Are the Strengths of Using Patterns for the Solution? 544What Are the Weaknesses of Using Patterns for the Solution? 545Synopsis 545Other Thoughts Regarding Using the Patterns 546Physical Database Design 546Other Applications for Patterns 547Other Considerations When Using Generalized Patterns 547Summary of Using the Patterns 548References 552Chapter 10 Socializing the Patterns 553What Is the Significance of Socializing the Patterns? 554What Is in This Chapter? 554Experiences Using and Socializing These Patterns 555What Makes the Difference In Socializing the Patterns? 558Understanding Motivations — Why Would Someone Use or Not Use the Patterns? 559Creating a Clear, Common, Compelling Purpose and Vision for Using the Patterns 566Developing Trust so People Can Rely on the Patterns 569Managing Resistance and/or Conflict Regarding Patterns 573Other Comments about Socializing the Patterns 580Patterns May Need to Be Socialized in Many Types of Circumstances 581What about Upper-Level Management Commitment? 581What Is the Return on Investment Regarding Using These Patterns? 582Summary 584References 585Index 587