The Barnes & Noble Review
Time was, all database design books were packed with mathematics and jargon. You’d be up to your neck in normal forms before you even had a chance to wade. When Michael J. Hernandez needed a database design book to teach “mere mortals” like himself, there were none. So he began a personal quest to learn enough to write one. And he did.
Now in its Second Edition, Database Design for Mere Mortals is a miracle for today’s generation of database users who don’t have the background -- or the time -- to learn database design the hard way. It’s also a secret pleasure for working pros who are occasionally still trying to figure out what they were taught.
Drawing on 13 years of database teaching experience, Hernandez has organized database design into several key principles that are surprisingly easy to understand and remember. He illuminates those principles using examples that are generic enough to help you with virtually any application.
Hernandez’s goals are simple. You’ll learn how to create a sound database structure as easily as possible. You’ll learn how to optimize your structure for efficiency and data integrity. You’ll learn how to avoid problems like missing, incorrect, mismatched, or inaccurate data. You’ll learn how to relate tables together to make it possible to get whatever answers you need in the future -- even if you haven’t thought of the questions yet.
If -- as is often the case -- you already have a database, Hernandez explains how to analyze it -- and leverage it. You’ll learn how to identify new information requirements, determine new business rules that need to be applied, and apply them.
Hernandez starts with an introduction to databases, relational databases, and the idea and objectives of database design. Next, you’ll walk through the key elements of the database design process: establishing table structures and relationships, assigning primary keys, setting field specifications, and setting up views. Hernandez’s extensive coverage of data integrity includes a full chapter on establishing business rules and using validation tables.
Hernandez surveys bad design techniques in a chapter on what not to do -- and finally, helps you identify those rare instances when it makes sense to bend or even break the conventional rules of database design.
There’s plenty that’s new in this edition. Hernandez has gone over his text and illustrations with a fine-tooth comb to improve their already impressive clarity. You’ll find updates to reflect new advances in technology, including web database applications. There are expanded and improved discussions of nulls and many-to-many relationships; multivalued fields; primary keys; and SQL data type fields. There’s a new Quick Reference database design flowchart. A new glossary. New review questions at the end of every chapter.
Finally, it’s worth mentioning what this book isn’t. It isn’t a guide to any specific database platform -- so you can use it whether you’re running Access, SQL Server, or Oracle, MySQL or PostgreSQL. And it isn’t an SQL guide. (If that’s what you need, Michael J. Hernandez has also coauthored the superb SQL Queries for Mere Mortals). But if database design is what you need to learn, this book’s worth its weight in gold. Bill Camarda
Bill Camarda is a consultant, writer, and web/multimedia content developer. His 15 books include Special Edition Using Word 2000 and Upgrading & Fixing Networks for Dummies, Second Edition.
Read an Excerpt
Chapter 11: Business Rule
Reviewing the Business Rule Specification Sheets
Once you've established the Business Rules you believe to be appropriate, review their specification sheets. Carefully examine the specification sheet and make certain that the rule has been properly established and that all the appropriate areas on the sheet are clearly marked. If you find an error, make the necessary modifications and review it once more. Repeat this process until you've reviewed every Business Rule.
Business Rules are an important component of the database. Along with contributing to overall data integrity, Business Rules impose integrity constraints that are specific to the organization. As you've seen, these rules help to ensure the validity and consistency of the data within the context of the manner in which the organization functions or conducts its business. Furthermore, these rules will affect the manner in which the database is implemented in an RDBMS and how it works with the application program used to work with the database.
It's important to understand that you will revisit these rules quite often. As you review the final structure, for example, you may determine that additional Business Rules are necessary. You may discover that several of the rules will not provide the results you had initially envisioned, and thus they need to be modified. It's also possible to determine that some of the rules aren't necessary after all. (In this instance, be absolutely sure to examine the rules carefully before you remove them.)
Keep in mind that the Business Rules you define now are bound to require modifications in the future; you will most likely need to add Business Rules in due course because of changes in the way the organization functions or conducts its business. The need to modify existing Business Rules or develop new ones comes up naturally-the organization inevitably grows and matures, and so does the manner in which it acts upon or reacts to external forces. These forces affect the manner in which the organization perceives and uses its data, which, in turn, changes the nature of the organization's requirements in terms of Business Rules.
The task of defining and establishing Business Rules is-as are so many other tasks within the database design process-ongoing. Don't be discouraged if you have to perform this task several times. Your efforts will pay great dividends in the long run.
Now it's time to establish Business Rules for Mike's database. You schedule a meeting with Mike and his staff to review the tables and relationships in their database. The first order of business is to define and establish field-specific Business Rules.
You begin the process by reviewing the PRODUCTS table. As you begin to examine each field, you try to determine whether any constraints are required. Upon reviewing the CATEGORY field, you remember that there was some question as to the values that could be entered into the field. (Refer to the Case Study in Chapter 9.) After some discussion with Mike and his staff, you compile a list of categories. Then Mike decides that the values for the CATEGORY field should be limited to those on this list to make certain that the staff does not arbitrarily invent new categories. Because Mike wants to limit the values that can be entered in the CATEGORY field, you define an appropriate Business Rule to properly establish the constraint. The new rule is stated as follows:
"Invalid product categories are not allowed." Because there are a number of items in the list of possible categories, you decide that the best way to establish this rule is to create and use a CATEGORIES validation table. So you create the table and establish a relationship between it and the PRODUCTS table. After you establish the relationship, you make the appropriate settings to its characteristics. The new validation table, relationship, and its table relationship characteristics are shown in the diagram depicted in Figure 11-18.
These are the settings you've made for the table relationship characteristics:
- There is a restrict deletion rule for the relationship.
- The type of participation for the CATEGORIES table is set to "Mandatory."
- The type of participation for the PRODUCTS table is set to "Optional."
- The degree of participation for CATEGORIES is set to "(1,1)."
- The degree of participation for PRODUCTS is set to "(O,N)."
Remember that by establishing this relationship, you've replaced the existing CATEGORY field in the PRODUCTS table with a copy of the CATEGORY ID field from the new CATEGORIES table. Because the CATEGORY ID field in the PRODUCTS table is a Foreign key, you now make certain that it conforms to the Elements of a Foreign key and make the appropriate modifications to its Field Specifications. Then you modify the Range of Values element to read "Any value within the CATEGORY ID field in the CATEGORIES table." Figure 11-19 shows the Logical Elements settings of the Field Specifications for the CATEGORY ID field in the PRODUCTS table.
Now you must decide when the rule should be tested. As you already know, you typically want to test a rule established with a validation table if the user attempts to insert a value into the field or update an existing value within the field.
Finally, you complete a Business Rule Specification sheet for this new Business Rule. This specification sheet will reflect the modifications you've made to the Field Specifications for the CATEGORY ID field as well as the characteristics of the relationship between the CATEGORIES and PRODUCTS tables...