The Data Model Resource Book, Volume 2
Description
A quick and reliable way to build proven databases for core business functions
Industry experts raved about The Data Model Resource Book when it was first published in March 1997 because it provided a simple, cost-effective way to design databases for core business functions. Len Silverston has now revised and updated the hugely successful First Edition, while adding a companion volume to take care of more specific requirements of different businesses. Each volume is accompanied by a CD-ROM, which is sold separately. Each CD-ROM provides powerful design templates discussed in the books in a ready-to-use electronic format, allowing companies and individuals to develop the databases they need at a fraction of the cost and a third of the time it would take to build them from scratch.
With each business function boasting its own directory, this CD-ROM provides a variety of data models for specific implementations in such areas as financial services, insurance, retail, healthcare, universities, and telecom.
Datenmodelle für Kernfunktionen, die in nahezu allen Geschäftsbereichen eine Rolle spielen: Dieses zweibändige, überarbeitete Handbuch in der 2. Auflage zeigt Datenbankprogrammierern, wie sie zwei Drittel der üblichen Entwicklungszeit sparen können! Im Mittelpunkt dieses 2. Bandes steht die Erstellung von Datenmodellen für verschiedenste Anwendungsgebiete - von der Finanzdienstleistung über das Versicherungswesen bis zu Bildungsangeboten. Dabei wird jeweils analysiert, wie die im ersten Band des Werkes vorgestellten generischen Modelle auf die spezielle Anwendung zuzuschneiden sind. Eine separat erhältliche CD-ROM beinhaltet sämtliche Datenmodelle in einer Form, die leicht in gebräuchliche kommerzielle Datenbanken zu integrieren ist.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
"I thought the models in the books were well thought out and adequately explained." (Computer Shopper, February 2002)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.
Data Modeling and Design
"These books are a must for any company implementing data models. They contain practical insights and templates of universal data models which can be used by all enterprises, regardless of their level of experience."
—Ron Powell, Publisher, DM Review
If you've relied on The Data Model Resource Book, Revised Edition, Volume 1 (0-471-38023-7) to jump-start your system development efforts, then you know that using a library of proven data models and data warehouse designs can produce huge savings in development costs. But what about the unique needs of your industry?
The Data Model Resource Book, Revised Edition, Volume 2, provides data models specifically tailored to your industry. It includes both brand-new models for each industry and generic models that the author customizes. The result is a comprehensive library of the industry's core data models and applications, including models for:
- Financial services
- Professional services
- E-commerce
- Manufacturing
- Health care
- Insurance
- Travel
- Telecommunications
You not only get the data models themselves, you also learn to:
- Apply reusable data designs across a broad variety of industries
- Integrate databases and data warehouses across your organization
- Combine models from different applications for your specific needs
- Use the data structures to provide quality assurance for your own models
- Employ the book's star schema designs to develop data analysis solutions
You can save even more time and money with The Data Model Resource Book, Revised Edition, Volume 2, CD-ROM, which:
- Provides the SQL code you'll need to implement the models described in the book or to reverse-engineer them into your CASE tool
- Allows you to view for free the demonstration of data models from both Volume 1 and Volume 2
- Allows you to purchase online the downloadable models by industry type—directions inside
"Len Silverston has produced an enormously useful two-volume compendium of generic (but not too generic) data models for an extensive set of typical enterprise subject areas, and for various industries that any data modeler will likely encounter at some point in his or her career. The material is clearly written, well organized, and goes below the obvious to some of the more perverse and difficult information requirements in an enterprise. This is an invaluable resource for doing one's homework before diving into any modeling session; if you can't find it here, there is certainly a very similar template that you can use for just about any situation with which you might be faced."-- William G. Smith President, William G. Smith & Associates
"In today's fast-paced e-oriented world, it is no longer acceptable to bury business constraints in hard-to-change data structures. Data architects must comprehend complex requirements and recast them into data architecture with vision for unforeseen futures. Len's models provide an outstanding starting point for novice and advanced data architects for delivering flexible data models. These models position an organization for the business rule age. Their proper implementation and customization allows the organization to externalize and manage business policies and rules so that the business can proactively change itself. In this way, the data architecture, based on Len's models and procedures for customizing them, becomes by design the foundation for business change."--Barbara von Halle Founder, Knowledge Partners, Inc. Co-author of Handbook of Relational Database Design
"These books are long overdue and a must for any company implementing universal data models. They contain practical insights and templates for implementing universal data models and can help all enterprises regardless of their level of experience. Most books address the needs for data models but give little in the way of practical advice. These books fill in that void and should be utilized by all enterprises."--Ron Powell Publisher, DM Review
"I was first introduced to The Data Model Resource Book three years ago when I was hired by a firm that wanted an enterprise data model. This company did not believe the dictum that "all companies are basically the same;" they felt they were somehow unique. After a little analysis with Len Silverston's help, we found that we were actually quite a bit the same: we had customers, accounts, employees, benefits, and all the things you'd find in any corporation. All we had to do was adapt the product component of Len's book and we were ready to move ahead with a great framework for all of our data. A CD-ROM that accompanies the book provided scripts to build the model in Oracle very quickly. We then began mapping all of our detailed data types to the enterprise model and, voila, we could find a place for all of those various spellings and misspellings of Account Number.
Volume 2 of this revised edition provided even more exciting features: models of industry-specific data. I began to see interesting patterns that permeated this volume. For example, a reservation is a reservation, whether you're an airline, a restaurant, or a hotel. (We even have something similar in the oil industry--the allocation.)
Another concept from the book that has changed my thinking and vocabulary is the word "party." I recently managed a project in which an employee could also function as a customer and as an on-line computer user. The team was in disagreement regarding a name for this entity; but after checking The Data Model Resource Book, we realized that here we had a party playing three roles.
Whether your job is to jump-start a data warehouse project or borrow ideas for any subject area in your next operational database, I highly recommend The Data Model Resource Books, Revised Edition, Volumes 1 and 2 as your bible for design."--Ted Kowalski Data Architect, Equilon Enterprises LLC Author of Opening Doors: A Facilitator's Handbook
PUBLISHER:
Wiley
ISBN-13:
9780471353485
BINDING:
Paperback
BISAC:
COMPUTERS
BOOK DIMENSIONS:
Dimensions: 185.40(W) x Dimensions: 231.10(H) x Dimensions: 30.50(D)
AUDIENCE TYPE:
General/Adult
LANGUAGE:
English