Google Preview

Plain cooking cannot be entrusted to plain cooks. --Countess Morphy In the past, the process of designing a database has been a task performed by information technology (IT) personnel and professional database developers. These people usually had mathematical, computer science, or systems design backgrounds and typically worked with large mainframe databases. Many of them were experienced programmers and had coded a number of database application programs consisting of thousands of lines of code. (And these people were usually very overworked due to the nature and importance of their work!) People designing database systems at that time needed to have a solid educational background because most of the systems they created were meant to be used companywide. Even when creating databases for single departments within a company or for small businesses, database designers still required extensive formal training because of the complexity of the programming languages and database application programs that they were using. As technology advanced, however, those educational requirements evolved. Since the mid-1980s, many software vendors have developed database software programs that run on desktop computers and can be more easily programmed to collect, store, and manage data than their mainframe counterparts. They have also produced software that allows groups of people to access and share centralized data within a variety of environments, such as client/server architectures on computers connected within local-area networks (LANs) and wide-area networks (WANs), and even via the Internet. People within a company or organization are no longer strictly dependent on mainframe databases or on having their information needs met by centralized IT departments. Over the years, vendors have added new features and enhanced the tool sets in their database software, enabling database developers to create more powerful and flexible database applications. They've also improved the ease with which the software can be used, inspiring many people to create their own database applications. Today's database software greatly simplifies the process of creating efficient database structures and intuitive user interfaces. Most programs provide sample database structures that you can copy and alter to suit your specific needs. Although you might initially think that it would be quite advantageous for you to use these sample structures as the basis for a new database, you should stop and reconsider that move for a moment. Why? Because you could easily and unwittingly create an improper, inefficient, and incomplete design. Then you would eventually encounter problems in what you believed to be a dependable database design. This, of course, raises the question, "What types of problems would I encounter?" Most problems that surface in a database fall into two categories: application problems and data problems. Application problems include such things as problematic data entry/edit forms, confusing menus, confusing dialog boxes, and tedious task sequences. These problems typically arise when the database developer is inexperienced, is unfamiliar with a good application-design methodology, or knows too little about the software he's using to implement the database. Problems of this nature are common and important to address, but they are beyond the scope of this work. NOTE: One good way to solve many of your application problems is to purchase and study third-party "developer" books that cover the software you're using. Such books discuss application-design issues, advanced programming techniques, and various tips and tricks that you can use to improve and enhance an application. Armed with these new skills, you can revamp and fine-tune the database application so that it works correctly, smoothly, and efficiently. Data problems, on the other hand, include such things as missing data, incorrect data, mismatched data, and inaccurate information. Poor database design is typically the root cause of these types of problems. A database will not fulfill an organization's information requirements if it is not structured properly. Although poor design is typically generated by a database developer who lacks knowledge of good database-design principles, it shouldn't necessarily reflect negatively on the developer. Many people, including experienced programmers and database developers, have had little or no instruction in any form of database-design methodology. Many are unaware that design methodologies even exist. Data problems and poor design are the issues that this work will address. What's New in the Second Edition I revised this edition to improve readability, update or extend existing topics, add new content, and enhance its educational value. Here is a list of the changes you'll find in this edition: Much of the text has been rewritten to improve clarity and reader comprehension. Many of the figures and illustrations have been revised to improve clarity. New figures and illustrations have been added as warranted by revisions of or additons to existing text. Discussions of relational database management systems and the relational model in Chapter 1 have both been expanded to include brief content on recent technological advances and general industry direction. The premise behind the design methodology presented in this book is explained in Chapter 2. Discussion of nulls and the many-to-many relationship in Chapter 3 have both been expanded to provide greater detail on these subjects. Web-page-based examples are now included as appropriate in Chapter 6. Discussions of multivalued fields and the subset tables in Chapter 7 have both been expanded to provide greater detail on these subjects. The discussion of primary keys in Chapter 8 has been expanded to provide greater detail on this subject. The Field Specifications sheet has been updated and redesigned for improved flow and readability. The discussion of the Data Type field specification element in Chapter 9 has been expanded to include an introduction to Structured Query Language (SQL) data types. Discussions of self-referencing relationships and the Deny, Nullify, and Set Default deletion rules have been added to Chapter 10. Review questions have been added at the end of Chapters 1 through 12, and the answers to the questions appear in Appendix A. A flowchart of the design process has been provided as a quick reference tool and is included in Appendix B. All of the various design guidelines have been compiled in Appendix C. A glossary has been added to provide a quick reference for various terms used throughout the book. The accompanying CD includes files in Adobe Acrobat PDF format. These files contain the material in Appendixes B through F. Who Should Read This Book No previous background in database design is necessary to read this book. The reason you have this book in your hands is to learn how to design a database properly. If you're just getting into database management and you're thinking about developing your own databases, this book will be very valuable to you. It's better that you learn how to create a database properly from the beginning than that you learn by trial and error. The latter method takes much longer, believe me. If you fall into the category of those people who have been working with database programs for a while and are ready to begin developing new databases for your company or business, you should read this book. You probably have a good feel for what a good database structure should look like, but aren't quite sure how database developers arrive at an effective design. Maybe you're a programmer who has created a number of databases following a few basic guidelines, but you have always ended up writing a lot of code to get the database to work properly. If this is the case, this book is also for you. It would be a good idea for you to read this book even if you already have some background in database design. Perhaps you learned a design methodology back in college or attended a database class that discussed design, but your memory is vague about some details, or there were parts of the design process that you just did not completely understand. Those points with which you had difficulty will finally become clear once you learn and understand the design process presented in this book. This book is also appropriate for those of you who are experienced database developers and programmers. Although you may already know many of the aspects of the design process that are presented here, you'll probably find that there are some elements that you've never before encountered or considered. You may even come up with fresh ideas about how to design your databases by reviewing the material in this book because many of the design processes familiar to you are presented here from a different viewpoint. At the very least, this book can serve as a great refresher course in database design. NOTE: Those of you who have a burning desire to immerse yourselves in the depths of the database field (i.e., to learn the intricacies of database theory and design, analysis, implementation, administration, application development, and so on) should make a point of reading most of the books on my recommended reading list. Although I do not cover any of the aforementioned topics, my book does serve as the beginning of your journey into the realm of the database professional. The Purpose of This Book In general terms, there are three phases to the overall database-development process. Logical design: The first phase involves determining and defining tables and their fields, establishing primary and foreign keys, establishing table relationships, and determining and establishing the various levels of data integrity. Physical implementation: The second phase entails creating the tables, establishing key fields and table relationships, and using the proper tools to implement the various levels of data integrity. Application development: The third phase involves creating an application that allows a single user or group o...