The Data Model Resource Book, Vol. 1 A Library of Universal Data Models for All Enterprises (Len Silverston) (Z-Library)

📄 File Format: PDF

💾 File Size: 3.3 MB

📖 Read Online ⬇️ Download

Registered users can read the full content for free

Register as a Gaohf Library member to read the complete e-book online for free and enjoy a better reading experience.

📄 Page 1

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

📄 Page 2

Contents Foreword Acknowledgments About the Author Chapter 1 Introduction Why Is There a Need for This Book? Who Can Benefit from Reading This Book? The Need for Universal Data Models A Holistic Approach to Systems Development What Is the Intent of This Book and These Models? What Is New in the Second Edition of the Data Model Resource Book? Conventions and Standards Used in This Book Entities Subtypes and Supertypes Non-Mutually Exclusive Sets of Subtypes Attributes Relationships Relationship Optionality Relationship Cardinality Foreign Key Relationships Foreign Key Inheritance Intersection or Association Entities to Handle Many-to-Many Relationships Exclusive Arcs Recursive Relationships Physical Models Conventions Used for Illustration Tables Conventions Used to Reference Figures The Companion CD-ROM Chapter 2 People and Organizations Organization Person Person—Alternate Model xiii XV xvii 1 1 2 2 3 5 6 8 8 9 11 11 12 12 12 14 15 15 16 17 17 18 19 19 21 22 25 26

📄 Page 3

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 Standard Order Model Order and Order Items 105 106 109

📄 Page 4

Order Parties and Contact Mechanisms Sales Order Parties and Contact Mechanisms Party Placing Order and Related Contact Mechanism Party Taking Order and Related Contact Mechanism Ship-to Party and Contact Mechanism Bill-to Party and Contact Mechanism Person Roles for Orders Purchase Order Parties and Contact Mechanisms Generic Order Roles and Contact Mechanisms Order Adjustments Order Status and Terms Order Status Order Terms Order Item Association Optional Order Models Requirements Requirement Roles Requirement Status Product Requirements Order Requirement Commitments Requirement Example Requests Request Request Items Quote Definition Quote Roles Quote Quote Items Quote Terms Agreement Definition Agreement Item Agreement Terms Agreement Pricing Agreement to Order Summary Chapter 5 Shipments Shipments Shipment Types Shipments Parties and Contact Mechanisms Shipping Detail Shipment Status Shipment-to-Order Relationship Shipment Receipts Item Issuance for Outgoing Shipments Shipment Documents Shipment Routing Shipment Vehicle Summary Contents vii 113 113 116 116 116 117 119 120 120 124 127 127 129 129 131 132 134 134 135 135 135 137 137 140 141 143 143 143 144 145 148 151 153 155 157 159 160 161 162 163 166 167 170 173 176 178 180 182

📄 Page 5

vi i i Contents Chapter 6 Work Effort Work Requirement and Work Efforts Work Requirement Definition Requirement Types Anticipated Demand Work Requirement Compared to Order Work Requirement Roles Work Effort Generation Work Effort Type and Work Effort Purpose Type Work Effort Attributes Fulfillment of Work Requirements Work Effort and Facility Work Effort Generation—Alternate Model Work Effort Associations Work Effort Association Definition Work Effort Dependency Work Efforts and Work Tasks Work Effort Party Assignment Work Effort Party Assignment Party Skill and Skill Type Work Effort Status Work Effort Party Assignment Work Effort Role Type Work Effort Assignment Facility Work Effort Time Tracking Work Effort Rates Work Effort Assignment Rate Inventory Assignments Fixed Asset Assignments Fixed Asset Fixed Asset Type Fixed Asset Assignment and Status Party Fixed Asset Assignments Work Effort Type Standards Work Effort Skill Standards Work Effort Good Standards Work Effort Fixed Asset Standard Work Effort Results Summary Chapter 7 Invoicing Invoices and Invoice Items Invoice Roles Billing Account Invoice Specific Roles Invoice Terms and Status Invoice Status Invoice Terms -Invoice and Associated Transactions Billing for Shipment Items 185 186 186 188 190 190 191 193 195 195 196 198 198 200 200 203 203 203 205 206 207 207 208 209 209 211 214 215 217 218 218 219 220 221 223 223 224 224 227 229 230 234 237 240 242 242 244 244 245

📄 Page 6

Billing for Work Efforts and Time Entries Billing for Order Items Payments Financial Accounts, Deposits, and Withdrawals Summary Chapter 8 Accounting and Budgeting Chart of Accounts for Internal Organizations General Ledger Accounts and Types Organization GL Account Accounting Period Accounting Transactions Definition Business Transactions versus Accounting Transactions? Accounting Transaction Accounting Transactions and Their Related Parties Accounting Transaction Details Transaction Detail Relationships between Accounting Transaction Details Account Balances and Transactions Subsidiary Accounts Asset Depreciation Budget Definition Budget Budget Item Budget Status Budget Revision Budget Review Budget Scenarios Usage and Sources of Budgeted Amounts Commitments against Budgets Payments against Budgets Budget Relationship to General Ledger Budgeted Items versus General Ledger Accounts Summary Chapter 9 Human Resources Standard Human Resources Model Employment Position Definition Position Position Authorization Position Type Position Responsibilities Position Type Definition Organization Position Fulfillment and Tracking Position Fulfillment Position Status Type Hiring Organization Other Considerations Contents IX 247 249 250 254 258 259 260 260 262 263 265 267 267 268 270 270 273 2H 216 278 280 280 282 282 283 286 288 289 292 293 295 298 298 299 300 302 303 305 305 306 306 307 307 309 310 311 311 311

📄 Page 7

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

📄 Page 8

Contents xi Creating Relationship Artifacts Accommodating Levels of Granularity Merging Tables Separation Based on Stability Other Considerations The Sample Data Warehouse Data Model Common Reference Tables Summary 360 362 363 363 363 364 365 365 Chapter 12 Star Schema Designs for Sales Analysis Sales Analysis Data Mart Customer Sales Facts Customer Dimension Customer Demographics Dimensions Sales Reps Dimension Internal Organizations Dimension Addresses Dimension Product Dimension Time Dimension Transaction-Oriented Sales Data Mart Variations on the Sales Analysis Data Mart Variation 1: Sales Rep Performance Data Mart Customer Rep Sales Fact Time Dimension Variation 2: Product Analysis Data Mart Product Sales Facts Geographic Boundaries Dimension Summary Chapter 13 Star Schema Designs for Human Resources Human Resources Star Schema Human Resource Fact Table Organizations Dimension Position Types Dimension Genders Dimension Length of Services Dimension Statuses Dimension Pay Grades Dimension EEOC Types Dimension Time_By_Month Dimension Human Resources Star Schema at a Higher Level of Granularization Summary Chapter 14 Additional Star Schema Designs Inventory Management Analysis Purchase Order Analysis Shipment Analysis Work Effort Analysis 369 370 372 373 373 374 375 375 376 376 377 380 380 381 382 382 383 384 384 387 388 389 390 391 392 392 392 392 393 393 393 395 397 398 399 401 402

📄 Page 9

xi i 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

📄 Page 10

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

📄 Page 11

xiv Foreword tions, painfully aware that he or she is re-inventing the wheel, but without any confidence that any particular modeler has done a better job. Such additions to data modeling's "body of knowledge" have been a long time coming. Books, papers, and educational material have continued to focus on the foundations of data modeling: modeling paradigms, diagramming conven- tions, and normalization. These are important topics, to be sure, but the absence of more developed material lends credence to the argument that data modeling does not deserve the status of a fully-fledged discipline. Perhaps the reason for the gap in the literature is that the individuals best placed to recognize common situations and to develop models for them are data modeling practitioners—more particularly consultants who have had the opportunity to see a range of different business requirements. The models that they have developed over the years are a valuable professional resource, more profitably deployed on consulting assignments than as material for general pub- lication. It also takes some courage to present one's own solutions for scrutiny by peers, all of whom will turn naturally to the problems for which they have personally developed the most elegant solutions! I am therefore delighted that Len Silverston has chosen to publish a second and substantially expanded edition of The Data Modeling Resource Book. The first edition was essential reading for anyone charged with developing data models for business information systems, and was particularly notable for including contributions by specialists in particular data modeling domains. The second edition retains this feature, covers new business areas, and updates the original material. Len's willingness to continue to improve the material gives me hope that the core models will acquire a deserved status as standard start- ing points. The second edition of The Data Modeling Resource Book is an excellent answer to the question "what is the second data modeling book I should pur- chase, once I've learned the basics?"—and every practitioner of data modeling should own at least two books on the subject! Graeme Simsion 1 January 2001

📄 Page 12

Introduction If you see can see more of the whole, you are moving closer towards the truth. Why Is There a Need for This Book? On many data modeling consulting engagements, clients have asked the same question: "Where can we find a book showing a standard way to model this structure? Surely, we are not the first company to model company and address information." Many organizations develop their data models or data warehouse designs with very few outside reference materials. A large cost is associated with either hiring experienced consultants or using internal staff to develop this critical component of the system design. Often there is no objective reference material that the company can use to validate its data models or data warehouse designs or to seek alternate options for database structures. Based on numerous experiences of using template or "universal data mod- els" and customizing them for various enterprises, we have concluded that usu- ally more than 50 percent of the data model (corporate or logical) consists of common constructs that are applicable to most organizations, another 25 per- cent of the model is industry specific (these models are covered in The Data

📄 Page 13

Chapter 1 * Model Resource Book, Volume 2), and, on average, about 25 percent of the enterprise's data model is specific to that organization. This means that most data modeling efforts are recreating data modeling constructs that have already been created many times before in other organizations. With this in mind, doesn't it make sense to have a source to use to get a head start on your data model so that you are not "reinventing the wheel" each time a company develops a new system? Organizations can save time and money by leveraging the use of common or universal database structures. Even if a com- pany has data models from its previous systems development efforts, it is very helpful to be able to check the designs against an unbiased source in order to evaluate alternative options. Although a large number of publications describe how to model data, very few compilations of data model examples exist in published form. This book provides both a starting point and a source for validating data models. It can help data modelers minimize design costs and develop more effective and inte- grated database designs. Who Can Benefit from Reading This Book? This book can assist many different systems development professionals: data administrators, data modelers, data analysts, database designers, data ware- house administrators, data warehouse designers, data stewards, corporate data integrators, or anyone who needs to analyze or integrate data structures. Sys- tems professionals can use the database constructs contained in this book to increase their productivity and provide a checkpoint for quality designs. The Need for Universal Data Models Data modeling first gained recognition in Dr. Peter Chen's 1976 article, "Entity- Relationship Modeling," which illustrated his newfound approach. Since then data modeling has become the standard approach used to design databases. By properly modeling an organization's data, the database designer can eliminate data redundancies, which are a key source of inaccurate information and inef- fective systems. Currently, data modeling is a well-known and accepted method for designing effective databases. Therefore, there is a great need to provide standard tem- plates to enterprises (the term "enterprise" is used to describe the organiza- tions for whom the models and systems are being developed) so that they can refine and customize their data models instead of starting from scratch. Although many standards exist for data modeling, there is a great need to take data modeling to the next step: providing accessibility to libraries of com-

📄 Page 14

Introduction mon data model examples in a convenient format. Many different organizations and industries should be able to use these libraries of data models. Such uni- versal data models can help save tremendous amounts of time and money spent in the systems development process. A Holistic Approach to Systems Development One of the greatest challenges to building effective systems is integration. Sys- tems are often built separately to meet particular needs at different times within each enterprise. Enterprises need to build many systems: contact man- agement systems, sales order systems, project management systems, account- ing systems, budgeting systems, purchase order systems, and human resources systems, to name a few. When systems are built separately, separate pools of information are created for each system. Many of these systems will use common information about organizations, people, geographic locations, or products. This means that each separate system will build and use its own source of information. A huge prob- lem with this approach is that it is almost impossible to maintain accurate, up- to-date information because the same type of information is stored redundantly across many systems. In large organizations, it is not uncommon to see infor- mation about customers, employees, organizations, products, and locations stored in dozens of separate systems. How is it possible to know which source of information is the most current or most accurate? Another disadvantage of building separate systems with non-integrated data structures is that the enterprise (the organization for which the models and sys- tems are being designed) does not have the benefit of viewing integrated infor- mation. Being able to see a complete profile for a person, organization, product, or inventory item is an enormous benefit. Imagine systems that are built so that each part of an organization knows what the other part is doing, where the cus- tomer service, sales, purchasing, and accounting departments of an organiza- tion have integrated information about the people, organizations, and products of the enterprise. This integration can make a big different in the service, sales, and performance of an enterprise. Another way to approach systems development is from a perspective that an enterprise's systems are connected and, in fact, may be viewed as one inter- connected system. From this perspective, there are tremendous benefits to building an enterprise-wide framework so that systems can work together more effectively. Part of this framework should include a corporate data model (i.e., an enterprise data model) that can assist the enterprise in maintaining one of its most valued assets: information. Because each system or application may use

📄 Page 15

Chapter 1 similar information about people, organizations, products, and geographic lo- cations, a shared information architecture can be invaluable. The IS (information systems) industry has recognized the need for integrated designs, prompting the many corporate data modeling and corporate data ware- house modeling efforts. Unfortunately, the IS track record for building and imple- menting corporate data models has been very poor. Enterprises have realized that it takes a tremendous amount of time and resources to build these models. Enter CASE (Computer-Aided Systems Engineering) tools. These tools claimed tremendous productivity and time savings when used for corporate- wide modeling efforts. While these tools help document the models, unfortu- nately they do not reduce the time needed to develop good corporate models. Many enterprises have stopped building corporate data models because of their time constraints. They are looking at the track record of corporate data modeling and CASE efforts and choosing other alternatives. Enter data warehousing. Finally, here is an approach to provide executives with the management information they need, without all the time and expense of corporate data modeling. Enterprises are now extracting the various pieces of information they need directly from their operational systems in order to build decision support systems. The only problem with this approach is that the same problem exists! First of all, the information in the data warehouse may be extracted from several dif- ferent, inconsistent sources. If there are multiple places where customer infor- mation is being held, which system represents the most accurate source of information? According to data warehousing principles, the transformation routines are responsible for consolidating and cleansing the data. If different departments have different needs for various pieces of data, then each department may build its own extracts from the operational systems. One department may transform the information using one algorithm; a different department may use another algorithm. For example, if two departments are extracting sales analysis infor- mation, one department may use the order entry system as its source and another department may use the invoicing system as its source. A high-level manager may view information from both data warehouses and see inconsis- tent results, thus questioning the credibility of all the information. This type of scenario actually compounds the initial problem of many data sources by cre- ating even more slices of data. This is not to say that data warehousing is the wrong approach. It is an inge- nious approach that can be used extremely effectively not only to create deci- sion support systems but also to build a migration path to an integrated environment. The data warehouse transformation process helps to identify where there are data inconsistencies and data redundancies in the operational environment. It is imperative, though, to use this information to migrate to more integrated data structures.

📄 Page 16

Introduction The answer is still to build integrated data structures in order to provide good, accurate information. The only effective way to do this is to understand how the data within an enterprise and the relationships fit together and to be able to see the data in a holistic integrated manner. It is necessary to under- stand the nature of the data in order to build effective systems. Instead of say- ing that, corporate data modeling, or CASE. is the wrong, approach because it just takes too long, the IS community needs to find a way to make it work effec- tively. By building common, reusable data structures, the IS community can produce quicker results and move toward integrated structures in both the transaction processing and data warehouse environments. What Is the Intent of This Book and These Models? Most data modeling books focus on the techniques and methodologies behind data modeling. The approach behind this book is dramatically different. This book assumes that the reader knows how to model data. Data modeling has been around long enough that most information systems professionals are familiar with this concept and will be able to understand this book. Therefore, this book makes no efforts to teach data modeling principles, except by exam- ple. Data modelers can use this book, and their previous experience, to build on and refine the data model examples contained within the book in order to develop more customized data models. Essentially, it gives the modeler funda- mental tools and building blocks that can be reused. Therefore, the modeler can be more productive and save a great deal of time by starting with standard data models instead of building data models from scratch. Furthermore, the reader can also benefit from the data warehouse models that are applicable to decision support environments. This book not only pre- sents examples of data warehouse designs, but it also explains in detail how to convert the logical data models to an enterprise-wide data warehouse, then to departmental data marts. The logical data models and data warehouse models presented here are applicable across a wide variety of enterprises. These models are intended to be a starting point for developing logical and data warehouse data models for an enterprise. Each enterprise will have its own detailed requirements; the models will need to be modified and cus- tomized in order to be implemented for a specific enterprise. Because the data warehouse data models reflect actual database designs (as opposed to logical data models), they are even more dependent on the business needs of the spe- cific enterprise wishing to use these models. In addition, the models in this book can be used to validate an enterprise's existing data models. The models presented in the first part of this book (Chapters 2 through 9) are logical data models, not physical database designs. Therefore, these models are

📄 Page 17

Chapter 1 _ _ _ _ _ normalized and may require some denormalization when designing the physical database. Consistent with this point, the logical data models do not include any derived attributes because derived attributes do not add anything to the infor- mation requirements of a business. They merely serve to enhance performance of the physical database. These logical data models represent possible data requirements for enter- prises. They do not include many of the business processing rules that may accompany data models. The data models generally provide all the information needed to enforce business rules; however, the reader is advised in many cases that additional business rules may need to be developed to supplement the data models. Examples of the need for business rules are provided throughout this book. These data models were designed to benefit many different industries and enterprises. They were picked specifically because they represent very com- mon data constructs that appear in most organizations. Within these models, whenever there was a data modeling decision that may have been dependent on a specific enterprise, the most flexible data modeling option was chosen in order to accommodate many different enterprises. Furthermore, the chapter on Implementing Universal Data Models provides an explanation on how to use the data models to build an enterprise data model, logical data models, and physical database designs. Detailed examples are provided for how to transform the data models into a physical database design that can be implemented for a database management system. What Is New in the Second Edition of the Data Model Resource Book? The second edition of the Data Model Resource Book provides many enhance- ments and additional models. There are a great number of updates and addi- tions; the following points describe them at a high level. A great majority of the data models in the original Data Model Resource Book have been significantly enhanced with additional entities, attributes, and relationships. Many of the data models have slightly different and more enhanced data structures. Based on numerous usages and implementations of these models, the models have been updated to reflect even more effective data structures. A number of new chapters have been added to the second edition. Chapter 14 provides additional star schemas that can be used as templates for data analysis solutions. Chapter 15 provides an explanation of how to use the uni- versal data models to create an enterprise data model, a logical data model,

📄 Page 18

Introduction and a physical database design. This chapter provides examples of customiz- ing enterprise and logical data models and several physical database design examples for implementing one of the universal data models. A great number of new universal data models have been added to the already existing compre- hensive library from the first edition. Table 1.1 provides a listing of the new models. Table 1.1 Data Models Added in Second Edition CHAPTER 2 Parties 8 Accounting NEW DATA MODELS THAT HAVE BEEN ADDED FROM THE FIRST EDITION TO THE SECOND EDITION 2b Person—alternate model 2.4 Party roles 2.5 Specific party relationships 2.6 Common party relationships 2.11 Facility versus contact mechanism 2.12 Party communication event 2.13 Communication event follow up event 3 Products 4 Orders 5 Shipments 6 Work Efforts 7 Invoices 3.4 Product feature 3.10a Products and parts 3.10b Products and parts—alternate model 4.3 Sales order parties and contact mechanisms 4.4 Purchase order parties and contact mechanisms 4.6 Order adjustments 4.12 Agreement roles 5.4 Shipment receipt for incoming shipments 5.5 Item issuances for outgoing shipments 5.6 Shipping documents 5.7 Shipment route segments 6.1 Work requirement 6.2 Work requirement roles 6.12 Work effort results 7.8a Invoice payments 7.8b Invoice payments-alternate model 7.9 Financial accounts, withdrawals and deposits 8.2 Business transactions versus accounting transactions 8.4 General ledger account associations and subsidiary ledger accounts 8.7 Budget revision 8.8 Budget revision review 8.9 Budget scenario Continues

📄 Page 19

8 Chapter 1 Table 1.1 Data Models Added in Second Edition (Continued) CHAPTER 9 Human Resources NEW DATA MODELS THAT HAVE BEEN ADDED FROM THE FIRST EDITION TO THE SECOND EDITION 9.8 Benefits tracking 9.10 Employee application 9.11 Employee skills and qualifications 9.12 Employee performance 9.13 Employee termination 12 Star Schema Designs for Sales Analysis Star Schema Designs 12.2 Transaction oriented sales data mart 14 Additional Star Schema Designs 14.1 Inventory management star schema 14.2 Purchase order star schema 14.3 Shipment star schema 14.4 Work effort star schema 14.5 Financial analysis star schema 15 Implementing Universal Data Models 15.2. Customized party contact mechanism (using different terms) 15.3 Additions to the party contact mechanism model 15.4 Detailed model for sales force (showing a customized version for a particular application) 15.6 Party roles and relationships physical design option 1 15.7 Party roles and relationships physical design option 2 15.8 Party roles and relationships physical design option 3 Conventions and Standards Used in This Book The following section describes the naming standards and diagrarnming con- ventions used for presenting the models in this book. Details are provided for entities, subtypes, attributes, relationships, foreign keys, physical models, and illustration tables. Entities An entity is something of significance about which the enterprise wishes to store information. Whenever entities are referenced throughout the book, they are shown in capital letters. For example, ORDER represents an entity that stores information about a commitment between parties to purchase products. When the name of an entity is used in a sentence to illustrate concepts and busi-

📄 Page 20

Introduction Figure 1.1 An entity. ness rules, it may be shown in normal text—for example, "Many enterprises have mechanisms such as a sales order form to record sales order information." The naming conventions for an entity include using a singular noun that is as meaningful as possible to reflect the information it is maintaining. Additionally, the suffix TYPE is added to the entity name if the entity represents a classifica- tion of information such as an ORDER TYPE (i.e., sales versus purchase order) rather than a specific instance of a real thing such as an ORDER ("order #23987"). The data models in this book include TYPE entities on the diagrams, even though they usually have only an id and a description. These entities are included for completeness and to show where allowable values or look-ups are stored. Entities are included in the data model if it is a requirement of the enterprise to maintain the information included in the entity. For example, if an enterprise doesn't really care about tracking the tasks associated with a shipment, then even though this information exists in the real world, the data model should not incorporate this information because it may not be important enough informa- tion for the enterprise to maintain. Entities are represented by rounded boxes. Figure 1.1 shows an example of the entity ORDER. Subtypes and Supertypes A subtype, sometimes referred to as a subentity, is a classification of an entity that has characteristics such as attributes or relationships in common with the more general entity. LEGAL ORGANIZATION and INFORMAL ORGANIZA- TION are, for example, subtypes of ORGANIZATION. Subtypes are represented in the data modeling diagrams by entities inside other entities. The common attributes and relationships between subtypes are shown in the outside entity, which is known as the supertype. The attributes and relationships of the supertype are therefore inherited by the subtype. Figure 1.2 shows the supertype ORGANIZATION and its sub-types LEGAL ORGANIZA- TION and INFORMAL ORGANIZATION. Notice that the name applies to the