SQL Database Design

Table of Contents

• 1. Introduction
• 2. Terminology
  • 2.1 Related Database Terms
  • 2.2 The Parent-Child Fuss
• 3. Modeling Levels
• 4. Notations and Paradigms
  • 4.1 Notations
  • 4.2 Paradigms
• 5. Properties of Relationships
  • 5.1 Degree
  • 5.2 Type
    • 5.2.1 Referential Relationships
    • 5.2.2 Hierarchical Relationships
  • 5.3 Cardinality
  • 5.4 Optionality
  • 5.5 Navigability
  • 5.6 Epilog on Relationship Notations
• 6. Database Modeling Process
• 7. Standard Data Types
  • 7.1 String Types
    • 7.1.1 Lengths
    • 7.1.2 National String Types
    • 7.1.3 Storage Requirements
    • 7.1.4 Oracle VARCHAR2
  • 7.2 Numeric Types
    • 7.2.1 Integral Numbers
    • 7.2.2 Fractional Numbers
      • 7.2.2.1 Precise Fractional Numbers
      • 7.2.2.2 Approximate Fractional Numbers
  • 7.3 Booleans
    • 7.3.1 Three-Valued Logic
    • 7.3.2 Null Propagation
    • 7.3.3 My Own Interpretation
    • 7.3.4 Using Booleans
    • 7.3.5 Simulating Booleans
      • 7.3.5.1 Integer Booleans
      • 7.3.5.2 Bit Booleans
      • 7.3.5.3 String Booleans
      • 7.3.5.4 Enumerated Booleans
    • 7.3.6 Boolean Domains
    • 7.3.7 Object-Relational Booleans
    • 7.3.8 Summary
  • (7.4 Temporal Types)
  • 7.5 Binary Types
  • 7.7 Collective Types
  • 7.8 Structured Types
• 8. User-Defined Types
  • 8.1 Domains
  • 8.2 Distinct Types
  • 8.3 Enumerated Types
    • 8.3.1 PostgreSQL ENUMs
    • 8.3.2 MySQL ENUMs
    • 8.3.3 Other DBMSs
  • 8.4 Object-Relational Types
• 9. Default Values
  • 9.1 Simple Defaults
  • 9.2 Derived Values
  • 9.3 Auto-Increment Numbers
  • 9.4 Sequences
  • 9.5 Summary
• 10. Constraints
  • 10.1 Primary Keys
    • 10.1.1 Unsigned IDs
    • 10.1.2 Strings as Keys
    • 10.1.3 Mutability
  • 10.2 Foreign Keys
    • 10.2.1 Integrity Rules
    • 10.2.2 Integrity Actions
      • 10.2.2.1 NO ACTION
      • 10.2.2.2 RESTRICT
      • 10.2.2.3 CASCADE
      • 10.2.2.4 SET NULL
      • 10.2.2.5 SET DEFAULT
    • 10.2.3 Summary
  • 10.3 Unique Constraints
  • 10.4 Check Constraints
    • 10.4.1 Foreign Key Type Propagation
    • 10.4.2 MySQL ENUM Propagation Workaround
    • 10.4.3 Maximum List Size
  • 10.5 Not-Null Constraints
• 11. Indices
• 12. Auxiliary Tables
  • 12.1 Lookup Tables
    • 12.1.1 Constraint Lookup Tables
    • 12.1.2 Real Lookup Tables
    • 12.1.3 Constraint Repetition
  • 12.2 Sequence Tables
  • 12.3 Constants Tables
• 13. Design Heuristics
  • 13.1 Natural vs. Artificial Keys
    • 13.1.1 Natural Keys
    • 13.1.2 Artificial Keys
    • 13.1.3 Scheme of Key Characteristics
    • 13.1.4 Using Natural Keys
    • 13.1.5 Using Identity Keys
    • 13.1.6 Using Constructed Keys
    • 13.1.7 Using Surrogate Keys
    • 13.1.8 Summary
  • 13.2 Dependent Keys
  • 13.3 Predefined Values
    • 13.3.1 Constraint-Based Solutions
    • 13.3.2 Table-Based Solutions
    • 13.3.3 Decision Matrix
    • 13.3.4 Value Naming
  • 13.4 Modeling Relationships
    • 13.4.1 Inheritance
    • 13.4.2 Many-to-Many Relationships
    • 13.4.3 Multi-Degree Relationships
    • 13.4.4 Composition
    • 13.4.5 Aggregation
    • 13.4.6 Association
    • 13.4.7 Summary
• 14. Normalization
  • 14.1 Repeating Attributes
  • 14.2 Misconception of Entity Types
  • 14.3 First Normal Form
  • 14.4 Functional Dependencies
  • 14.5 Second Normal Form
  • 14.6 Third Normal Form
• 15. Advanced Data Structures
  • 15.1 Trees
    • 15.1.1 Adjacency Lists
    • 15.1.2 Nested Sets
    • 15.1.3 Summary
  • (15.2 Graphs)
• 16. Conclusion
• Bibliography