Wiley.com
Print this page Share

The Data Model Resource Book, Volume 1: A Library of Universal Data Models for All Enterprises, Revised Edition

ISBN: 978-0-471-38023-8
560 pages
March 2001
The Data Model Resource Book, Volume 1: A Library of Universal Data Models for All Enterprises, Revised Edition (0471380237) cover image

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.
Updating the data models from the First Edition CD-ROM, this resource allows database developers to quickly load a core set of data models and customize them to support a wide range of business functions.
See More

Table of Contents

Foreword.

Acknowledgments.

About the Author.

Introduction.

People and Organizations.

Products.

Ordering Products.

Shipments.

Work Effort.

Invoicing.

Accounting and Budgeting.

Human Resources.

Creating the Data Warehouse Data Model from the Enterprise Data Model.

A Sample Data Warehouse Data Model.

Star Schema Designs for Sales Analysis.

Star Schema Designs for Human Resources.

Additional Star Schema Designs.

Implementing the Universal Data Models.

Appendix A: Logical Data Model Entities and Attributes.

Appendix B: Data Warehouse Data Model Tables and Columns.

Appendix C: Star Schema Design Tables and Columns.

How to Use the CD-ROM Product.

Other Reusable Data Model and Data Warehouse Design Resources.

Index.
See More

Author Information

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.
See More

Reviews

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

"The Data Model Resource Book, Revised Edition, Volume 1 is the best book I?ve seen on data architecture. It does not merely address the top levels of a data architecture (Zachman Framework row one or two); it provides both common and industry-specific logical models as well as data designs that may be customized to meet your requirements. The end result is a is a rich framework whose models span the higher and lower levels of a data architecture, including high-level models, logical models, warehouse designs, star schemas, and SQL scripts. You can use the data models, designs, and scripts as templates or starting points for your own modeling, an introduction to subject areas you might not be familiar with, a reference to validate your existing models, and a help to building an enterprise data architecture. The book provides techniques to transform models from one level to another, as well as tips and techniques for getting the appropriate levels of abstraction in the models. Instance tables (sample data) help bring the models to life. I have customized and used the models from the first edition on many projects in the last two years?it is an invaluable resource to me."
--Van Scott, President, Sonata Consulting, Inc.

"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

"Businesses across the world are demanding quality systems that are built faster by IT shops. This book provides a foundation of patterns for data modelers to expand upon and can cut days, if not weeks, off a project schedule. I have found The Data Model Resource Book, Revised Edition, Volume 1 valuable as a resource for my modeling efforts at L.L. Bean, Inc. and feel it is an essential component in any modelers toolkit."
--Susan T. Oliver, Enterprise Data Architect, L.L. Bean, Inc.

"I was first introduced to The Data Model Resource Book three years ago when I was hired by a firm who 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, Equilon Enterprises LLC, Author of Opening Doors: A Facilitator's Handbook

See More

Errata

Do you think you've discovered an error in this book? Please check the list of errata below to see if we've already addressed the error. If not, please submit the error via our Errata Form. We will attempt to verify your error; if you're right, we will post a correction below.

ChapterPageDetailsDatePrint Run
10 2nd to last paragraph, last sentence
Change from: For example, each INFORMATION ORGANIZATION
Change to: For example, each INFORMAL ORGANIZATION
56 2nd paragraph with the Contact Mechanism Purpose section, first sentence
Change from: ... each PARTY CONTACT MECHANISM must have
Change to: ... each PARTY CONTACT MECHANISM may have
86 1st paragraph, first sentence
Change from: The ITEM VARIANCE entity ...
Change to: The INVENTORY ITEM VARIANCE entity ...
110 2nd paragraph, last sentence
Change from: ORDER ITEM that is for a product feature that is ordered for an ORDER ITEM that is for a product feature.
Change to: ORDER ITEM that is for a product to an ORDER ITEM that is for a product feature, which is ordered for the former.
134 First paragraph
Change from: This entity would maintain which SALES ORDER ITEMs fulfilled which REQUIREMENT ITEMs ...
It would also maintain which PURCHASE ORDER ITEMs are fulfilled by the REQUIREMENT ITEMs Change to: This entity would maintain which SALES ORDER ITEMs fulfilled which REQUIREMENT ...
It would also maintain which PURCHASE ORDER ITEMs are fulfilled by the REQUIREMEN
145 Third paragraph with the Agreement Definition section, first sentence
Change from: ADDENDUMs modify the agreement
Change to: ADDENDUMs (see Figure 4.13) modify the agreement
171 2nd paragraph, after 4th sentence
Change to: After the 4th sentence that ends with "that stores the details of the receipt.", insert the following sentence:
However, the SHIPMENT PACKAGE may exist without having a SHIPMENT RECEIPT, for instance on outgoing shipments when the organization assembles something into a package. So there is only a mandatory relationship from SHIOPMENT RECEIPT to SHIPMENT PACKAGE.
243 Model 7.4 Invoice Status and Terms
In diagram 7.4 in INVOICE ITEM entity, attribute "unit price" should be changed to "amount"
246 Model 7.5 Billing for Shipment items
Billing for Shipment items, Figure 7.5 Relationship names from and to SHIPMENT ITEM BILLING should be changed.
Change the relationship names:
- from SHIPMENT ITEM to SHIPMENT ITEM BILLING to "billed via"
- from SHIPMENT ITEM BILLING to SHIPMENT ITEM to "of"
SHIPMENT ITEM BILLING to INVOICE ITEM to "for"
INVOICE ITEM to SHIPMENT ITEM BILLING to "the billing for"
250 Model 7.7 Billing for order items
Billing for order items, Figure 7.7 Relationship names from and to ORDER ITEM BILLING should be changed.
Changed the relationship names:
- from ORDER ITEM to ORDER ITEM BILLING to "billed via"
- from ORDER ITEM BILLING to ORDER ITEM to "of"
ORDER ITEM BILLING to INVOICE ITEM to "for"
INVOICE ITEM to ORDER ITEM BILLING to "the billing for"
271 Accounting transaction details diagram, figure 8.3a--Relationship should be mandatory
Relationship from ACCOUNTING TRANSACTION to the TRANSACTION DETAIL shows a dotted line and therefore that it is optional.
Relationship from ACCOUNTING TRANSACTION to the TRANSACTION DETAIL should be mandatory in both directions and therefore it should be a solid line all the way across.
271 Accounting transaction details diagram, Figure 8.3a--Recursive relationship error
The recursive relationship around the TRANSACTION DETAIL is shown incorrectly as a solid line.
The recursive relationship around the TRANSACTION DETAIL should be optional and therefore be a dotted line throughout the line.
271 Accounting transaction details diagram, Figure 8.3a--Relationship name missing
The relationship from ORGANIZATION GL ACCOUNT to TRANSACTION DETAIL does not have a relationship name.
The relationship name from ORGANIZATION GL ACCOUNT to TRANSACTION DETAIL should be "associated with".
277 Accounting transaction details diagram, figure 8.3b--Relationship should be mandatory
Relationship from ACCOUNTING TRANSACTION to the TRANSACTION DETAIL shows a dotted line and therefore that it is optional.
Relationship from ACCOUNTING TRANSACTION to the TRANSACTION DETAIL should be mandatory in both directions and therefore that it should be a solid line all the way across.
277 Accounting transaction details diagram, Figure 8.3b--Recursive relationship error
The recursive relationship around the TRANSACTION DETAIL is shown incorrectly as a solid line.
The recursive relationship around the TRANSACTION DETAIL should be optional and therefore be a dotted line throughout the line.
297 Overall Accounting and budgeting model, Figure 8.12--Relationship should be mandatory
Relationship from ACCOUNTING TRANSACTION to the TRANSACTION DETAIL shows a dotted line and therefore that it is optional.
Relationship from ACCOUNTING TRANSACTION to the TRANSACTION DETAIL should be mandatory in both directions and therefore that it should be a solid line all the way across.
302 Error in Figure 9.2
Relationships from EMPLOYMENT are related to PERSON ROLE and ORGANIZATION ROLE
Relationships from EMPLOYMENT should be to EMPLOYEE and INTERNAL ORGANIZATION
444 Error in referece to table
In the 2nd paragraph, first sentence, Table 15.13 should be Table 15.12
485 Error in PRODUCT CATEGORY ROLLUP attribute names
PARTY TYPE ID MADE UP OF
PARTY TYPE ID PART OF
Should be:
PRODUCT CATEGORY ID MADE UP OF
PRODUCT CATEGORY ID PART OF
Appendix A 485, 486 Errata in text - Pages 485/486 (Appendix A)
Currently Reads:
PRODUCT INCOMPATABILITY

Should Read:
PRODUCT INCOMPATIBILITY
26-Apr-16
Appendix A Logical Data Model Entities & Attributes, BUDGET ITEM entity, BUDGET_TYPE_ID
The attribute BUDGET_TYPE_ID should not be part of the BUDGET ITEM entity
Appendix A Logical Data Model Entities & Attributes, ORDER ITEM entity, BUDGET_TYPE_ID
This attribute should not be in the entity ORDER ITEM. Delete the last line in the ORDER ITEM that shows BUDGET_TYPE_ID as an attribute.
Appendix A Logical Data Model Entities & Attributes, BUDGET SCENARIO IMPACT entity, BUDGET_TYPE_ID
This attribute should not be in the entity BUDGET SCENARIO IMPACT. Delete the line in the BUDGET SCENARIO IMPACT entity that shows BUDGET_TYPE_ID as an attribute.
Appendix A Logical Data Model Entities and Attributes, BUDGET REVISION IMPACT entity, BUDGET_TYPE_ID
This attribute should not be in the entity BUDGET REVISION IMPACT. Delete the line in the POSITION entity that shows BUDGET_TYPE_ID as an attribute.
Appendix A Logical Data Model Entities & Attributes, REQUIREMENT BUDGET ALLOCATION entity, BUDGET_TYPE_ID
This attribute, BUDGET_TYPE_ID, should not be in the entity REQUIREMENT BUDGET ALLOCATION. Delete this first attribute and its associated values of "yes""yes" and "id", and move the BUDGET_ITEM up to be the first attribute in this entity.
Appendix A Logical Data Model Entities & Attributes, BUDGET entity, BUDGET_TYPE_ID
The first column for BUDGET_TYPE_ID should be "no" since it is not part of the primary key
Appendix A Entity FACILITY has missing attribute
Entity FACILITY is missing the PART OF FACILITY ID attribute which does occur in Volume 2 . Need to add PART OF FACILITY ID attribute as part of the FACILITY just after the FACILITY row and the column values are "no" "yes" "ID"
Appendix A Logical Data Model Entities and Attributes
FINANCIAL ACCOUNT should show another attribute (another row) named FINANCIAL ACCOUNT TYPE ID with values of "no" "yes" "ID"
Appendix A Logical Data Model Entities and Attributes
The first attribute in FINANCIAL ACCOUNT TYPE should be FINANCIAL ACCOUNT TYPE ID instead of FINANCIAL ACCOUNT ID
Appendix A 464 Logical Data Model Entities and Attributes
The word FINANCIAL ACCOUNT TRANSACTION is repeated for the field ENTRY DATE. This is not correct. The entity name should only be printed once for the first attribute.
Appendix A 464 Logical Data Model Entities and Attributes
Only the attribute FINANCIAL_ACCOUNT_TRANSACTION_ID should be marked "yes" for the PK column. The field FINANCIAL ACCOUNT ID should have "no" in the first column indicating that it is not a primary key. Also the field FINANCIAL_ACCOUNT_TRANSACTIONID should be FINANCIAL_ACCOUNT_TRANS_ID instead.
Volume 1 CD-ROM CD Error with "description" field
REASON should have the "description" field as an attribute and not part of the primary key.
Volume 1 CD-ROM CD Error with key for ORDER_ITEM_ROLE
ORDER_ITEM_ROLE does not contain the order_item_seq_id as part of its key. ORDER_ITEM_ROLE should contain the order_item_seq_id as part of its key.
Volume 1 CD-ROM CD Error: Missing attributes for AGREEMENT
AGREEMENT table should have a "description" and "from date" attributes. Add "description" and "from date" attributes to AGREEMENT.
Volume 1 CD-ROM CD Error with relationship from ORDER ROLE to ORDER ITEM ROLE
Relationship from ORDER ROLE to ORDER ITEM ROLE is incorrect and should be moved. Was changed to a relationship from ORDER ITEM to ORDER ITEM ROLE.
Volume 1 CD-ROM CD Error with relationship with INTERNAL ACCTG TRANS
INTERNAL ACCTG TRANS should be related to INTERNAL ORGANIZATION and not PARTY ROLE. INTERNAL ACCTG TRANS should be related to INTERNAL ORGANIZATION and not PARTY ROLE.
See More

Notes

Visit these related products by Len Silverston
For more information about this and the author's other data model products, visit this site.
See More

Buy Both and Save 25%!

+

The Data Model Resource Book, Volume 1: A Library of Universal Data Models for All Enterprises, Revised Edition (US $75.00)

-and- Professional Microsoft SQL Server 2012 Administration (US $49.99)

Total List Price: US $124.99
Discounted Price: US $93.74 (Save: US $31.25)

Buy Both
Cannot be combined with any other offers. Learn more.
Back to Top