The data model resource book Vol. 1, A library of unversal data models for all enterprises

562 Pages·2001·2.285 MB·English

by  Silverston, Len

05_380237_c02_7.qxd 7/13/07 9:44 AM Page 68
01_380237_ffirs_7.qxd 7/12/07 3:56 PM Page i
The Data Model
Resource Book
Revised Edition
Volume 1
A Library of Universal Data Models
for All Enterprises
Len Silverston
Wiley Computer Publishing
John Wiley & Sons, Inc.
NEW YORK • CHICHESTER • WEINHEIM • BRISBANE • SINGAPORE • TORONTO
01_380237_ffirs_8.qxd 8/27/08 10:00 AM Page ii
Publisher: Robert Ipsen
Editor: Robert M. Elliott
Assistant Editor: Emilie Herman
Managing Editor: John Atkins
Associate New Media Editor:Brian Snapp
Text Design & Composition: Publishers’ Design and Production Services, Inc.
Designations used by companies to distinguish their products are often claimed as
trademarks. In all instances where John Wiley & Sons, Inc., is aware of a claim, the product
names appear in initial capital or ALLCAPITALLETTERS. Readers, however, should contact the
appropriate companies for more complete information regarding trademarks and
registration.
This book is printed on acid-free paper.
Copyright © 2001 by Len Silverston. All rights reserved.
Published by John Wiley & Sons, Inc.
Published simultaneously in Canada.
No part of this publication may be reproduced, stored in a retrieval system or transmitted
in any form or by any means, electronic, mechanical, photocopying, recording, scanning or
otherwise, except as permitted under Sections 107 or 108 of the 1976 United States
Copyright Act, without either the prior written permission of the Publisher, or
authorization through payment of the appropriate per-copy fee to the Copyright Clearance
Center, 222 Rosewood Drive, Danvers, MA 01923, [phone], fax [phone].
Requests to the Publisher for permission should be addressed to the Permissions
Department, John Wiley & Sons, Inc., 10475 Crosspoint Blvd., Indianapolis, IN 46256, (317)
572-3447, fax [phone], e-mail: permcoordinator@ wiley.com.
This publication is designed to provide accurate and authoritative information in regard to
the subject matter covered. It is sold with the understanding that the publisher is not
engaged in professional services. If professional advice or other expert assistance is
required, the services of a competent professional person should be sought.
Library of Congress Cataloging-in-Publication Data:
ISBN: 978-0-471-38023-8
Printed in the United States of America.
10 9 8
01_380237_ffirs_7.qxd 7/12/07 3:56 PM Page iii
Advance Praise for The Data Model
Resource Book, Revised Edition, Volume 1
“TheData 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 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 toogeneric) 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
01_380237_ffirs_7.qxd 7/12/07 3:56 PM Page iv
“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, DMReview
“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 Bookthree 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 productcomponent of Len’s book and we were
ready to move ahead with a great framework for all of our data. A CD-ROM (that can be
purchased separately) provides 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
02_380237_ftoc_7.qxd 7/12/07 4:01 PM Page v
Contents
Foreword xiii
Acknowledgments xv
About the Author xvii
Chapter 1 Introduction 1
Why Is There a Need for This Book? 1
Who Can Benefit from Reading This Book? 2
The Need for Universal Data Models 2
A Holistic Approach to Systems Development 3
What Is the Intent of This Book and These Models? 5
What Is New in the Second Edition of the Data Model
ResourceBook? 6
Conventions and Standards Used in This Book 8
Entities 8
Subtypes and Supertypes 9
Non-Mutually Exclusive Sets of Subtypes 11
Attributes 11
Relationships 12
Relationship Optionality 12
Relationship Cardinality 12
Foreign Key Relationships 14
Foreign Key Inheritance 15
Intersection or Association Entities to Handle Many-to-Many
Relationships 15
Exclusive Arcs 16
Recursive Relationships 17
Physical Models 17
Conventions Used for Illustration Tables 18
Conventions Used to Reference Figures 19
The Companion Electronic Product 19
Chapter 2 People and Organizations 21
Organization 22
Person 25
Person—Alternate Model 26
v
02_380237_ftoc_7.qxd 7/12/07 4:01 PM Page vi
vi Contents
Party 29
Party Roles 33
Organization Roles 35
Common Party Role Subtypes 36
Should Roles Be Defined at the Time of the Transaction? 36
Party Role Example 37
Role Types throughout This Book 37
Party Relationship 39
Party Relationship Examples 44
Party Relationship Information 46
Status Types 47
Party Contact Information 47
Postal Address Information 49
Geographic Boundaries 51
Party Contact Mechanism—Telecommunications Numbers
andElectronic Addresses 53
Party Contact Mechanism (Expanded) 54
Contact Mechanism Purpose 56
Facility versus Contact Mechanism 58
Party Communication Event 58
Communication Event Follow-Up 63
Summary 67
Chapter 3 Products 69
Product Definition 70
Product Category 71
Product Identification Codes 75
Product Features 76
Product Feature Interaction 78
Product Feature Subtypes 78
Product Feature Examples 79
Unit of Measure 80
Suppliers and Manufacturers of Products 81
Inventory Item Storage 84
Product Pricing 87
Pricing Subtypes 87
Price Component Attributes and Relationship to Product
orProduct Feature 89
Pricing Factors 89
International Pricing 91
Example of Product Pricing 91
Product Costing 93
Product to Product Associations 96
Products and Parts 100
Summary 104
Chapter 4 Ordering Products 105
Standard Order Model 106
Order and Order Items 109
02_380237_ftoc_7.qxd 7/12/07 4:01 PM Page vii
Contents vii
Order Parties and Contact Mechanisms 113
Sales Order Parties and Contact Mechanisms 113
Party Placing Order and Related Contact Mechanism 116
Party Taking Order and Related Contact Mechanism 116
Ship-to Party and Contact Mechanism 116
Bill-to Party and Contact Mechanism 117
Person Roles for Orders 119
Purchase Order Parties and Contact Mechanisms 120
Generic Order Roles and Contact Mechanisms 120
Order Adjustments 124
Order Status and Terms 127
Order Status 127
Order Terms 129
Order Item Association 129
Optional Order Models 131
Requirements 132
Requirement Roles 134
Requirement Status 134
Product Requirements 135
Order Requirement Commitments 135
Requirement Example 135
Requests 137
Request 137
Request Items 140
Quote Definition 141
Quote Roles 143
Quote 143
Quote Items 143
Quote Terms 144
Agreement Definition 145
Agreement Item 148
Agreement Terms 151
Agreement Pricing 153
Agreement to Order 155
Summary 157
Chapter 5 Shipments 159
Shipments 160
Shipment Types 161
Shipments Parties and Contact Mechanisms 162
Shipping Detail 163
Shipment Status 166
Shipment-to-Order Relationship 167
Shipment Receipts 170
Item Issuance for Outgoing Shipments 173
Shipment Documents 176
Shipment Routing 178
Shipment Vehicle 180
Summary 182
02_380237_ftoc_7.qxd 7/12/07 4:01 PM Page viii
viii Contents
Chapter 6 Work Effort 185
Work Requirement and Work Efforts 186
Work Requirement Definition 186
Requirement Types 188
Anticipated Demand 190
Work Requirement Compared to Order 190
Work Requirement Roles 191
Work Effort Generation 193
Work Effort Type and Work Effort Purpose Type 195
Work Effort Attributes 195
Fulfillment of Work Requirements 196
Work Effort and Facility 198
Work Effort Generation—Alternate Model 198
Work Effort Associations 200
Work Effort Association Definition 200
Work Effort Dependency 203
Work Efforts and Work Tasks 203
Work Effort Party Assignment 203
Work Effort Party Assignment 205
Party Skill and Skill Type 206
Work Effort Status 207
Work Effort Party Assignment 207
Work Effort Role Type 208
Work Effort Assignment Facility 209
Work Effort Time Tracking 209
Work Effort Rates 211
Work Effort Assignment Rate 214
Inventory Assignments 215
Fixed Asset Assignments 217
Fixed Asset 218
Fixed Asset Type 218
Fixed Asset Assignment and Status 219
Party Fixed Asset Assignments 220
Work Effort Type Standards 221
Work Effort Skill Standards 223
Work Effort Good Standards 223
Work Effort Fixed Asset Standard 224
Work Effort Results 224
Summary 227
Chapter 7 Invoicing 229
Invoices and Invoice Items 230
Invoice Roles 234
Billing Account 237
Invoice Specific Roles 240
Invoice Terms and Status 242
Invoice Status 242
Invoice Terms 244
Invoice and Associated Transactions 244
Billing for Shipment Items 245