Patterns of Data Modeling

Patterns of Data Modeling

by Michael Blaha
Released June 2010
Publisher(s): CRC Press
ISBN: 9781439819906

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

Book description

Helping readers avoid common mistakes and build better models, this is one of the first books to apply the popular patterns perspective to database systems and the data models that are used to design state-of-the-art, efficient database systems. Best-selling author and database expert Dr. Michael Blaha provides detailed data model patterns as a basis for expertly building data models. Since most commercial software is built around a database, he focuses on databases rather than programming. He emphasizes how database representation sets the path for the software, determines its flexibility, affects its quality, and influences whether the software succeeds or fails.

Table of contents

  1. Cover
  2. Preliminaries
  3. Preface
    1. Who Should Read This Book?
    2. What You Will Find
    3. Comparison with Other Books
    4. Acknowledgments
  4. Chapter 1 Introduction
    1. 1.1 What Is a Model?
    2. 1.2 Modeling Notation
      1. 1.2.1 UML
      2. 1.2.2 IDEF1X
      3. 1.2.3 Using Both Notations
    3. 1.3 What Is a Pattern?
    4. 1.4 Why Are Patterns Important?
    5. 1.5 Drawbacks of Patterns
    6. 1.6 Pattern vs. Seed Model
    7. 1.7 Aspects of Pattern Technology
    8. 1.8 Chapter Summary
    9. Bibliographic Notes
    10. References
    1. Table 1.1
  6. Part I Mathematical Templates
    1. Chapter 2 Tree Template
      1. 2.1 Hardcoded Tree Template
        1. 2.1.1 UML Template
        2. 2.1.2 IDEF1X Template
        3. 2.1.3 SQL Queries
        4. 2.1.4 Sample Populated Tables
        5. 2.1.5 Examples
      2. 2.2 Simple Tree Template
        1. 2.2.1 UML Template
        2. 2.2.2 IDEF1X Template
        3. 2.2.3 SQL Queries
        4. 2.2.4 Sample Populated Tables
        5. 2.2.5 Examples
      3. 2.3 Structured Tree Template
        1. 2.3.1 UML Template
        2. 2.3.2 IDEF1X Template
        3. 2.3.3 SQL Queries
        4. 2.3.4 Sample Populated Tables
        5. 2.3.5 Examples
      4. 2.4 Overlapping Trees Template
        1. 2.4.1 UML Template
        2. 2.4.2 IDEF1X Template
        3. 2.4.3 SQL Queries
        4. 2.4.4 Sample Populated Tables
        5. 2.4.5 Example
      5. 2.5 Tree Changing over Time Template
        1. 2.5.1 UML Template
        2. 2.5.2 IDEF1X Template
        3. 2.5.3 SQL Queries
        4. 2.5.4 Sample Populated Tables
        5. 2.5.5 Example
      6. 2.6 Degenerate Node and Edge Template
        1. 2.6.1 UML Template
        2. 2.6.2 IDEF1X Template
        3. 2.6.3 SQL Queries
        4. 2.6.4 Sample Populated Tables
        5. 2.6.5 Example
      7. 2.7 Chapter Summary
      8. Bibliographic Notes
      9. References
      1. Figure 2.1
      2. Figure 2.2
      3. Figure 2.3
      4. Figure 2.4
      5. Figure 2.5
      6. Figure 2.6
      7. Figure 2.7
      8. Figure 2.8
      9. Figure 2.9
      10. Figure 2.10
      11. Figure 2.11
      12. Figure 2.12
      13. Figure 2.13
      14. Figure 2.14
      15. Figure 2.15
      16. Figure 2.16
      17. Figure 2.17
      18. Figure 2.18
      19. Figure 2.19
      20. Figure 2.20
      21. Figure 2.21
      22. Figure 2.22
      23. Figure 2.23
      24. Figure 2.24
      25. Figure 2.25
      26. Figure 2.26
      27. Figure 2.27
      28. Figure 2.28
      29. Figure 2.29
      30. Figure 2.30
      31. Figure 2.31
      32. Figure 2.32
      33. Figure 2.33
      34. Figure 2.34
      35. Figure 2.35
      36. Figure 2.36
      37. Figure 2.37
      38. Figure 2.38
      39. Figure 2.39
      40. Figure 2.40
      41. Figure 2.41
      42. Figure 2.42
      43. Figure 2.43
      44. Figure 2.44
      45. Figure 2.45
      46. Figure 2.46
      1. Table 2.1
    4. Chapter 3 Directed Graph Template
      1. 3.1 Simple Directed Graph Template
        1. 3.1.1 UML Template
        2. 3.1.2 IDEF1X Template
        3. 3.1.3 SQL Queries
        4. 3.1.4 Sample Populated Tables
        5. 3.1.5 Example
      2. 3.2 Structured Directed Graph Template
        1. 3.2.1 UML Template
        2. 3.2.2 IDEF1X Template
        3. 3.2.3 SQL Queries
        4. 3.2.4 Sample Populated Tables
        5. 3.2.5 Examples
      3. 3.3 Node and Edge Directed Graph Template
        1. 3.3.1 UML Template
        2. 3.3.2 IDEF1X Template
        3. 3.3.3 SQL Queries
        4. 3.3.4 Sample Populated Tables
        5. 3.3.5 Examples
      4. 3.4 Connection Directed Graph Template
        1. 3.4.1 UML Template
        2. 3.4.2 IDEF1X Template
        3. 3.4.3 SQL Queries
        4. 3.4.4 Sample Populated Tables
        5. 3.4.5 Example
      5. 3.5 Simple DG Changing over Time Template
        1. 3.5.1 UML Template
        2. 3.5.2 IDEF1X Template
        3. 3.5.3 SQL Queries
        4. 3.5.4 Sample Populated Tables
        5. 3.5.5 Example
      6. 3.6 Node and Edge DG Changing over Time Template
        1. 3.6.1 UML Template
        2. 3.6.2 IDEF1X Template
        3. 3.6.3 SQL Queries
        4. 3.6.4 Sample Populated Tables
        5. 3.6.5 Examples
      7. 3.7 Chapter Summary
      8. Bibliographic Notes
      9. References
      1. Figure 3.1
      2. Figure 3.2
      3. Figure 3.3
      4. Figure 3.4
      5. Figure 3.5
      6. Figure 3.6
      7. Figure 3.7
      8. Figure 3.8
      9. Figure 3.9
      10. Figure 3.10
      11. Figure 3.11
      12. Figure 3.12
      13. Figure 3.13
      14. Figure 3.14
      15. Figure 3.15
      16. Figure 3.16
      17. Figure 3.17
      18. Figure 3.18
      19. Figure 3.19
      20. Figure 3.20
      21. Figure 3.21
      22. Figure 3.22
      23. Figure 3.23
      24. Figure 3.24
      25. Figure 3.25
      26. Figure 3.26
      27. Figure 3.27
      28. Figure 3.28
      29. Figure 3.29
      30. Figure 3.30
      31. Figure 3.31
      32. Figure 3.32
      33. Figure 3.33
      34. Figure 3.34
      35. Figure 3.35
      36. Figure 3.36
      37. Figure 3.37
      38. Figure 3.38
      39. Figure 3.39
      40. Figure 3.40
      41. Figure 3.41
      42. Figure 3.42
      43. Figure 3.43
      44. Figure 3.44
      45. Figure 3.45
      46. Figure 3.46
      47. Figure 3.47
      48. Figure 3.48
      1. Table 3.1
    7. Chapter 4 Undirected Graph Template
      1. 4.1 Node and Edge Undirected Graph Template
        1. 4.1.1 UML Template
        2. 4.1.2 IDEF1X Template
        3. 4.1.3 SQL Queries
        4. 4.1.4 Sample Populated Tables
        5. 4.1.5 Examples
      2. 4.2 Connection Undirected Graph Template
        1. 4.2.1 UML Template
        2. 4.2.2 IDEF1X Template
        3. 4.2.3 SQL Queries
        4. 4.2.4 Sample Populated Tables
        5. 4.2.5 Examples
      3. 4.3 Undirected Graph Changing over Time Template
        1. 4.3.1 UML Template
        2. 4.3.2 IDEF1X Template
        3. 4.3.3 SQL Queries
        4. 4.3.4 Sample Populated Tables
        5. 4.3.5 Example
      4. 4.4 Chapter Summary
      1. Figure 4.1
      2. Figure 4.2
      3. Figure 4.3
      4. Figure 4.4
      5. Figure 4.5
      6. Figure 4.6
      7. Figure 4.7
      8. Figure 4.8
      9. Figure 4.9
      10. Figure 4.10
      11. Figure 4.11
      12. Figure 4.12
      13. Figure 4.13
      14. Figure 4.14
      15. Figure 4.15
      16. Figure 4.16
      17. Figure 4.17
      18. Figure 4.18
      19. Figure 4.19
      20. Figure 4.20
      1. Table 4.1
    10. Chapter 5 Item Description Template
      1. 5.1 Item Description Template
        1. 5.1.1 UML Template
        2. 5.1.2 IDEF1X Template
        3. 5.1.3 SQL Queries
        4. 5.1.4 Sample Populated Tables
        5. 5.1.5 Examples
      2. 5.2 Homomorphism Template
        1. 5.2.1 UML Template
        2. 5.2.2 IDEF1X Template
        3. 5.2.3 SQL Queries
        4. 5.2.4 Sample Populated Tables
        5. 5.2.5 Examples
      3. 5.3 Chapter Summary
      4. Bibliographic Notes
      5. References
      1. Figure 5.1
      2. Figure 5.2
      3. Figure 5.3
      4. Figure 5.4
      5. Figure 5.5
      6. Figure 5.6
      7. Figure 5.7
      8. Figure 5.8
      9. Figure 5.9
      10. Figure 5.10
      11. Figure 5.11
      12. Figure 5.12
      13. Figure 5.13
      14. Figure 5.14
      15. Figure 5.15
      1. Table 5.1
      2. Table 5.2
    13. Chapter 6 Star Schema Template
      1. 6.1 Star Schema Template
        1. 6.1.1 UML Template
        2. 6.1.2 IDEF1X Template
        3. 6.1.3 SQL Queries
        4. 6.1.4 Sample Populated Tables
        5. 6.1.5 Examples
      2. 6.2 Chapter Summary
      3. Bibliographic Notes
      4. References
      1. Figure 6.1
      2. Figure 6.2
      3. Figure 6.3
      4. Figure 6.4
      5. Figure 6.5
      6. Figure 6.6
      7. Figure 6.7
      1. Table 6.1
    16. Chapter 7 Summary of Templates
      1. Table 7.1
      2. Table 7.2
      3. Table 7.3
      4. Table 7.4
      5. Table 7.5
  7. Part II Antipatterns
    1. Chapter 8 Universal Antipatterns
      1. 8.1 Symmetric Relationship Antipattern
        1. 8.1.1 Observation
        2. 8.1.2 Exceptions
        3. 8.1.3 Resolution
        4. 8.1.4 Examples
      2. 8.2 Dead Elements Antipattern
        1. 8.2.1 Observation
        2. 8.2.2 Exceptions
        3. 8.2.3 Resolution
        4. 8.2.4 Examples
      3. 8.3 Disguised Fields Antipattern
        1. 8.3.1 Observation
        2. 8.3.2 Exceptions
        3. 8.3.3 Resolution
        4. 8.3.4 Examples
      4. 8.4 Artificial Hardcoded Levels Antipattern
        1. 8.4.1 Observation
        2. 8.4.2 Exceptions
        3. 8.4.3 Resolution
        4. 8.4.4 Example
      5. 8.5 Excessive Generalization Antipattern
        1. 8.5.1 Observation
        2. 8.5.2 Exceptions
        3. 8.5.3 Resolution
        4. 8.5.4 Examples
      6. 8.6 Disconnected Entity Types Antipattern
        1. 8.6.1 Observation
        2. 8.6.2 Exceptions
        3. 8.6.3 Resolution
        4. 8.6.4 Example
      7. 8.7 Modeling Errors Antipattern
        1. 8.7.1 Observations
        2. 8.7.2 Exceptions
        3. 8.7.3 Resolution
        4. 8.7.4 Examples
      8. 8.8 Multiple Inheritance Antipattern
        1. 8.8.1 Observation
        2. 8.8.2 Exceptions
        3. 8.8.3 Resolutions
        4. 8.8.4 Example
      9. 8.9 Paradigm Degradation Antipattern
        1. 8.9.1 Observation
        2. 8.9.2 Exceptions
        3. 8.9.3 Resolution
        4. 8.9.4 Examples
      10. 8.10 Chapter Summary
      11. Bibliographic Notes
      12. References
      1. Figure 8.1
      2. Figure 8.2
      3. Figure 8.3
      4. Figure 8.4
      5. Figure 8.5
      6. Figure 8.6
      7. Figure 8.7
      8. Figure 8.8
      9. Figure 8.9
      10. Figure 8.10
      11. Figure 8.11
      12. Figure 8.12
      13. Figure 8.13
      1. Table 8.1
    4. Chapter 9 Non-Data-Warehouse Antipatterns
      1. 9.1 Derived Data Antipattern
        1. 9.1.1 Observation
        2. 9.1.2 Exceptions
        3. 9.1.3 Resolution
        4. 9.1.4 Examples
      2. 9.2 Parallel Attributes Antipattern
        1. 9.2.1 Observation
        2. 9.2.2 Exceptions
        3. 9.2.3 Resolution
        4. 9.2.4 Examples
      3. 9.3 Parallel Relationships Antipattern
        1. 9.3.1 Observation
        2. 9.3.2 Exceptions
        3. 9.3.3 Resolution
        4. 9.3.4 Example
      4. 9.4 Combined Entity Types Antipattern
        1. 9.4.1 Observation
        2. 9.4.2 Exceptions
        3. 9.4.3 Resolution
        4. 9.4.4 Example
      5. 9.5 Chapter Summary
      1. Figure 9.1
      2. Figure 9.2
      3. Figure 9.3
      4. Figure 9.4
      5. Figure 9.5
      6. Figure 9.6
      7. Figure 9.7
      8. Figure 9.8
      9. Figure 9.9
      10. Figure 9.10
      11. Figure 9.11
      1. Table 9.1
  8. Part III Archetypes
    1. Chapter 10 Archetypes
      1. 10.1 Account
      2. 10.2 Actor
      3. 10.3 Address
      4. 10.4 Asset
      5. 10.5 Contract
      6. 10.6 Course
      7. 10.7 Customer
      8. 10.8 Document
      9. 10.9 Event
      10. 10.10 Flight
      11. 10.11 Item
      12. 10.12 Location
      13. 10.13 Opportunity
      14. 10.14 Part
      15. 10.15 Payment
      16. 10.16 Position
      17. 10.17 Product
      18. 10.18 Role
      19. 10.19 Transaction
      20. 10.20 Vendor
      21. 10.21 Chapter Summary
      22. Bibliographic Notes
      23. References
      1. Figure 10.1
      2. Figure 10.2
      3. Figure 10.3
      4. Figure 10.4
      5. Figure 10.5
      6. Figure 10.6
      7. Figure 10.7
      8. Figure 10.8
      9. Figure 10.9
      10. Figure 10.10
      11. Figure 10.11
      12. Figure 10.12
      13. Figure 10.13
      14. Figure 10.14
      15. Figure 10.15
      16. Figure 10.16
      17. Figure 10.17
      18. Figure 10.18
      19. Figure 10.19
      20. Figure 10.20
      21. Figure 10.21
      22. Figure 10.22
      23. Figure 10.23
      24. Figure 10.24
      25. Figure 10.25
      26. Figure 10.26
      27. Figure 10.27
      28. Figure 10.28
      29. Figure 10.29
      30. Figure 10.30
      31. Figure 10.31
      32. Figure 10.32
      33. Figure 10.33
      34. Figure 10.34
      35. Figure 10.35
      36. Figure 10.36
      37. Figure 10.37
      38. Figure 10.38
      39. Figure 10.39
      40. Figure 10.40
      1. Table 10.1
      2. Table 10.2
  9. Part IV Identity
    1. Chapter 11 Identity
      1. 11.1 Intrinsic Identity
        1. 11.1.1 Candidate Keys
        2. 11.1.2 UML Qualifiers
        3. 11.1.3 Logical Horizon
      2. 11.2 Names
      3. 11.3 Surrogate Identity
      4. 11.4 Structured Fields
      5. 11.5 Master Applications
      6. 11.6 Merging Data
      7. 11.7 Chapter Summary
      8. Bibliographic Notes
      9. References
      1. Figure 11.1
      2. Figure 11.2
      3. Figure 11.3
      1. Table 11.1
  10. Part V Canonical Models
    1. Chapter 12 Language Translation
      1. 12.1 Alternative Architectures
      2. 12.2 Attribute Translation in Place
      3. 12.3 Phrase-to-Phrase Translation
      4. 12.4 Language-Neutral Translation
      5. 12.5 Chapter Summary
      6. Bibliographic Notes
      7. References
      1. Figure 12.1
      2. Figure 12.2
      3. Figure 12.3
      4. Figure 12.4
      5. Figure 12.5
      6. Figure 12.6
      7. Figure 12.7
      8. Figure 12.8
      1. Table 12.1
      2. Table 12.2
    4. Chapter 13 Softcoded Values
      1. 13.1 UML Model
        1. 13.1.1 Data
        2. 13.1.2 Metadata
      2. 13.2 IDEF1X Model
      3. 13.3 Architecture
        1. 13.3.1 Using Softcoded Values in an Application
        2. 13.3.2 Hardcoded Model for Populating Metadata
        3. 13.3.3 Mixing Hardcoded and Softcoded Attributes
        4. 13.3.4 Database Performance
        5. 13.3.5 User Interface
      4. 13.4 Softcoding Variations
        1. 13.4.1 Add Value Metadata
        2. 13.4.2 Cut Metadata
        3. 13.4.3 Store All SoftcodedValues as Strings
        4. 13.4.4 Subtype by Data Type
        5. 13.4.5 Require All Attributes to be Enumerated
        6. 13.4.6 Enable Time History
        7. 13.4.7 Support Weakly Typed Entities
        8. 13.4.8 Combine Variations
      5. 13.5 Chapter Summary
      6. Bibliographic Notes
      7. References
      1. Figure 13.1
      2. Figure 13.2
      3. Figure 13.3
      4. Figure 13.4
      5. Figure 13.5
      6. Figure 13.6
      7. Figure 13.7
      8. Figure 13.8
      9. Figure 13.9
      10. Figure 13.10
      11. Figure 13.11
      12. Figure 13.12
      13. Figure 13.13
      14. Figure 13.14
      15. Figure 13.15
      16. Figure 13.16
      17. Figure 13.17
      18. Figure 13.18
      19. Figure 13.19
      20. Figure 13.20
      21. Figure 13.21
      22. Figure 13.22
      23. Figure 13.23
      24. Figure 13.24
      1. Table 13.1
    7. Chapter 14 Generic Diagrams
      1. 14.1 Generic Diagram Examples
      2. 14.2 Diagram Subject Area
      3. 14.3 Model Subject Area
      4. 14.4 Model-Diagram Binding Subject Area
      5. 14.5 DiagramType Subject Area
      6. 14.6 Diagram Example, Revisited
      7. 14.7 Chapter Summary
      8. Bibliographic Notes
      1. Figure 14.1
      2. Figure 14.2
      3. Figure 14.3
      4. Figure 14.4
      5. Figure 14.5
      6. Figure 14.6
      7. Figure 14.7
      8. Figure 14.8
      9. Figure 14.9
      10. Figure 14.10
      11. Figure 14.11
      12. Figure 14.12
      13. Figure 14.13
      14. Figure 14.14
      15. Figure 14.15
    9. Chapter 15 State Diagrams
      1. 15.1 State Diagrams
      2. 15.2 Scenarios
      3. 15.3 Chapter Summary
      4. Bibliographic Notes
      5. References
      1. Figure 15.1
      2. Figure 15.2
      3. Figure 15.3
      4. Figure 15.4
      5. Figure 15.5
  11. Part VI Relational Database Design
    1. Chapter 16 Relational Database Design
      1. 16.1 Mapping: Entity Types
      2. 16.2 Mapping: Non-Qualified Relationships
      3. 16.3 Mapping Qualified Relationships
      4. 16.4 Mapping: Generalizations
      5. 16.5 Design Identity
      6. 16.6 Referential Integrity
      7. 16.7 Miscellaneous Database Constraints
        1. 16.7.1 SQL Triggers
        2. 16.7.2 General SQL Constraints
      8. 16.8 Indexes
      9. 16.9 Generating SQL Code
      10. 16.10 Chapter Summary
      11. Bibliographic Notes
      12. References
      1. Figure 16.1
      2. Figure 16.2
      3. Figure 16.3
      4. Figure 16.4
      5. Figure 16.5
      6. Figure 16.6
      7. Figure 16.7
      8. Figure 16.8
      9. Figure 16.9
      10. Figure 16.10
      11. Figure 16.11
      12. Figure 16.12
      13. Figure 16.13
      14. Figure 16.14
      15. Figure 16.15
      16. Figure 16.16
      17. Figure 16.17
      18. Figure 16.18
      1. Table 16.1
      2. Table 16.2
    4. Appendix A Explanation of the UML Notation
      1. Entity Type
      2. Relationships
      3. Generalization
      4. Bibliographic Notes
      5. References
      1. Figure A1.1
      2. Figure A1.2
      3. Figure A1.3
      4. Figure A1.4
      5. Figure A1.5
      6. Figure A1.6
      7. Figure A1.7
      8. Figure A1.8
      9. Figure A1.9
      10. Figure A1.10
    6. Appendix B Explanation of the IDEF1X Notation
      1. Entity Type
      2. Relationships
      3. Generalization
      4. Bibliographic Notes
      5. References
      1. Figure B1.1
      2. Figure B1.2
      3. Figure B1.3
      4. Figure B1.4
      5. Figure B1.5
      6. Figure B1.6
      7. Figure B1.7
    8. Appendix C Glossary

Product information

  • Title: Patterns of Data Modeling
  • Author(s): Michael Blaha
  • Release date: June 2010
  • Publisher(s): CRC Press
  • ISBN: 9781439819906