Data Model Resource Book, Volume 2

LEN SILVERSTON (lsilverston@univdata.com) is founder and owner of Universal Data Models, LLC (www.universaldatamodels.com), a Colorado-based firm providing consulting and training for helping enterprises customize and implement "universal data models" and develop holistic, integrated systems. Mr. Silverston has over 20 years' experience in delivering data integration, database and data warehouse solutions to organizations.

"I thought the models in the books were well thought out and adequately explained." (Computer Shopper, February 2002)

• Foreword xiiiAcknowledgments xvAbout the Author xviiChapter 1 Introduction 1Why is There a Need for This Book? 1To Integrate or Disintegrate? That is the Question 2Approach of This Book 4Who is the Intended Audience for This Book? 5General Industry Models versus More Specific Industry Models 5Industry Data Models versus Data Model Applications 7The Volume 2 Models: Customized and New Models for Each Industry 8Conventions and Standards Used in This Book 8Entities 8Subtypes and Supertypes 15Non-Mutually Exclusive Sets of Subtypes 16Attributes 17Relationships 18Relationship Optionality 18Relationship Cardinality 19Foreign Key Relationships 20Foreign Key Inheritance 20Intersection or Association Entities to Handle Many-to Many Relationships 21Exclusive Arcs 22Recursive Relationships 22Physical Models 23Conventions Used for Illustration Tables 24Conventions Used to Reference Figures 25The Data Model Resource Book, Volume 2, Industry Download Products 25Chapter 2 Manufacturing 27People and Organizations in Manufacturing 31Product Models in Manufacturing 33Products and Parts 33Design Engineering 36Part Specifications and Documentation 36Part Specification Roles and Status 40Engineering Change 41Product Bill of Material, Substitutes, and Inventory Configurations 43Product Bill of Materials 44Part Substitutions 46Inventory Item Configurations 50Other Product Models 53Orders 54Usage of the Order Models for Manufacturers 54Materials Requirements Planning 56Delivery 57Deployment and Use of Products 58Deployments 58Deployment Usage 60Inventory Ownership 62Work Effort 63Process Plan 63Production Runs 65Invoicing, Accounting, Budgeting, and Human Resources Models 67Star Schema for Manufacturing 69Production Run Fact 70Dimensions 71Variation of Product Run Data Mart 72Manufacturing Summary 73Chapter 3 Telecommunications 75People and Organizations in Telecommunications 80Generic Person and Organization Roles from Volume 1 80Party Roles and Relationships for Telecommunications 80Example of Party Roles and Relationships for Telecommunications 83Telecommunications Products 84Telecommunications Modifications to the Generic Product Data Models 84Telecommunications Services 86Telecommunications Goods 88Telecommunications Features 88Product Feature Interaction 89Telecommunications Product and Product Feature Example 89Product Deployment 91Telecommunications Product Associations 93Network Data Models 98Network Components 99Network Assemblies 101Circuits 104Product, Circuit, and Network Assembly Capabilities 106Communication IDs and Contact Mechanisms 109Orders 111Service Orders 111Product Availability 113Delivery 114Deployment Usage 114Invoicing 118Work Effort, Accounting, and Human Resources Models 120Star Schema for Telecommunications 121Product Deployment Usage Fact 122Customers 123Deployment Usage Types and Unit of Measures Dimensions 123Products 123Facilities 124Time by Hour 124Telecommunications Summary 124Chapter 4 Health Care 127People and Organizations in Health Care 129Person Roles 129Organization Roles 132Insured Party Roles 133Party Relationships 134Party Roles Example 134Health Care Facilities and Contact Mechanisms 138Patient, Practitioner, and Provider Information 138Health Care Products 141Health Care Offering Definition 141Health Care “Orders” 144Agreement Definition 145Health Care Shipments and Delivery 148Health Care Episodes, Incidents and Visits 148Health Care Delivery 151Health Care Claims 154Invoice Data Models versus Claims Data Models 154Health Care Claims Submission 156Claim Header Information 157Claim Codes 159Insurance Policy Information 160Payment Settlement 162Health Care Referrals 164Star Schema for Health Care 165Health Care Summary 167Chapter 5 Insurance 171People and Organizations in Insurance 173Person Roles 176Organization Roles 178Person or Organization Roles 179Insurance Party Relationships 181Insurance Product 184Insurance Products and Categories 185Insurance Product Coverage 188Details behind Coverage Types and Coverage Levels 190Insurance Product Features 194Insurance Product Rules 196Insurance Pricing 200Community-Based Rating 200Insurance Rate Tables 203Experienced-Based Insurance Rating 206Insurance Policies (Orders for Insurance) 206Insurance Application 206Insurance Quote 208The Insurance Agreement or Insurance Policy 213Insurance Policy Roles 214Insurance Policy Items 216Health Care Insurance Agreement 219Casualty Insurance Agreement 224Property Insurance Agreement 225Life Insurance Agreement 226Premium Schedule 227Premium Invoicing and Payments 231Policy Claims 231Insurance Claim Incidents 232Insurance Claims Submission 234Claims Settlement 237Delivery, Work Efforts, Accounting, and Human Resources for Insurance Enterprises 240Star Schemas for Insurance 241Analysis Information 241Claim Star Schema 241Fact Table 242Dimensions 242Insurance Summary 245Chapter 6 Financial Services 247People and Organizations in Financial Services 249Generic Party Role Subtypes 252Financial Service Party Roles 252Financial Service Relationships 254Financial Objectives, Needs, and Plans 257Financial Services Products 261Financial Services Product Definition 262Product Categories 264Product Feature and Functional Setting 264Financial Product and Functional Setting 265Features and Functional Settings for Product Categories 266Features and Functional Settings for Products 267Example of Predefined Financial Products with Product Features and Functional Settings 267Financial Products That are Customized for the Specific Needs of a Customer 268Product Category Roll-ups 272Financial Product Regulations and Rules 273Agreements 276Financial Agreements 276Agreement Subtypes and Roles 276Asset Roles and Agreement Asset Usage 278Agreement Status 279Delivery 279Financial Account 279Account Product 280Account Roles 282Account Relationships 282Media 283Account Status 283Account Transaction 284Account Transaction Type 284Account Transaction Status 286Account Transaction Relationships 286Account Transaction Tasks 288Work Efforts 290Account Notification 290Notification Task Types 291Invoicing and Statement Tasks 291Marketing Tasks 293Alert Tasks 294Other Notification Tasks 294Example of Account Notification Tasks 295Analysis Task 295Analysis Task Data Model 297Example of Risk Analysis Task 299Invoicing, Accounting, and Human Resources 299Star Schemas for Financial Services 300Analysis Information 300Account Star Schema 300Fact Table 302Dimensions 302Account Transaction Star Schema 304Fact Table 304Dimensions 304Financial Services Summary 306Chapter 7 Professional Services 309People and Organizations in Professional Services 311Person Roles 313Organization Roles 314Professional Services Roles and Relationships Examples 314Professional Services Products 316Professional Services Products Model 317Applicability of Other Product Models 319Professional Service Product Associations 321Orders 321Professional Services Requirements 322Professional Services Requests and Quotes 325Professional Services Requests 328Professional Service Quotes (Proposals and Statements of Work) 330Engagements 333Types of Engagement Items 333Engagement Rates 335Placement versus Deliverables-Based Consulting 336Engagement Example 336Professional Services Agreements 338Delivery 341Professional Services Entries 341Work Efforts 343Invoicing 346Accounting and Human Resources Management 347Star Schema for Professional Services 348Time Entry Fact 349Dimensions 350Professional Services Summary 350Chapter 8 Travel 353People and Organizations in Travel 355Person Roles 357Organization Roles 357Party Relationships 359Travel Preferences 362Travel Products 364Product Definition 364Travel Orders (Reservations) 369Order Models 370Ticketing 373Agreements 376Travel Agreements 376Pricing of Agreements and Products 376Delivery (Travel Experience) 379Invoicing 383Work Efforts 383Travel Programs and Travel Accounts 383Travel Programs, Rules, and Factors 385Travel Account Example 385Star Schemas for Travel 386Passenger Transportation Offering Star Schema 386Fact Table 386Dimensions 387Non-Transportation Travel Product Star Schema 388Fact Table 388Dimensions 388Travel Summary 389Chapter 9 E-Commerce Models 393People and Organizations in E-Commerce 395E-Commerce Parties, Roles, and Relationships 397Automated Agent Party Subtype 398Generic Party Roles from Volume 1 399E-Commerce Party Roles and Relationships 399Is There Always a PARTY for a PARTY ROLE? 400Example Data for E-Commerce Party Roles and Relationships 400Party Contact Mechanisms in E-Commerce 402Web Site Content and User Login Information 405Web Site Content 405Login Account 407E-Commerce Products and Objects 408Orders in E-Commerce 410Party and Product Needs 412Subscriptions 414Web Visits 418Web Hits and Web Logs 419Web Visits Model 420VISIT Information 422SERVER HIT Information 422Defining a Visit 423Delivery, Work Efforts, Invoicing, Accounting, and Human Resources 426Web Server Hits Star Schema 426Server Hit Star Schema 427Server Hit Fact 428Visitors Dimension 428ISPS Dimension 429Referrers Dimension 429Web Contents Dimension 429User Agent Types Dimension 430Products Dimension 430Time by Hour Dimension 430Web Visit Star Schema 431E-Commerce Summary 433Chapter 10 Using the Industry Models in the Real World 435Using the Models to Build Transaction-Oriented Systems and Data Warehouses 436Enterprises in Other Industries 437Relevance of Models to Very Specific Industries 438Diverse Enterprises Needing Models from Several Chapters 438Setting Up Modularized Data Models for Use Across Industries 439For More Information 439Appendix A Entities and Attributes for Manufacturing Models 443Entities and Attributes Listing for Manufacturing 445Appendix B Entities and Attributes for Telecommunications Models 453Entities and Attributes Listing for Telecommunications Models 454Appendix C Entities and Attributes for Health Care Models 463Entities and Attributes Listing for Health Care Models 464Appendix D Entities and Attributes for Insurance Models 475Entities and Attributes Listing for Insurance Models 476Appendix E Entities and Attributes for Financial Services Models 487Entities and Attributes Listing for Financial Services Models 488Appendix F Entities and Attributes for Professional Services Models 497Entities and Attributes Listing for Professional Services Models 498Appendix G Entities and Attributes for Travel Models 507Entities and Attributes Listing for Travel Models 508Appendix H Entities and Attributes for E-Commerce Models 519Entities and Attributes Listing for E-Commerce Models 520Appendix I List of Entities and Their Associated Figures 525Other Reusable Data Model and Data Warehouse Design Resources 541Reusable Data Model Resources 541Reusable Data Warehouse Design Resources 542How to Use the Volume 2 Industry Electronic Products 543Contents of the Industry Electronic Products 544Using the Scripts 545How to Use the Industry Electronic Products in Conjunction with the Data Model Resource CD-ROM, Volume 1 545Platform-Specific and Other Information on the Electronic Products 545Index 547