The Data Model Resource Book, Volume 2: A Library of Universal Data Models by Industry Types, Revised Edition

Foreword xiii

Acknowledgments xv

About the Author xvii

Chapter 1 Introduction 1

Why is There a Need for This Book? 1

To Integrate or Disintegrate? That is the Question 2

Approach of This Book 4

Who is the Intended Audience for This Book? 5

General Industry Models versus More Specific Industry Models 5

Industry Data Models versus Data Model Applications 7

The Volume 2 Models: Customized and New Models for Each Industry 8

Conventions and Standards Used in This Book 8

Entities 8

Subtypes and Supertypes 15

Non-Mutually Exclusive Sets of Subtypes 16

Attributes 17

Relationships 18

Relationship Optionality 18

Relationship Cardinality 19

Foreign Key Relationships 20

Foreign Key Inheritance 20

Intersection or Association Entities to Handle Many-to Many Relationships 21

Exclusive Arcs 22

Recursive Relationships 22

Physical Models 23

Conventions Used for Illustration Tables 24

Conventions Used to Reference Figures 25

The Data Model Resource Book, Volume 2, Industry Download Products 25

Chapter 2 Manufacturing 27

People and Organizations in Manufacturing 31

Product Models in Manufacturing 33

Products and Parts 33

Design Engineering 36

Part Specifications and Documentation 36

Part Specification Roles and Status 40

Engineering Change 41

Product Bill of Material, Substitutes, and Inventory Configurations 43

Product Bill of Materials 44

Part Substitutions 46

Inventory Item Configurations 50

Other Product Models 53

Orders 54

Usage of the Order Models for Manufacturers 54

Materials Requirements Planning 56

Delivery 57

Deployment and Use of Products 58

Deployments 58

Deployment Usage 60

Inventory Ownership 62

Work Effort 63

Process Plan 63

Production Runs 65

Invoicing, Accounting, Budgeting, and Human Resources Models 67

Star Schema for Manufacturing 69

Production Run Fact 70

Dimensions 71

Variation of Product Run Data Mart 72

Manufacturing Summary 73

Chapter 3 Telecommunications 75

People and Organizations in Telecommunications 80

Generic Person and Organization Roles from Volume 1 80

Party Roles and Relationships for Telecommunications 80

Example of Party Roles and Relationships for Telecommunications 83

Telecommunications Products 84

Telecommunications Modifications to the Generic Product Data Models 84

Telecommunications Services 86

Telecommunications Goods 88

Telecommunications Features 88

Product Feature Interaction 89

Telecommunications Product and Product Feature Example 89

Product Deployment 91

Telecommunications Product Associations 93

Network Data Models 98

Network Components 99

Network Assemblies 101

Circuits 104

Product, Circuit, and Network Assembly Capabilities 106

Communication IDs and Contact Mechanisms 109

Orders 111

Service Orders 111

Product Availability 113

Delivery 114

Deployment Usage 114

Invoicing 118

Work Effort, Accounting, and Human Resources Models 120

Star Schema for Telecommunications 121

Product Deployment Usage Fact 122

Customers 123

Deployment Usage Types and Unit of Measures Dimensions 123

Products 123

Facilities 124

Time by Hour 124

Telecommunications Summary 124

Chapter 4 Health Care 127

People and Organizations in Health Care 129

Person Roles 129

Organization Roles 132

Insured Party Roles 133

Party Relationships 134

Party Roles Example 134

Health Care Facilities and Contact Mechanisms 138

Patient, Practitioner, and Provider Information 138

Health Care Products 141

Health Care Offering Definition 141

Health Care “Orders” 144

Agreement Definition 145

Health Care Shipments and Delivery 148

Health Care Episodes, Incidents and Visits 148

Health Care Delivery 151

Health Care Claims 154

Invoice Data Models versus Claims Data Models 154

Health Care Claims Submission 156

Claim Header Information 157

Claim Codes 159

Insurance Policy Information 160

Payment Settlement 162

Health Care Referrals 164

Star Schema for Health Care 165

Health Care Summary 167

Chapter 5 Insurance 171

People and Organizations in Insurance 173

Person Roles 176

Organization Roles 178

Person or Organization Roles 179

Insurance Party Relationships 181

Insurance Product 184

Insurance Products and Categories 185

Insurance Product Coverage 188

Details behind Coverage Types and Coverage Levels 190

Insurance Product Features 194

Insurance Product Rules 196

Insurance Pricing 200

Community-Based Rating 200

Insurance Rate Tables 203

Experienced-Based Insurance Rating 206

Insurance Policies (Orders for Insurance) 206

Insurance Application 206

Insurance Quote 208

The Insurance Agreement or Insurance Policy 213

Insurance Policy Roles 214

Insurance Policy Items 216

Health Care Insurance Agreement 219

Casualty Insurance Agreement 224

Property Insurance Agreement 225

Life Insurance Agreement 226

Premium Schedule 227

Premium Invoicing and Payments 231

Policy Claims 231

Insurance Claim Incidents 232

Insurance Claims Submission 234

Claims Settlement 237

Delivery, Work Efforts, Accounting, and Human Resources for Insurance Enterprises 240

Star Schemas for Insurance 241

Analysis Information 241

Claim Star Schema 241

Fact Table 242

Dimensions 242

Insurance Summary 245

Chapter 6 Financial Services 247

People and Organizations in Financial Services 249

Generic Party Role Subtypes 252

Financial Service Party Roles 252

Financial Service Relationships 254

Financial Objectives, Needs, and Plans 257

Financial Services Products 261

Financial Services Product Definition 262

Product Categories 264

Product Feature and Functional Setting 264

Financial Product and Functional Setting 265

Features and Functional Settings for Product Categories 266

Features and Functional Settings for Products 267

Example of Predefined Financial Products with Product Features and Functional Settings 267

Financial Products That are Customized for the Specific Needs of a Customer 268

Product Category Roll-ups 272

Financial Product Regulations and Rules 273

Agreements 276

Financial Agreements 276

Agreement Subtypes and Roles 276

Asset Roles and Agreement Asset Usage 278

Agreement Status 279

Delivery 279

Financial Account 279

Account Product 280

Account Roles 282

Account Relationships 282

Media 283

Account Status 283

Account Transaction 284

Account Transaction Type 284

Account Transaction Status 286

Account Transaction Relationships 286

Account Transaction Tasks 288

Work Efforts 290

Account Notification 290

Notification Task Types 291

Invoicing and Statement Tasks 291

Marketing Tasks 293

Alert Tasks 294

Other Notification Tasks 294

Example of Account Notification Tasks 295

Analysis Task 295

Analysis Task Data Model 297

Example of Risk Analysis Task 299

Invoicing, Accounting, and Human Resources 299

Star Schemas for Financial Services 300

Analysis Information 300

Account Star Schema 300

Fact Table 302

Dimensions 302

Account Transaction Star Schema 304

Fact Table 304

Dimensions 304

Financial Services Summary 306

Chapter 7 Professional Services 309

People and Organizations in Professional Services 311

Person Roles 313

Organization Roles 314

Professional Services Roles and Relationships Examples 314

Professional Services Products 316

Professional Services Products Model 317

Applicability of Other Product Models 319

Professional Service Product Associations 321

Orders 321

Professional Services Requirements 322

Professional Services Requests and Quotes 325

Professional Services Requests 328

Professional Service Quotes (Proposals and Statements of Work) 330

Engagements 333

Types of Engagement Items 333

Engagement Rates 335

Placement versus Deliverables-Based Consulting 336

Engagement Example 336

Professional Services Agreements 338

Delivery 341

Professional Services Entries 341

Work Efforts 343

Invoicing 346

Accounting and Human Resources Management 347

Star Schema for Professional Services 348

Time Entry Fact 349

Dimensions 350

Professional Services Summary 350

Chapter 8 Travel 353

People and Organizations in Travel 355

Person Roles 357

Organization Roles 357

Party Relationships 359

Travel Preferences 362

Travel Products 364

Product Definition 364

Travel Orders (Reservations) 369

Order Models 370

Ticketing 373

Agreements 376

Travel Agreements 376

Pricing of Agreements and Products 376

Delivery (Travel Experience) 379

Invoicing 383

Work Efforts 383

Travel Programs and Travel Accounts 383

Travel Programs, Rules, and Factors 385

Travel Account Example 385

Star Schemas for Travel 386

Passenger Transportation Offering Star Schema 386

Fact Table 386

Dimensions 387

Non-Transportation Travel Product Star Schema 388

Fact Table 388

Dimensions 388

Travel Summary 389

Chapter 9 E-Commerce Models 393

People and Organizations in E-Commerce 395

E-Commerce Parties, Roles, and Relationships 397

Automated Agent Party Subtype 398

Generic Party Roles from Volume 1 399

E-Commerce Party Roles and Relationships 399

Is There Always a PARTY for a PARTY ROLE? 400

Example Data for E-Commerce Party Roles and Relationships 400

Party Contact Mechanisms in E-Commerce 402

Web Site Content and User Login Information 405

Web Site Content 405

Login Account 407

E-Commerce Products and Objects 408

Orders in E-Commerce 410

Party and Product Needs 412

Subscriptions 414

Web Visits 418

Web Hits and Web Logs 419

Web Visits Model 420

VISIT Information 422

SERVER HIT Information 422

Defining a Visit 423

Delivery, Work Efforts, Invoicing, Accounting, and Human Resources 426

Web Server Hits Star Schema 426

Server Hit Star Schema 427

Server Hit Fact 428

Visitors Dimension 428

ISPS Dimension 429

Referrers Dimension 429

Web Contents Dimension 429

User Agent Types Dimension 430

Products Dimension 430

Time by Hour Dimension 430

Web Visit Star Schema 431

E-Commerce Summary 433

Chapter 10 Using the Industry Models in the Real World 435

Using the Models to Build Transaction-Oriented Systems and Data Warehouses 436

Enterprises in Other Industries 437

Relevance of Models to Very Specific Industries 438

Diverse Enterprises Needing Models from Several Chapters 438

Setting Up Modularized Data Models for Use Across Industries 439

For More Information 439

Appendix A Entities and Attributes for Manufacturing Models 443

Entities and Attributes Listing for Manufacturing 445

Appendix B Entities and Attributes for Telecommunications Models 453

Entities and Attributes Listing for Telecommunications Models 454

Appendix C Entities and Attributes for Health Care Models 463

Entities and Attributes Listing for Health Care Models 464

Appendix D Entities and Attributes for Insurance Models 475

Entities and Attributes Listing for Insurance Models 476

Appendix E Entities and Attributes for Financial Services Models 487

Entities and Attributes Listing for Financial Services Models 488

Appendix F Entities and Attributes for Professional Services Models 497

Entities and Attributes Listing for Professional Services Models 498

Appendix G Entities and Attributes for Travel Models 507

Entities and Attributes Listing for Travel Models 508

Appendix H Entities and Attributes for E-Commerce Models 519

Entities and Attributes Listing for E-Commerce Models 520

Appendix I List of Entities and Their Associated Figures 525

Other Reusable Data Model and Data Warehouse Design Resources 541

Reusable Data Model Resources 541

Reusable Data Warehouse Design Resources 542

How to Use the Volume 2 Industry Electronic Products 543

Contents of the Industry Electronic Products 544

Using the Scripts 545

How to Use the Industry Electronic Products in Conjunction with the Data Model Resource CD-ROM, Volume 1 545

Platform-Specific and Other Information on the Electronic Products 545

Index 547