CYAN YELLOW MAGENTA BLACK PANTONE 123 CV BOOKS FOR PROFESSIONALS BY PROFESSIONALS® THE EXPERT’S VOICE® Companion eBook Beginning Database Design Available Dear Reader, B e Whether you are keeping data for yourself, your business, a local club, or a g Beginning research project, you need to be confident that your data is safe and accurate, i that you will always be able to extract the information you need, and that your n database can evolve as your needs change. n Many people are surprised to find that a number of problems with their data- i Database n bases are caused by poor design rather than difficulties in using the database g management software. This book shows you how to stand back from the problem and see the broader picture. It explains how to identify potential trouble spots D so you don’t paint yourself into a corner and have to start all over again. The book is aimed at beginners, but the messages apply to designers of a databases large and small. After reading this book, you should have a good idea of how to ask important questions about your data so you can understand the t a Design problem you are trying to solve and all its little quirks. You should then be able to put together a pragmatic design that captures the essentials while leaving b the door open for refinements and extensions at a later stage. The book includes chapters on how to represent your designs in a relational database a management system and introduces the concepts of querying, indexing, and s interface design. Your data is precious. I hope after reading this book you will see how to store e it so that you can make the best use of it without avoidable mistakes, which will cost you both in time and money. D Clare Churcher e From Novice to Professional s THE APRESS ROADMAP i g Beginning Applied Mathematics for Date on Database: Database Design Database Professionals Writings 2000–2006 n Designing databases for the desktop and beyond Companion eBook Beginning PHP Excel As Beginning PHP and MySQL 5 Your Database and PostgreSQL 8 Building Database-Driven Beginning SQL Server Flash Applications 2005 Express See last page for details on $10 eBook version C Clare Churcher h ISBN-13: 978-1-59059-769-9 u www.apress.com ISBN-10: 1-59059-769-9 rc 53499 h Foreword by Stéphane Faroult e US $34.99 r Shelve in Databases User level: 9 781590 597699 Beginner this print for content only—size & color not accurate spine = 0.638" 272 page count 7699FM.qxd 12/12/06 7:56 PM Page i Beginning Database Design Clare Churcher 7699FM.qxd 12/12/06 7:56 PM Page ii Beginning Database Design Copyright © 2007 by Clare Churcher All rights reserved. No part of this work may be reproduced or transmitted in any form or by any means, electronic or mechanical, including photocopying, recording, or by any information storage or retrieval system, without the prior written permission of the copyright owner and the publisher. ISBN-13 (pbk): 978-1-59059-769-9 ISBN-10 (pbk): 1-59059-769-9 Printed and bound in the United States of America 9 8 7 6 5 4 3 2 1 Trademarked names may appear in this book. Rather than use a trademark symbol with every occurrence of a trademarked name, we use the names only in an editorial fashion and to the benefit of the trademark owner, with no intention of infringement of the trademark. Lead Editor: Jonathan Gennick Technical Reviewer: Stéphane Faroult Editorial Board: Steve Anglin, Ewan Buckingham, Gary Cornell, Jason Gilmore, Jonathan Gennick, Jonathan Hassell, James Huddleston, Chris Mills, Matthew Moodie, Dominic Shakeshaft, Jim Sumser, Keir Thomas, Matt Wade Project Manager: Richard Dal Porto Copy Edit Manager: Nicole Flores Copy Editor: Ami Knox Assistant Production Director: Kari Brooks-Copony Production Editor: Kelly Gunther Compositor: Gina Rexrode Proofreader: Elizabeth Berry Indexer: John Collin Artist: April Milne Cover Designer: Kurt Krames Manufacturing Director: Tom Debolski Distributed to the book trade worldwide by Springer-Verlag New York, Inc., 233 Spring Street, 6th Floor, New York, NY 10013. Phone 1-800-SPRINGER, fax 201-348-4505, e-mail [email protected], or visit http://www.springeronline.com. For information on translations, please contact Apress directly at 2560 Ninth Street, Suite 219, Berkeley, CA 94710. Phone 510-549-5930, fax 510-549-5939, e-mail [email protected], or visit http://www.apress.com. The information in this book is distributed on an “as is” basis, without warranty. Although every precau- tion has been taken in the preparation of this work, neither the author(s) nor Apress shall have any liability to any person or entity with respect to any loss or damage caused or alleged to be caused directly or indirectly by the information contained in this work. 7699FM.qxd 12/12/06 7:56 PM Page iii To Neville 7699FM.qxd 12/12/06 7:56 PM Page iv 7699FM.qxd 12/12/06 7:56 PM Page v Contents at a Glance Foreword. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xiii About the Author . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xv About the Technical Reviewer . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xvii Acknowledgments . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xix Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxi ■ CHAPTER 1 What Can Go Wrong. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1 ■ CHAPTER 2 Guided Tour of the Development Process. . . . . . . . . . . . . . . . . . . . . . . 11 ■ CHAPTER 3 Initial Requirements and Use Cases. . . . . . . . . . . . . . . . . . . . . . . . . . . . 31 ■ CHAPTER 4 Learning from the Data Model. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 53 ■ CHAPTER 5 Developing a Data Model . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 75 ■ CHAPTER 6 Generalization and Specialization . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 95 ■ CHAPTER 7 From Data Model to Relational Schema. . . . . . . . . . . . . . . . . . . . . . . . 113 ■ CHAPTER 8 Normalization . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 139 ■ CHAPTER 9 More on Keys and Constraints . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 157 ■ CHAPTER 10 Queries. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 171 ■ CHAPTER 11 User Interface . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 191 ■ CHAPTER 12 Other Implementations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 205 ■ CONCLUSION. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 225 ■ INDEX . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 229 v 7699FM.qxd 12/12/06 7:56 PM Page vi 7699FM.qxd 12/12/06 7:56 PM Page vii Contents Foreword. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xiii About the Author . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xv About the Technical Reviewer . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xvii Acknowledgments . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xix Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxi ■ CHAPTER 1 What Can Go Wrong. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1 Mishandling Keywords and Categories. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1 Repeated Information. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5 Designing for a Single Report. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8 Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9 ■ CHAPTER 2 Guided Tour of the Development Process . . . . . . . . . . . . . . . . . 11 Initial Problem Statement. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12 Analysis and Simple Data Model . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 14 Classes and Objects . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15 Relationships . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 16 Further Analysis:Revisiting the Use Cases. . . . . . . . . . . . . . . . . . . . . . . . . . 19 Design. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 23 Implementation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 24 Interfaces for Input Use Cases. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25 Reports for Output Use Cases. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 26 Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 28 ■ CHAPTER 3 Initial Requirements and Use Cases. . . . . . . . . . . . . . . . . . . . . . . 31 Real and Abstract Views of a Problem. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 33 Data Minding . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 34 Task Automation. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 34 vii 7699FM.qxd 12/12/06 7:56 PM Page viii viii ■ CONTENTS What Does the User Do? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 36 What Data Is Involved?. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 37 What Is the Objective of the System? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 38 What Data Is Required to Satisfy the Objective?. . . . . . . . . . . . . . . . . . . . . 40 What Are the Input Use Cases?. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 42 What Is the First Data Model?. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 44 What Are the Output Use Cases? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 45 More About Use Cases. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 47 Actors. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 47 Exceptions and Extensions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 48 Use Cases for Maintaining Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 48 Use Cases for Reporting Information. . . . . . . . . . . . . . . . . . . . . . . . . . 49 Finding Out More About the Problem. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 49 What Have We Postponed? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 50 Changing Prices. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 50 Meals That Are Discontinued. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 50 Quantities of Particular Meals. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 51 Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 51 ■ CHAPTER 4 Learning from the Data Model . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 53 Review of Data Models. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 54 Optionality:Should It Be 0 or 1?. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 57 Student Course Example . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 57 Customer Order Example. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 58 Insect Example. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 59 A Cardinality of 1:Might It Occasionally Be Two? . . . . . . . . . . . . . . . . . . . . 60 Insect Example. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 60 Sports Club Example. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 62 A Cardinality of 1:What About Historical Data?. . . . . . . . . . . . . . . . . . . . . . 63 Sports Club Example. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 63 Departments Example. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 64 Insect Example. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 65 7699FM.qxd 12/12/06 7:56 PM Page ix ■ CONTENTS ix A Many–Many:Are We Missing Anything? . . . . . . . . . . . . . . . . . . . . . . . . . . 66 Sports Club Example. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 67 Student Course Example . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 69 Meal Delivery Example. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 70 When a Many–Many Doesn’t Need an Intermediate Class. . . . . . . . 72 Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 72 ■ CHAPTER 5 Developing a Data Model. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 75 Attribute,Class,or Relationship? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 75 Two or More Relationships Between Classes. . . . . . . . . . . . . . . . . . . . . . . . 78 Different Routes Between Classes. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 81 Redundant Information. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 81 Routes Providing Different Information . . . . . . . . . . . . . . . . . . . . . . . . 83 False Information from a Route (Fan Trap) . . . . . . . . . . . . . . . . . . . . . 84 Gaps in a Route Between Classes (Chasm Trap) . . . . . . . . . . . . . . . . 85 Relationships Between Objects of the Same Class. . . . . . . . . . . . . . . . . . . 87 Relationships Involving More Than Two Classes . . . . . . . . . . . . . . . . . . . . . 89 Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 92 ■ CHAPTER 6 Generalization and Specialization . . . . . . . . . . . . . . . . . . . . . . . . . 95 Classes or Objects with Much in Common. . . . . . . . . . . . . . . . . . . . . . . . . . 95 Specialization. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 97 Generalization . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 98 Inheritance in Summary. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 100 When Inheritance Is Not a Good Idea. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 102 Confusing Objects with Subclasses . . . . . . . . . . . . . . . . . . . . . . . . . . 102 Confusing an Association with a Subclass . . . . . . . . . . . . . . . . . . . . 103 When Is Inheritance Worth Considering?. . . . . . . . . . . . . . . . . . . . . . . . . . . 104 Should the Superclass Have Objects?. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 105 Objects That Belong to More Than One Subclass . . . . . . . . . . . . . . . . . . . 107 It Isn’t Easy. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 110 Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 111