The Data Model Resource Book, Volume 1 2nd Ed.

A Library of Universal Data Models for All Enterprises

Len Silverston

Publisher: Wiley, 2001, 538 pages

ISBN: 0-471-38023-7

Keywords: Programming, Enterprise Resource Planning, Databases

Last modified: April 6, 2021, 8:12 a.m.

Synopsis

Industry experts raved about The Data Model Resource Book when it first came out—and no wonder. This book arms you with a powerful set of data models and data warehouse designs that you can use to jump-start your database development projects. You get proven models for common business functions such as ordering and managing products, handling shipments, invoicing, accounting and budgeting, managing human resources, contact management, and project management. You’ll save countless hours and thousands of dollars in database development costs.

This updated edition, fully edited and revised by Len Silverston, includes many new and expanded data models, including models for call center management, product customization, shipping and receiving, budgeting scenarios, and employee qualifications and performance. Plus, there are new data mart designs, including financial analysis, inventory management, and shipping logistics.

With this book, you’ll learn how to:

  • Customize enterprise and logical data models that meet the specific needs of your organization
  • Convert logical data models to data warehouses and data marts
  • Develop physical data designs and evaluate design options based on the universal data models
  • Integrate databases and data warehouses across the enterprise
  • Validate your organization’s existing data models

You’ll also want to check out the companion volume, The Data Model Resource Book, Revised Edition, Volume 2 (0-471-35348-5), which provides universal data models that have been tailored for various industries and applications.

See how you can save even more time and money with the Data Model Resource Book, Revised Edition, Volume 1 CD-ROM, which:

  • Provides the SQL code you’ll need to implement the models described in the book or to reverse-engineer them into your CASE tool
  • Allows you to view for free the demonstration of data models from both Volume 1 and Volume 2
  • Can be purchased separately by unlocking the enclosed CD-ROM via phone or online–directions inside

Table of Contents

  • 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
    • Party
    • Party Roles
      • Organization Roles
      • Common Party Role Subtypes
      • Should Roles Be Defined at the Time of the Transaction?
      • Party Role Example
      • Role Types throughout This Book
    • Party Relationship
      • Party Relationship Examples
      • Party Relationship Information
      • Status Types
    • Party Contact Information
      • Postal Address Information
        • Geographic Boundaries
      • Party Contact Mechanism—Telecommunications Numbers and Electronic Addresses
      • Party Contact Mechanism (Expanded)
      • Contact Mechanism Purpose
    • Facility versus Contact Mechanism
    • Party Communication Event
      • Communication Event Follow-Up
    • Summary
  • Chapter 3: Products
    • Product Definition
    • Product Category
    • Product Identification Codes
    • Product Features
      • Product Feature Interaction
      • Product Feature Subtypes
      • Product Feature Examples
      • Unit of Measure
    • Suppliers and Manufacturers of Products
    • Inventory Item Storage
    • Product Pricing
      • Pricing Subtypes
      • Price Component Attributes and Relationship to Product or Product Feature
      • Pricing Factors
      • International Pricing
      • Example of Product Pricing
    • Product Costing
    • Product to Product Associations
      • Products and Parts
    • Summary
  • Chapter 4: Ordering Products
    • Standard Order Model
    • Order and Order Items
    • 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
  • 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
      • 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
    • Position Reporting Relationships
      • Position Reporting Structure
    • Salary Determination and Pay History
      • Position Type Rate
      • Pay Grade and Salary Step
      • Pay History and Actual Salary
    • Benefits Definition and Tracking
      • Employment
      • Party Benefit
      • Period Type
      • Benefit Type
    • Payroll Information
      • Employee
      • Payment Method Type
      • Payroll Preference
      • Paycheck
      • Deduction and Deduction Type
    • Employment Application
    • Employee Skills and Qualifications
    • Employee Performance
    • Employee Termination
    • Summary
  • Chapter 10: Creating the Data Warehouse Data Model from the Enterprise Data Model
    • The Data Warehouse Architecture
      • The Enterprise Data Model
      • The Data Warehouse Design
      • The Departmental Data Warehouse Design or Data Mart
      • An Architected Data Warehouse Environment
    • The Enterprise Data Model
      • Transformation Requirements
      • Process Models
      • High-Level and Logical Data Models
    • Making the Transformation
      • Removing Operational Data
      • Adding an Element of Time to the Warehouse Key
      • Adding Derived Data
      • Creating Relationship Artifacts
      • Changing Granularity of Data
      • Merging Tables
      • Creation of Arrays of Data
      • Organizing Data According to Its Stability
    • Summary
  • Chapter 11: A Sample Data Warehouse Data Model
    • Transformation to Customer Invoice
      • Removing Operational Data
      • Adding an Element of Time
      • Adding Derived Data
      • 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
  • Chapter 12: Star Schema Designs for Sales Analysis
    • Sales Analysis Data Mart
      • Customer Sales Facts
      • Customer Dimension
      • Customer Demographics Dimensions
      • Sales Reps Dimensions
      • 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
    • Financial Analysis
    • Summary
  • Chapter 15: Implementing the Universal Data Models
    • The Enterprise Data Model—An Integrated Business View of the Enterprise's Information
      • Customizing the Universal Data Models
        • Degrees of Customization
        • Customizing the Models for Unique Business Terminology
        • Example of Changing the Terms for the Specific Enterprise
        • Additional Information Requirements Needed for the Enterprise
      • How the Universal Data Models and Enterprise Data Model Slove Business Problems
    • Using a Data Model for a Particular Application
      • Understanding Business Processes
      • Building the Logical Data Model
    • Physical Database Design
      • Basic Database Design Principles
      • Creating a Physical Database Design
      • Physical Database Design Examples
        • Review of the Party Role and Relationship Model
        • Party Roles and Relationships Physical Design, Option 1
          • Example Data for Physical Database Design, Option 1
        • Party Roles and Relationships Physical Design, Option 2
        • Party Roles and Relationships Generic Design, Option 3
    • Using the Data Warehouse Models
    • Summary
  • 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
  • Other Reusable Data Model and Data Warehouse Design Resources