The Data Model Resource Book, Vol. 1: A Library of Universal Data Models for All Enterprises

525 Pages·2001·3.27 MB·English

by  Len Silverston

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 • CH1CHESTER • WEINHEIM • BRISBANE • SINGAPORE • TORONTO
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
Resource Book? 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 CD-ROM 19
Chapter 2 People and Organizations 21
Organization 22
Person 25
Person—Alternate Model 26
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
and Electronic 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
or Product 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
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
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
203
Work Effort Dependency
203
Work Efforts and Work Tasks
203
Work Effort Party Assignment
205
Work Effort Party Assignment
206
Party Skill and Skill Type
207
Work Effort Status
207
Work Effort Party Assignment
208
Work Effort Role Type
209
Work Effort Assignment Facility
209
Work Effort Time Tracking
211
Work Effort Rates
214
Work Effort Assignment Rate
215
Inventory Assignments
217
Fixed Asset Assignments
218
Fixed Asset
218
Fixed Asset Type
219
Fixed Asset Assignment and Status
220
Party Fixed Asset Assignments
221
Work Effort Type Standards
223
Work Effort Skill Standards
223
Work Effort Good Standards
224
Work Effort Fixed Asset Standard
224
Work Effort Results
227
Summary
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
Contents IX
Billing for Work Efforts and Time Entries 247
Billing for Order Items 249
Payments 250
Financial Accounts, Deposits, and Withdrawals 254
Summary 258
Chapter 8 Accounting and Budgeting 259
Chart of Accounts for Internal Organizations 260
General Ledger Accounts and Types 260
Organization GL Account 262
Accounting Period 263
Accounting Transactions Definition 265
Business Transactions versus Accounting Transactions? 267
Accounting Transaction 267
Accounting Transactions and Their Related Parties 268
Accounting Transaction Details 270
Transaction Detail 270
Relationships between Accounting Transaction Details 273
Account Balances and Transactions 2H
Subsidiary Accounts 216
Asset Depreciation 278
Budget Definition 280
Budget 280
Budget Item 282
Budget Status 282
Budget Revision 283
Budget Review 286
Budget Scenarios 288
Usage and Sources of Budgeted Amounts 289
Commitments against Budgets 292
Payments against Budgets 293
Budget Relationship to General Ledger 295
Budgeted Items versus General Ledger Accounts 298
Summary 298
Chapter 9 Human Resources 299
Standard Human Resources Model 300
Employment 302
Position Definition 303
Position 305
Position Authorization 305
Position Type 306
Position Responsibilities 306
Position Type Definition 307
Organization 307
Position Fulfillment and Tracking 309
Position Fulfillment 310
Position Status Type 311
Hiring Organization 311
Other Considerations 311
Contents
Position Reporting Relationships 312
Position Reporting Structure 312
Salary Determination and Pay History 314
Position Type Rate 316
Pay Grade and Salary Step 317
Pay History and Actual Salary 318
Benefits Definition and Tracking 319
Employment 319
Party Benefit 320
Period Type 321
Benefit Type 321
Payroll Information 322
Employee 322
Payment Method Type 324
Payroll Preference 324
Paycheck 325
Deduction and Deduction Type 326
Employment Application 327
Employee Skills and Qualifications 328
Employee Performance 328
Employee Termination 333
Summary 333
Chapter 10 Creating the Data Warehouse Data Model from the
Enterprise Data Model 337
The Data Warehouse Architecture 337
The Enterprise Data Model 338
The Data Warehouse Design 338
The Departmental Data Warehouse Design or Data Mart 338
An Architected Data Warehouse Environment 339
The Enterprise Data Model 340
Transformation Requirements 340
Process Models 342
High-Level and Logical Data Models 342
Making the Transformation 343
Removing Operational Data 345
Adding an Element of Time to the Warehouse Key 346
Adding Derived Data 346
Creating Relationship Artifacts 347
Changing Granularity of Data 350
Merging Tables 351
Creation of Arrays of Data 352
Organizing Data According to Its Stability 354
Summary 355
Chapter 11 A Sample Data Warehouse Data Model 357
Transformation to Customer Invoice 358
Removing Operational Data 358
Adding an Element of Time 359
Adding Derived Data 360
Contents xi
Creating Relationship Artifacts 360
Accommodating Levels of Granularity 362
Merging Tables 363
Separation Based on Stability 363
Other Considerations 363
The Sample Data Warehouse Data Model 364
Common Reference Tables 365
Summary 365
Chapter 12 Star Schema Designs for Sales Analysis 369
Sales Analysis Data Mart 370
Customer Sales Facts 372
Customer Dimension 373
Customer Demographics Dimensions 373
Sales Reps Dimension 374
Internal Organizations Dimension 375
Addresses Dimension 375
Product Dimension 376
Time Dimension 376
Transaction-Oriented Sales Data Mart 377
Variations on the Sales Analysis Data Mart 380
Variation 1: Sales Rep Performance Data Mart 380
Customer Rep Sales Fact 381
Time Dimension 382
382
Variation 2: Product Analysis Data Mart
383
Product Sales Facts
384
Geographic Boundaries Dimension
384
Summary
Chapter 13 Star Schema Designs for Human Resources 387
Human Resources Star Schema 388
Human Resource Fact Table 389
Organizations Dimension 390
Position Types Dimension 391
Genders Dimension 392
Length of Services Dimension 392
Statuses Dimension 392
Pay Grades Dimension 392
EEOC Types Dimension 393
Time_By_Month Dimension 393
Human Resources Star Schema at a Higher Level of
Granularization 393
Summary 395
Chapter 14 Additional Star Schema Designs 397
Inventory Management Analysis 398
Purchase Order Analysis 399
Shipment Analysis 401
Work Effort Analysis 402
xii Contents
Financial Analysis 404
Summary 405
Chapter 15 Implementing the Universal Data Models 407
The Enterprise Data Model—An Integrated Business View
of the Enterprise's Information 408
Customizing the Universal Data Models 410
Degrees of Customization 410
Customizing the Models for Unique Business Terminology 411
Example of Changing the Terms for the Specific Enterprise 412
Additional Information Requirements Needed for the
Enterprise 416
How the Universal Data Models and Enterprise Data Model
Solve Business Problems 418
Using a Data Model for a Particular Application 419
Understanding Business Processes 420
Building the Logical Data Model 422
Physical Database Design 425
Basic Database Design Principles 425
Creating a Physical Database Design 427
Physical Database Design Examples 428
Review of the Party Role and Relationship Model 428
Party Roles and Relationships Physical Design, Option 1 430
Example Data for Physical Database Design, Option 1 433
Party Roles and Relationships Physical Design, Option 2 437
Party Roles and Relationships Generic Design, Option 3 439
Using the Data Warehouse Models 444
Summary 446
For More Information 447
Appendix A Logical Data Model Entities and Attributes 449
Appendix B Data Warehouse Data Model Tables and Columns 503
Appendix C Star Schema Design Tables and Columns 509
How to Use the CD-ROM Product 519
Other Reusable Data Model and Data Warehouse Design Resources 521
Index 523
Foreword
When I first became involved in data modeling in the mid-1970s, I was taught a
set of diagramming conventions, the rules of normalization, and a few princi-
ples of good design. It did not take me long to discover that my education had
covered only the easy part. The real challenge, as any experienced modeler
knows, lies in understanding business requirements and choosing an appropri-
ate set of concepts and structures to support them. The traditional advice to
"ask which things the enterprise needs to keep information about and how they
are related" is a gross over-simplification of the often very difficult process of
identifying entities and relationships.
Research in the last few years has supported what practitioners have known
for a long time: rather than modeling from first principles, experienced data
modelers re-use and adapt models and parts of models from their previous
work. In fact, their "experience" may well reside more in their personal library
of models—typically remembered rather than documented—than in greater
facility with the basic techniques. The use of pre-existing templates also
changes the nature of the dialog between the business experts and modelers:
modelers will seek to discover which model or models from their repertoire
may be appropriate to the situation, then to check the detail of those models.
This is a far more proactive role for modelers than that traditionally described,
and recognizes that both parties can contribute ideas and content to the final
model.
Of course, it takes time and exposure to a wide variety of business require-
ments for an individual to build up anything approaching a comprehensive
library of models. Only specialist data modelers are likely to have this opportu-
nity, and the reality is that much data modeling is performed by non-specialists.
The obvious step forward from this rather haphazard individual approach is
for experienced modelers to develop and publish models for the most com-
monly encountered business requirements, so that solutions can be shared,
reviewed and improved. Almost every commercial enterprise needs to keep
data about customers, about staff, about sales. And almost every data modeler
has spent time wrestling with these common—but by no means simple—situa-
xiii