Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

O'Reilly logo
Patterns of Data Modeling

Book Description

Best-selling author and database expert with more than 25 years of experience modeling application and enterprise data, Dr. Michael Blaha provides tried and tested data model patterns, to help readers avoid common modeling mistakes and unnecessary frustration on their way to building effective data models. Unlike the typical methodology book, Patterns of Data Modeling provides advanced techniques for those who have mastered the basics.

Recognizing that database representation sets the path for software, determines its flexibility, affects its quality, and influences whether it succeeds or fails, the text focuses on databases rather than programming. It is one of the first books to apply the popular patterns perspective to database systems and data models. It offers practical advice on the core aspects of applications and provides authoritative coverage of mathematical templates, antipatterns, archetypes, identity, canonical models, and relational database design.

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
  5. 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
    2. 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
    3. 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
    4. 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
    5. 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
    6. 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
  6. 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
    2. 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
  7. 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
  8. 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
  9. 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
    2. 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
    3. 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
    4. 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
  10. 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
    2. 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
    3. 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
    4. Appendix C Glossary