You are previewing Database Design for Mere Mortals®: A Hands-on Guide to Relational Database Design, Third Edition.
O'Reilly logo
Database Design for Mere Mortals®: A Hands-on Guide to Relational Database Design, Third Edition

Book Description

The #1 Easy, Commonsense Guide to Database Design! Michael J. Hernandez’s best-selling Database Design for Mere Mortals® has earned worldwide respect as the clearest, simplest way to learn relational database design. Now, he’s made this hands-on, software-independent tutorial even easier, while ensuring that his design methodology is still relevant to the latest databases, applications, and best practices. Step by step, Database Design for Mere Mortals®, Third Edition, shows you how to design databases that are soundly structured, reliable, and flexible, even in modern web applications. Hernandez guides you through everything from database planning to defining tables, fields, keys, table relationships, business rules, and views. You’ll learn practical ways to improve data integrity, how to avoid common mistakes, and when to break the rules.

Coverage includes

Understanding database types, models, and design terminology

Discovering what good database design can do for you—and why bad design can make your life miserable

Setting objectives for your database, and transforming those objectives into real designs

Analyzing a current database so you can identify ways to improve it

Establishing table structures and relationships, assigning primary keys, setting field specifications, and setting up views

Ensuring the appropriate level of data integrity for each application

Identifying and establishing business rules

Whatever relational database systems you use, Hernandez will help you design databases that are robust and trustworthy. Never designed a database before? Settling for inadequate generic designs? Running existing databases that need improvement? Start here.

Table of Contents

  1. Title Page
  2. Copyright Page
  3. Dedication Page
  4. About the Author
  5. Contents
  6. Foreword
    1. To the Third Edition
  7. Preface
  8. Acknowledgments
  9. Introduction
    1. What’s New in the Third Edition
    2. Who Should Read This Book
    3. The Purpose of This Book
    4. How to Read This Book
    5. How This Book Is Organized
    6. A Word About the Examples and Techniques in This Book
  10. Part I. Relational Database Design
    1. 1. The Relational Database
      1. Topics Covered in This Chapter
      2. Types of Databases
      3. Early Database Models
      4. The Relational Database Model
      5. Relational Database Management Systems
      6. Beyond the Relational Model
      7. What the Future Holds
      8. Summary
      9. Review Questions
    2. 2. Design Objectives
      1. Topics Covered in This Chapter
      2. Why Should You Be Concerned with Database Design?
      3. The Importance of Theory
      4. The Advantage of Learning a Good Design Methodology
      5. Objectives of Good Design
      6. Benefits of Good Design
      7. Database Design Methods
      8. Normalization
      9. Summary
      10. Review Questions
    3. 3. Terminology
      1. Topics Covered in This Chapter
      2. Why This Terminology Is Important
      3. Value-Related Terms
      4. Structure-Related Terms
      5. Relationship-Related Terms
      6. Integrity-Related Terms
      7. Summary
      8. Review Questions
  11. Part II. The Design Process
    1. 4. Conceptual Overview
      1. Topics Covered in This Chapter
      2. The Importance of Completing the Design Process
      3. Defining a Mission Statement and Mission Objectives
      4. Analyzing the Current Database
      5. Creating the Data Structures
      6. Determining and Establishing Table Relationships
      7. Determining and Defining Business Rules
      8. Determining and Defining Views
      9. Reviewing Data Integrity
      10. Summary
      11. Review Questions
    2. 5. Starting the Process
      1. Topics Covered in This Chapter
      2. Conducting Interviews
      3. Defining the Mission Statement
      4. Defining the Mission Objectives
      5. Summary
      6. Review Questions
    3. 6. Analyzing the Current Database
      1. Topics Covered in This Chapter
      2. Getting to Know the Current Database
      3. Conducting the Analysis
      4. Looking at How Data Is Collected
      5. Looking at How Information Is Presented
      6. Conducting Interviews
      7. Interviewing Users
      8. Interviewing Management
      9. Compiling a Complete List of Fields
      10. Summary
      11. Review Questions
    4. 7. Establishing Table Structures
      1. Topics Covered in This Chapter
      2. Defining the Preliminary Table List
      3. Defining the Final Table List
      4. Associating Fields with Each Table
      5. Refining the Fields
      6. Refining the Table Structures
      7. Summary
      8. Review Questions
    5. 8. Keys
      1. Topics Covered in This Chapter
      2. Why Keys Are Important
      3. Establishing Keys for Each Table
      4. Table-Level Integrity
      5. Reviewing the Initial Table Structures
      6. Summary
      7. Review Questions
    6. 9. Field Specifications
      1. Topics Covered in This Chapter
      2. Why Field Specifications Are Important
      3. Field-Level Integrity
      4. Anatomy of a Field Specification
      5. Using Unique, Generic, and Replica Field Specifications
      6. Defining Field Specifications for Each Field in the Database
      7. Summary
      8. Review Questions
    7. 10. Table Relationships
      1. Topics Covered in This Chapter
      2. Why Relationships Are Important
      3. Types of Relationships
      4. Identifying Existing Relationships
      5. Establishing Each Relationship
      6. Refining All Foreign Keys
      7. Establishing Relationship Characteristics
      8. Relationship-Level Integrity
      9. Summary
      10. Review Questions
    8. 11. Business Rules
      1. Topics Covered in This Chapter
      2. What Are Business Rules?
      3. Categories of Business Rules
      4. Defining and Establishing Business Rules
      5. Validation Tables
      6. Reviewing the Business Rule Specifications Sheets
      7. Summary
      8. Review Questions
    9. 12. Views
      1. Topics Covered in This Chapter
      2. What Are Views?
      3. Anatomy of a View
      4. Determining and Defining Views
      5. Summary
      6. Review Questions
    10. 13. Reviewing Data Integrity
      1. Topics Covered in This Chapter
      2. Why You Should Review Data Integrity
      3. Reviewing and Refining Data Integrity
      4. Assembling the Database Documentation
      5. Done at Last!
      6. Summary
  12. Part III. Other Database Design Issues
    1. 14. Bad Design—What Not to Do
      1. Topics Covered in This Chapter
      2. Flat-File Design
      3. Spreadsheet Design
      4. Database Design Based on the Database Software
      5. A Final Thought
      6. Summary
    2. 15. Bending or Breaking the Rules
      1. Topics Covered in This Chapter
      2. When May You Bend or Break the Rules?
      3. Documenting Your Actions
      4. Summary
    3. In Closing
  13. Part IV. Appendixes
    1. A. Answers to Review Questions
      1. Chapter 1
      2. Chapter 2
      3. Chapter 3
      4. Chapter 4
      5. Chapter 5
      6. Chapter 6
      7. Chapter 7
      8. Chapter 8
      9. Chapter 9
      10. Chapter 10
      11. Chapter 11
      12. Chapter 12
    2. B. Diagram of the Database Design Process
    3. C. Design Guidelines
      1. Defining and Establishing Field-Specific Business Rules
      2. Defining and Establishing Relationship-Specific Business Rules
      3. Elements of a Candidate Key
      4. Elements of a Foreign Key
      5. Elements of a Primary Key
      6. Elements of the Ideal Field
      7. Elements of the Ideal Table
      8. Field-Level Integrity
      9. Guidelines for Composing a Field Description
      10. Guidelines for Composing a Table Description
      11. Guidelines for Creating Field Names
      12. Guidelines for Creating Table Names
      13. Identifying Relationships
      14. Identifying View Requirements
      15. Interview Guidelines
      16. Mission Statements
      17. Mission Objectives
      18. Relationship-Level Integrity
      19. Resolving a Multivalued Field
      20. Table-Level Integrity
    4. D. Documentation Forms
    5. E. Database Design Diagram Symbols
    6. F. Sample Designs
    7. G. On Normalization
      1. Please Note . . .
      2. A Brief Recap
      3. How Normalization Is Integrated into My Design Methodology
      4. Logical Design versus Physical Design and Implementation
    8. H. Recommended Reading
    9. Glossary
    10. References
  14. Index
  15. Ad Pages