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:
Foreword xxi Preface xxv Acknowledgments xxvii Introduction xxix   Part I: Relational Database Design 1   Chapter 1: The Relational Database 3 Topics Covered in This Chapter 3 Types of Databases 4 Early Database Models 5 The Hierarchical Database Model 5 The Network Database Model 9 The Relational Database Model 12 Retrieving Data 15 Advantages of a Relational Database 16 Relational Database Management Systems 18 Beyond the Relational Model 19 What the Future Holds 21 A Final Note 22 Summary 22 Review Questions 24   Chapter 2: Design Objectives 25 Topics Covered in This Chapter 25 Why Should You Be Concerned with Database Design? 25 The Importance of Theory 27 The Advantage of Learning a Good Design Methodology 29 Objectives of Good Design 30 Benefits of Good Design 31 Database Design Methods 32 Traditional Design Methods 32 The Design Method Presented in This Book 34 Normalization 35 Summary 38 Review Questions 39   Chapter 3: Terminology 41 Topics Covered in This Chapter 41 Why This Terminology Is Important 41 Value-Related Terms 43 Data 43 Information 43 Null 45 The Value of Nulls 46 The Problem with Nulls 47 Structure-Related Terms 49 Table 49 Field 52 Record 53 View 54 Keys 56 Index 58 Relationship-Related Terms 59 Relationships 59 Types of Relationships 60 Types of Participation 65 Degree of Participation 66 Integrity-Related Terms 67 Field Specification 67 Data Integrity 68 Summary 69 Review Questions 70   Part II: The Design Process 73   Chapter 4: Conceptual Overview 75 Topics Covered in This Chapter 75 The Importance of Completing the Design Process 76 Defining a Mission Statement and Mission Objectives 77 Analyzing the Current Database 78 Creating the Data Structures 80 Determining and Establishing Table Relationships 81 Determining and Defining Business Rules 81 Determining and Defining Views 83 Reviewing Data Integrity 83 Summary 84 Review Questions 86   Chapter 5: Starting the Process 89 Topics Covered in This Chapter 89 Conducting Interviews 89 Participant Guidelines 91 Interviewer Guidelines (These Are for You) 93 The Case Study: Mike’s Bikes 98 Defining the Mission Statement 100 The Well-Written Mission Statement 100 Composing a Mission Statement 102 Defining the Mission Objectives 105 Well-Written Mission Objectives 106 Composing Mission Objectives 108 Summary 112 Review Questions 113   Chapter 6: Analyzing the Current Database 115 Topics Covered in This Chapter 115 Getting to Know the Current Database 115 Paper-Based Databases 118 Legacy Databases 119 Conducting the Analysis 121 Looking at How Data Is Collected 121 Looking at How Information Is Presented 125 Conducting Interviews 129 Basic Interview Techniques 130 Before You Begin the Interview Process . . . 137 Interviewing Users 137 Reviewing Data Type and Usage 138 Reviewing the Samples 140 Reviewing Information Requirements 144 Interviewing Management 152 Reviewing Current Information Requirements 153 Reviewing Additional Information Requirements 154 Reviewing Future Information Requirements 155 Reviewing Overall Information Requirements 155 Compiling a Complete List of Fields 157 The Preliminary Field List 157 The Calculated Field List 164 Reviewing Both Lists with Users and Management 165 Case Study 166 Summary 171 Review Questions 172   Chapter 7: Establishing Table Structures 175 Topics Covered in This Chapter 175 Defining the Preliminary Table List 176 Identifying Implied Subjects 176 Using the List of Subjects 178 Using the Mission Objectives 182 Defining the Final Table List 184 Refining the Table Names 186 Indicating the Table Types 192 Composing the Table Descriptions 192 Associating Fields with Each Table 199 Refining the Fields 202 Improving the Field Names 202 Using an Ideal Field to Resolve Anomalies 206 Resolving Multipart Fields 210 Resolving Multivalued Fields 212 Refining the Table Structures 219 A Word about Redundant Data and Duplicate Fields 219 Using an Ideal Table to Refine Table Structures 220 Establishing Subset Tables 228 Case Study 233 Summary 240 Review Questions 242   Chapter 8: Keys 243 Topics Covered in This Chapter 243 Why Keys Are Important 244 Establishing Keys for Each Table 244 Candidate Keys 245 Primary Keys 253 Alternate Keys 260 Non-keys 261 Table-Level Integrity 261 Reviewing the Initial Table Structures 261 Case Study 263 Summary 269 Review Questions 270   Chapter 9: Field Specifications 273 Topics Covered in This Chapter 273 Why Field Specifications Are Important 274 Field-Level Integrity 275 Anatomy of a Field Specification 277 General Elements 277 Physical Elements 285 Logical Elements 292 Using Unique, Generic, and Replica Field Specifications 300 Defining Field Specifications for Each Field in the Database 306 Case Study 308 Summary 310 Review Questions 311   Chapter 10: Table Relationships 313 Topics Covered in This Chapter 313 Why Relationships Are Important 314 Types of Relationships 315 One-to-One Relationships 316 One-to-Many Relationships 319 Many-to-Many Relationships 321 Self-Referencing Relationships 329 Identifying Existing Relationships 333 Establishing Each Relationship 344 One-to-One and One-to-Many Relationships 345 The Many-to-Many Relationship 352 Self-Referencing Relationships 358 Reviewing the Structure of Each Table 364 Refining All Foreign Keys 365 Elements of a Foreign Key 365 Establishing Relationship Characteristics 372 Defining a Deletion Rule for Each Relationship 372 Identifying the Type of Participation for Each Table 377 Identifying the Degree of Participation for Each Table 380 Verifying Table Relationships with Users and Management 383 A Final Note 383 Relationship-Level Integrity 384 Case Study 384 Summary 389 Review Questions 391   Chapter 11: Business Rules 393 Topics Covered in This Chapter 393 What Are Business Rules? 393 Types of Business Rules 397 Categories of Business Rules 399 Field-Specific Business Rules 399 Relationship-Specific Business Rules 401 Defining and Establishing Business Rules 402 Working with Users and Management 402 Defining and Establishing Field-Specific Business Rules 403 Defining and Establishing Relationship-Specific Business Rules 412 Validation Tables 417 What Are Validation Tables? 419 Using Validation Tables to Support Business Rules 420 Reviewing the Business Rule Specifications Sheets 425 Case Study 426 Summary 431 Review Questions 434   Chapter 12: Views 435 Topics Covered in This Chapter 435 What Are Views? 435 Anatomy of a View 437 Data View 437 Aggregate View 442 Validation View 446 Determining and Defining Views 448 Working with Users and Management 449 Defining Views 450 Reviewing the Documentation for Each View 458 Case Study 460 Summary 465 Review Questions 466   Chapter 13: Reviewing Data Integrity 469 Topics Covered in This Chapter 469 Why You Should Review Data Integrity 470 Reviewing and Refining Data Integrity 470 Table-Level Integrity 471 Field-Level Integrity 471 Relationship-Level Integrity 472 Business Rules 472 Views 473 Assembling the Database Documentation 473 Done at Last! 475 Case Study—Wrap-Up 475 Summary 476   Part III: Other Database Design Issues 477   Chapter 14: Bad Design—What Not to Do 479 Topics Covered in This Chapter 479 Flat-File Design 480 Spreadsheet Design 481 Dealing with the Spreadsheet View Mind-set 483 Database Design Based on the Database Software 485 A Final Thought 486 Summary 487   Chapter 15: Bending or Breaking the Rules 489 Topics Covered in This Chapter 489 When May You Bend or Break the Rules? 489 Designing an Analytical Database 489 Improving Processing Performance 490 Documenting Your Actions 493 Summary 495   In Closing 497   Part IV: Appendixes 499   Appendix A: Answers to Review Questions 501 Chapter 1 501 Chapter 2 502 Chapter 3 504 Chapter 4 505 Chapter 5 506 Chapter 6 508 Chapter 7 510 Chapter 8 513 Chapter 9 516 Chapter 10 518 Chapter 11 520 Chapter 12 521   Appendix B: Diagram of the Database Design Process 525   Appendix C: Design Guidelines 543 Defining and Establishing Field-Specific Business Rules 543 Defining and Establishing Relationship-Specific Business Rules 543 Elements of a Candidate Key 544 Elements of a Foreign Key 544 Elements of a Primary Key 545 Rules for Establishing a Primary Key 545 Elements of the Ideal Field 545 Elements of the Ideal Table 546 Field-Level Integrity 546 Guidelines for Composing a Field Description 547 Guidelines for Composing a Table Description 547 Guidelines for Creating Field Names 548 Guidelines for Creating Table Names 548 Identifying Relationships 549 Identifying View Requirements 549 Interview Guidelines 550 Participant Guidelines 550 Interviewer Guidelines 550 Mission Statements 551 Mission Objectives 551 Relationship-Level Integrity 551 Resolving a Multivalued Field 552 Table-Level Integrity 552   Appendix D: Documentation Forms 553   Appendix E: Database Design Diagram Symbols 557   Appendix F: Sample Designs 559   Appendix G: On Normalization 567 Please Note . . . 568 A Brief Recap 569 How Normalization Is Integrated into My Design Methodology 572 Logical Design versus Physical Design and Implementation 575   Appendix H: Recommended Reading 577   Glossary 579 References 595 Index 597