You are previewing Patterns of Data Modeling.
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. Who Should Read This Book?
    2. What You Will Find
    3. Comparison with Other Books
    4. Acknowledgments
    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
      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
      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
      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
      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
      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
      1. Table 7.1
      2. Table 7.2
      3. Table 7.3
      4. Table 7.4
      5. Table 7.5
      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
      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
      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
      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
      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
      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
      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
      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
      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
      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
      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