The Data Model Resource Book, Volume 2: A Library of Universal Data Models by Industry Types / Edition 1 available in Paperback
The Data Model Resource Book, Volume 2: A Library of Universal Data Models by Industry Types / Edition 1
- ISBN-10:
- 0471353485
- ISBN-13:
- 9780471353485
- Pub. Date:
- 03/21/2001
- Publisher:
- Wiley
The Data Model Resource Book, Volume 2: A Library of Universal Data Models by Industry Types / Edition 1
Paperback
Buy New
$79.00Buy Used
$54.46-
SHIP THIS ITEMIn stock. Ships in 6-10 days.PICK UP IN STORE
Your local store may have stock of this item.
Available within 2 business hours
-
SHIP THIS ITEM
Temporarily Out of Stock Online
Please check back later for updated availability.
Overview
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.
Product Details
| ISBN-13: | 9780471353485 |
|---|---|
| Publisher: | Wiley |
| Publication date: | 03/21/2001 |
| Series: | Data Model Resource Book Series , #2 |
| Edition description: | Revised Edition |
| Pages: | 576 |
| Product dimensions: | 7.30(w) x 9.10(h) x 1.20(d) |
About the Author
Read an Excerpt
1. Introduction
Why Is There a Need for This Book?When organizations develop custom information systems or strive to integrate their existing systems, they spend a significant amount of time and effort developing data models. Most of the time, organizations start from scratch when developing these data models because there are not many available sources for common data models that can be reused.
A tremendous amount of time and money could be saved by using "templates" or, to coin a phrase, "Universal Data Models" providing common data structures that are applicable across many business applications and industries.
The first book, The Data Model Resource Book: Volume 1, provided some Universal Data Model templates for common subject data areas that apply generally to most businesses. The book offered data structures to model people, organizations, products, orders, shipments, invoicing, work effort management, accounting, budgeting, and human resources. This represents a significant tool for developers to use to save time and money.
Many people have asked, why not extend the concept of the first edition of The Data Model Resource Book to provide models for specific industries? While most people find the first book very useful, there is still more work in modifying the models to work for specific industries. Organizations want to have reusable data models for their own industry, such as for financial services firms, manufacturers, travel-related enterprises, health care organizations, telecommunications companies, and insurance providers.
This book can save readers even more time and money when developing data models as they can reuse concepts and specific data structures for the industry or application that applies to them. Data modeling professionals can use the data structures as a method for quality assuring their own models and determining if there is a better way to model these structures.
Each chapter will provide an overview of an industry and the type of information that is critical to running that type of business. Then the book will provide graphical data models, along with narrative text describing how to best model the information needs for the industry. Finally, chapters will also include some star schema designs to assist in developing data analysis solutions for each type of industry.
Both Volumes 1 and 2 of The Data Model Resource Book, Revised Edition, have companion electronic products (sold separately) containing the SQL code necessary to implement the models described in the books: a CD-ROM for the generic models in Volume 1; downloadable software products for each of the eight industries covered in Volume 2. Each of the industry models for the Volume 2 electronic products contains the new and modified models that are unique to that industry. These products include the SQL code, in several formats, to implement the generic and industry models from this volume. Note that the Volume 1 and Volume 2 products are sold separately; the CD-ROM provided with this book provides several free sample models for your review and evaluation. See the section "How to Use the Volume 2 Industry Electronic Products," at the end of this book to learn how to purchase and use one or more of the industry downloadable products.
To Integrate or Disintegrate? That Is the Question
A key benefit of this book is the ability to reuse its industry models, saving tremendous amounts of time by not reinventing the wheel doing analysis that has been done before. However, perhaps there is even a more substantial benefit to this book: The models in this book are designed to facilitate the building of holistic, integrated systems.
How often have you worked with non-integrated systems? Have you ever gone to the emergency room of a hospital and been asked to fill out a form with your information when you had, just recently, given that information to another ward of the hospital? Have you ever been called by a sales representative who was completely unaware of the issue for which you had just alerted the customer service department? Have you ever called up a travel organization about the travel bonus points that you had earned and then tried to find out the status of your reservation, only to find out that you need to call a separate phone number for that information? Have you ever dealt with a procurement department in an organization that does not realize (or care) that you are also customer of that organization and perhaps deserve a little more attention?
If you have experienced difficulties such as these when dealing with an organization, there is good likelihood that their systems are set up separately, with systems built for separate departments, without the benefit of weaving each system into the whole in order to facilitate a shared information systems environment across the enterprise. Of course, the enterprise often drives and compensates each department or project to be successful without looking at the whole.
The consequences of not building integrated, holistic systems are huge. If an enterprise (the term enterprise will be used for the organization for which the systems are to be designed and built) does not focus on integration, the enterprise will move toward disintegration. If the enterprise builds their systems without regard to their overall system, redundant and inaccurate information is bound to occur. Without a holistic approach, each system will most likely define and maintain the same types of data using different formats with different names and with different meanings, leading to confusion and difficulty obtaining information.
With the advent of the Internet and many other technological advances, information is becoming much more available and widespread. This information is valuable, and enterprises that know how to take care of this information and manage it will have a key competitive edge. Can you image the power of being able to see complete profiles of individuals, organizations, products, and their related transactions across the entire enterprise? Enterprises can improve their communications if information is defined and maintained consistently across the enterprise. Integrated systems can lead to more effective service, sales, and strategic analysis for any enterprise.
The data models in Volume 2, as well as the models from Volume 1, are designed to be used to help clarify and see the entire picture of an enterprise's data and how data is related across the enterprise. If the enterprise uses these models as a road map for building integrated data structures, and if the enterprise has the attitude and culture of building integrated, holistic systems, then they can yield tremendous benefits for the enterprise and the people and organizations that the enterprise affects.
Approach of This Book
The approach of this book is quite different from that of most data modeling books. This is not a how-to book on data modeling. Data modeling has been around long enough that most systems professionals know how to model data. This book goes a step beyond and offers practical, reusable data models that can save the reader many thousands of hours in systems development efforts. Industries share many of the same data structures, so why should they reinvent the wheel each time they develop an application?
This book builds on the models and data structures in The Data Resource Book, Revised Edition, Volume 1. Volume 1 provided a series of industry Universal Data Models for each phase of an enterprise's business life cycle:
The data models in the first book apply to most enterprise because the preceding processes form the essential aspects of how most business is conducted. The data models represented in the first book identify basic information needs that are applicable across industries. This book shows how the models in the first book can be applied to many industries' unique information requirements.
Rather than repeat the common models found in Volume 1 for each industry, this book provides a table at the beginning of each chapter showing the major changes and additions required to transform the generic data models from Volume 1 into industry-oriented data, constructs. Most industries use a very high percentage of the models in Volume 1 and thus the second volume would be quite voluminous if they were repeated for each industry. (The companion Industry Download products for each industry includes the SQL code for the industry-specific data models and data warehouse star schema designs).
Each chapter is roughly organized by the preceding eight major subject data areas (i.e., parties, products, orders, shipments, work efforts, invoices, accounting, and human resources) as well as additional subject data areas that are unique for that industry. Some of the subject data areas are combined into a single section if not much customization is needed. (For instance, accounting and human resources usually do not need much customization for a particular industry.) This book shows the reader how to customize each of the subject data areas for each industry. The combination of the first book's models with the suggested customizations provides industry-specific Universal Data Models that give the user an even greater jump-start to data modeling efforts...
Table of Contents
Foreword xiiiAcknowledgments 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
What People are Saying About This
"In addition to being an excellent resource for data modelers, thisbook will help managers, business analysts and architects gain ahigh level understanding of various industries and integrationchallenges facing IT professionals. Len's concepts, insights andmodels provide a valuable contribution to dataarchitecture."—Regina Pieper Enterprise Architect, SunMicrosystems
"Len Silverston has produced an enormously useful two-volumecompendium of generic (but not too generic) data models for anextensive set of typical enterprise subject areas, and for variousindustries that any data modeler will likely encounter at somepoint in his or her career. The material is clearly written, wellorganized, and goes below the obvious to some of the more perverseand difficult information requirements in an enterprise. This is aninvaluable resource for doing one's homework before diving into anymodeling session; if you can't find it here, there is certainly avery similar template that you can use for just about any situationwith which you might be faced."— William G. Smith President,William G. Smith & Associates
"In today's fast-paced e-oriented world, it is no longeracceptable to bury business constraints in hard-to-change datastructures. Data architects must comprehend complex requirementsand recast them into data architecture with vision for unforeseenfutures. Len's models provide an outstanding starting point fornovice and advanced data architects for delivering flexible datamodels. These models position an organization for the business ruleage. Their proper implementation and customization allows theorganization to externalize and manage business policies and rulesso that the business can proactively change itself. In this way,the data architecture, based on Len's models and procedures forcustomizing them, becomes by design the foundation for businesschange."—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 companyimplementing universal data models. They contain practical insightsand templates for implementing universal data models and can helpall enterprises regardless of their level of experience. Most booksaddress the needs for data models but give little in the way ofpractical advice. These books fill in that void and should beutilized by all enterprises."—Ron Powell Publisher, DM Review
"I was first introduced to The Data Model Resource Book threeyears ago when I was hired by a firm that wanted an enterprise datamodel. This company did not believe the dictum that "all companiesare basically the same;" they felt they were somehow unique. Aftera little analysis with Len Silverston's help, we found that we wereactually quite a bit the same: we had customers, accounts,employees, benefits, and all the things you'd find in anycorporation. All we had to do was adapt the product component ofLen's book and we were ready to move ahead with a great frameworkfor all of our data. A CD-ROM that accompanies the book providedscripts to build the model in Oracle very quickly. We then beganmapping all of our detailed data types to the enterprise model and,voila, we could find a place for all of those various spellings andmisspellings of Account Number.
Volume 2 of this revised edition provided even more excitingfeatures: models of industry-specific data. I began to seeinteresting patterns that permeated this volume. For example, areservation is a reservation, whether you're an airline, arestaurant, or a hotel. (We even have something similar in the oilindustry—the allocation.)
Another concept from the book that has changed my thinking andvocabulary is the word "party." I recently managed a project inwhich an employee could also function as a customer and as anon-line computer user. The team was in disagreement regarding aname for this entity; but after checking The Data Model ResourceBook, we realized that here we had a party playing three roles.
Whether your job is to jump-start a data warehouse project orborrow ideas for any subject area in your next operationaldatabase, I highly recommend The Data Model Resource Books, RevisedEdition, Volumes 1 and 2 as your bible for design."—Ted KowalskiData Architect, Equilon Enterprises LLC Author of Opening Doors: AFacilitator's Handbook
Foreword
However, when it comes to Len's most recent book, The Data Model Resource Book, Revised Edition, Volume 2, this generic model argument is not even an issue! It doesn't make any difference how you come down on the argument, and it doesn't make any difference if you consider Len's models to be generic or specific in nature. Any way you look at it, it is a lot easier and faster to start with something somebody else has already put down on paper than to have to start with a blank sheet of paper and create something from scratch yourself!
Len has made a major contribution to us in publishing the set of industry models in this book. It is clear that he has done a lot of research in preparing to write the book, and it is also clear that he has capitalized on his vast experience in implementing the universal models. He has created enterprise-wide models for eight different industries, which in various combinations and permutations may well satisfy the bulk of what is needed by any specific Enterprise in virtually any industry!
Now, let's get pragmatic. Starting with a universal data model does not absolve anyone of the responsibility of knowing his or her own enterprise intimately, at even an excruciating level of detail! Nor does it absolve anyone from the responsibility of learning how to build data models!! What you have to do is start with the universal model and then, understanding data modeling and understanding your own enterprise, make the necessary modifications to make the universal model your own.
By starting with an enterprise-wide universal model, you will save yourself one heck of a lot of work-that is, time and money-trying to create the model yourself. What's more, you're more likely to produce a higher quality model than if you started from scratch, since you can be assured that you won't overlook key components of the model. Even if you are implementing a single application--that is, you are not attempting to build an Enterprise-wide model-you are still way ahead of the game using one of Len's models. Because his models are enterprise-wide in nature, he has already anticipated other applications' semantic requirements for the entities you are embarking on implementing. If you use his more broadly defined, enterprise-wide structures, you will save yourself the time and cost of having to scrap and rebuild your application later or the untold frustrations that arise in management when the data in your application is not consistent with the data in ensuing applications.
This is a very helpful book, whether you are building Enterprise Architectures or whether you are just implementing applications . . . whether you like generic models or whether you like more specific models. If you have any idea of the profound enterprise significance of data models and the challenges of creating them from scratch, this is a book for you!
John A. Zachman
Glendale, California 2001