Database Design for Mere Mortals: A Hands-On Guide to Relational Database Design / Edition 2

Paperback (Print)
Rent from
(Save 59%)
Est. Return Date: 07/25/2015
Buy New
Buy New from
Used and New from Other Sellers
Used and New from Other Sellers
from $1.99
Usually ships in 1-2 business days
(Save 97%)
Other sellers (Paperback)
  • All (36) from $1.99   
  • New (6) from $47.48   
  • Used (30) from $1.99   


Praise for Database Design for Mere Mortals™, Second Edition:

"If you told me that Mike Hernandez could improve on the first edition of Database Design for Mere Mortals, I wouldn't have believed you, but he did! The second edition is packed with more real-world examples, detailed explanations, and even includes database design tools on the CD-ROM! This is a must-read for anyone who is even remotely interested in relational database design, from the individual who is called upon occasionally to create a useful tool at work, to the seasoned professional who wants to brush up on the fundamentals. Simply put, if you want to do it right, read this book!"
--Matt Greer, Process Control Development, The Dow Chemical Company

"Mike has always had an incredible knack for taking the most complex topics, breaking them down, and explaining them so that anyone can 'get it'."
--John Viescas, President, Viescas Consulting, Inc.
Author of Running Microsoft Access 2000 and coauthor of SQL Queries for Mere Mortals

"Whether you need to learn about relational database design in general, design a relational database, understand relational database terminology, or learn best practices for implementing a relational database, Database Design for Mere Mortals™, Second Edition, is an indispensable book that you'll refer to often."
--Paul Cornell, Site Editor, MSDN Office Developer Center

"The second edition expands and improves on the original in so many ways. It is not only a good, clear read, but contains a remarkable quantity of clear, concise thinking on a very complex subject."

--Malcolm C. Rubel
Performance Dynamics Associates

"Database Design for Mere Mortals™, Second Edition, helps you design your database right in the first place!"
--Matt Nunn, Product Manager, SQL Server, Microsoft Corporation

Sound database design can save hours of development time and ensure functionality and reliability. Database Design for Mere Mortals™, Second Edition, is a straightforward, platform-independent tutorial on the basic principles of relational database design. It provides a commonsense design methodology for developing databases that work.

Database design expert Michael J. Hernandez has expanded his best-selling first edition, maintaining its hands-on approach and accessibility while updating its coverage and including even more examples and illustrations.

This edition features a CD-ROM that includes diagrams of sample databases, as well as design guidelines, documentation forms, and examples of the database design process.

This book will give you the knowledge and tools you need to create efficient and effective relational databases.

Read More Show Less

Editorial Reviews

From Barnes & Noble
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 More Show Less

Product Details

  • ISBN-13: 9780201752847
  • Publisher: Addison-Wesley
  • Publication date: 3/7/2003
  • Series: For Mere Mortals Series
  • Edition description: Revised with CD-ROM
  • Edition number: 2
  • Pages: 611
  • Sales rank: 510,896
  • Product dimensions: 7.37 (w) x 9.25 (h) x 1.39 (d)

Meet the Author

Michael J. Hernandez is an independent consultant and trainer specializing in relational databases. He has been a featured speaker at a variety of database conferences and is a top-rated Access instructor for Application Developer's Training Company.
Read More Show Less

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.

Case Study

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...

Read More Show Less

Table of Contents



1. What Is a Relational Database.
Types of Databases.
Early Database Models.
The Hierarchical Database Model.
The Network Database Model.

The Relational Database Model.
Retrieving Data.
Advantages of a Relational Database.

Relational Database Management Systems.
Beyond the Relational Model.
What the Future Holds.
A Final Note.

Review Questions.

2. Design Objectives.
Why Should You Be Concerned with Database Design?
The Importance of Theory.
The Advantage of Learning A Good Design Methodology.
Objectives of Good Design.
Benefits of Good Design.
Database Design Methods.
Traditional Design Methods.
The Design Method Presented in This Book.

Review Questions.

3. Terminology.
Why This Terminology Is Important.
Value-related Terms.

Structure-related Terms.

Relationship-related Terms.
Types of Relationships.
Types of Participation.
Degree of Participation.

Integrity-related Terms.
Field Specification.
Data Integrity.

Review Questions.


4. Conceptual Overview.
The Importance of Completing the Design Process.
Define a Mission Statement and Mission Objectives.
Analyze the Current Database.
Create the Data Structures.
Determine and Establish Table Relationships.
Determine and Define Business Rules.
Determine and Define Views.
Review Data Integrity.
Review Questions.

5. Starting the Process.
Conducting Interviews.
Participant Guidelines.
Interviewer Guidelines.

The Case Study: Mike's Bikes.
Defining the Mission Statement.
The Well-written Mission Statement.
Composing a Mission Statement.
Case Study.

Defining the Mission Objectives.
Well-written Mission Objectives.
Composing Mission Objectives.
Case Study.

Review Questions.

6. Analyzing the Current Database.
Getting to Know the Current Database.
Paper-based Databases.
Legacy Databases.

Conducting the Analysis.
Looking at How Data Is Collected.
Looking at How Information Is Presented.
Conducting Interviews.
Basic Interview Techniques.
Before You Begin the Interview Process.

Interviewing Users.
Reviewing Data Type and Usage.
Reviewing the Samples.
Reviewing Information Requirements.

Interviewing Management.
Reviewing Current Information Requirements.
Reviewing Additional Information Requirements.
Reviewing Future Information Requirements.
Reviewing Overall Information Requirements.

Compiling a Complete List of Fields.
The Preliminary Field List.
The Calculated Field List.
Reviewing Both Lists with Users and Management.

Case Study.
Review Questions.

7. Establishing Table Structures.
Defining the Preliminary Table List.
Identifying Implied Subjects.
Using the List of Subjects.
Using the Mission Objectives.

Defining the Final Table List.
Refining the Table Names.
Indicating the Table Types.
Composing the Table Descriptions.

Associating Fields with Each Table.
Refining the Fields.
Improving the Field Names.
Using the Ideal Field to Resolve Anomalies.
Resolving Mulit-part Fields.
Resolving Multi-valued Fields.

Refining the Table Structures.
A Word about Redundant Data and Duplicate Fields.
Using the Ideal Table to Refine Table Structures.
Establishing Subset Tables.

Case Study.
Review Questions.

8. Keys.
Why Keys Are Important.
Establishing Keys for Each Table.
Candidate Keys.
Primary Keys.
Alternate Keys.
Table-Level Integrity.

Reviewing the Initial Table Structures.
Case Study.
Review Questions.

9. Field Specifications.
Why Field Specifications Are Important.
Field-Level Integrity.
Anatomy of a Field Specification.
General Elements.
Physical Elements.
Logical Elements.

Using Unique, Generic, and Replica Field Specifications.
Defining Field Specifications for Each Field in the Database.
Case Study.
Review Questions.

10. Table Relationships.
Types of Relationships.
One-to-One Relationships.
One-to-Many Relationships.
Many-to-Many Relationships.
Self-referencing Relationships.

Identifying Existing Relationships.
Establishing Each Relationship.
One-to-One and One-to-Many Relationships.
Many-to-Many Relationships.
Self-referencing Relationships.
Reviewing the Structure of Each Table.

Refining All Foreign Keys.
Elements of a Foreign Key.

Establishing Relationship Characteristics.
Defining a Deletion Rule for Each Relationship.
Identifying the Type of Participation for Each Table.
Identifying the Degree of Participation for Each Table.
Verifying Table Relationships with Users and Management.
A Final Note.

Relationship-Level Integrity.
Case Study.
Review Questions. Chapter 11: Business Rules.
What Are Business Rules?
Types of Business Rules.

Categories of Business Rules.
Field-specific Business Rules.
Relationship-specific Business Rules.

Defining and Establishing Business Rules.
Working with Users and Management.
Defining and Establishing Field-Specific Business Rules.
Defining and Establishing Relationship-Specific Business Rules.

Validation Tables.
What Are Validation Tables?
Using Validation Tables to Support Business Rules.

Reviewing the Business Rule Specification Sheets.
Case Study.
Review Questions.

What Are Views?
Anatomy of a View.
Data View.
Aggregate View.
Validation View.

Determining and Defining Views.
Working with Users and Management.
Defining Views.
Reviewing the Documentation for Each View.

Case Study.
Review Questions.

13. Reviewing Data Integrity.
Why You Should Review Data Integrity.
Reviewing and Refining Data Integrity.
At the Table Level.
At the Field Level.
At the Relationship Level.
At the Level of Business Rules.
At the Level of Views.

Assembling the Database Documentation.
Done at Last!
Case Study—Wrap up.


14. Bad Design—What Not To Do.
Flat-File Design.
Spreadsheet Design.
Dealing with the Spreadsheet View Mind-set.

Database Design Based on Database Software.
A Final Thought.

15. Bending or Breaking the Rules.
When May You Bend or Break the Rules?
Designing an Analytical Database.
Improving Processing Performance.

Documenting Your Actions.

In Closing.


Appendix A: Answers to Review Questions.
Appendix B: Diagram of the Database Design Process.
Appendix C: Design Guidelines.
Appendix D: Documentation Forms.
Appendix E: Database Design Diagram Symbols.
Appendix F: Sample Designs.
Appendix G: Recommended Reading.
Read More Show Less

First Chapter

Chapter 10:Table Relationships

There is no substitute for the comfort supplied by the utterly taken-for-granted relationship.

-Iris Murdoch

  • Topics Covered in This Chapter
  • Types of Relationships
  • Identifying Existing Relationships
  • Establishing Each Relationship
  • Establishing Relationship Characteristics
  • Relationship-Level Integrity
  • Case Study
  • Summary

As you learned in Chapter 3, a relationship is a connection between a pair of tables. A relationship exists when the tables are connected by a Primary key and a Foreign key, or are linked together by a linking table; the manner in which the tables are connected depends on the type of relationship that exists between them.

A relationship is a crucial part of the database because

  • it establishes a connection between a pair of tables that are logically related to each other in some form or manner The logical relationship exists between the data contained in the tables. For example, a logical relationship exists between the data in a CUSTOMERS table and the data in an ORDERS table. Because a customer places an order for a specific piece of merchandise, a record in the CUSTOMERS table (representing the customer) is related to a record in the ORDERS table (representing a particular order.
  • it helps to further refine table structures and minimize redundant data. These benefits come about as a result of the manner in which the tables are connected. The process used to establish the relationship modifies the table structures in a manner that makes them more efficient.
  • it is the mechanism that allows data from multiple tables to be drawn together simultaneously. In Chapter 12 you'll learn that fields from related tables can be combined into a View, which allows you to view (and, in some cases, modify) the data as if it were in a single table.
  • Relationship-level integrity is established when a relationship is properly defined. Establishing relationship-level integrity guarantees that the relationships are reliable and sound. And, as you know, relationship-level integrity is a component of overall data integrity.

In order to take advantage of the many benefits provided by a relational database, you must make certain that you establish each relationship carefully and properly. Failure to do so can make working with multiple tables at the same time difficult, and it can also make inserting, updating, and deleting records in related tables difficult. These types of problems are discussed later as the design process unfolds.

Types of Relationships

Before you begin to establish relationships between tables in the database, you must learn what types of relationships can exist between a pair of tables. Two tables that are related to each other have a specific type of relationship. Knowing how to properly identify each type of relationship is invaluable for designing a successful database.

As you learned in Chapter 3, there are three possible types of relationships that can exist between a pair of tables: one-to-one, one-to-many, and many-to-many.

One-to-One Relationships

A pair of tables are defined as bearing a one-to-one relationship if a single record in the first table is related to one and only one record in the second table, and a single record in the second table is related to one and only one record in the first table. Figure 10-2 shows a generic example of a one-to-one relationship.

In Figure 10-2, a single record in TABLE A is related to only one record in TABLE B, and a single record in TABLE B is related to only one record in TABLE A. A one-to-one relationship usually (but not always) involves a subset table. For the sake of example, assume that TABLE B is the subset table. Figure 10-3 shows how to diagram a one-to-one relationship. In a relationship diagram, the type of relationship is indicated by the type of line drawn between the tables. Later in this chapter, you'll learn how to indicate the characteristics of the relationship on the diagram as well.

One-to-Many Relationships

A one-to-many relationship is defined as one in which a single record in the first table can be related to one or more records in the second table, but a single record in the second table can be related to only one record in the first table. Two diagrams are needed to illustrate this type of relationship.

Say you're working with two tables, TABLE A and TABLE B. A one-to-many relationship exists between these tables because a single record in TABLE A can be related to one or more (but not necessarily all) records in TABLE B, and a single record in the TABLE B can be related to only one recordin TABLE A.

This is the most common relationship found between a pair of tables, and it is the easiest to identify. Figure 10-6 shows how to diagram a one-to-many relationship.

Many-to-Many Relationships

A many-to-many relationship exists between a pair of tables if a single record in the first table can be related to one or more records in the second table, and a single record in the second table can be related to one or more records in the first table.

Assume once again that you're working with TABLE A and TABLE B. A many-to-many relationship exists between these tables because a single record in TABLE A can be related to one or more records (but not necessarily all) in TABLE B, and a single record in TABLE B can be related to one or more (but not necessarily all) records in TABLE A.

Figure 10-9 shows how to diagram a many-to-many relationship.

Problems with Many-to-Many Relationships

Before you can use the data from the tables involved in a many-to-many relationship you must resolve a few problems. If the relationship is not properly established,

  • one of the tables involved in the relationship will contain a large amount of redundant data. You would have to introduce duplicate fields into one of the tables (which, as you know, results in producing redundant data) to establish this relationship, unless you know the proper procedure.
  • both tables will contain some amount of duplicate data because of the redundancies. If you introduce duplicate fields into one of the tables, you get duplicate data in both tables.
  • it will be difficult to insert, update, and delete data in the participating tables. When you introduce duplicate fields, the same data (in the duplicate fields) has to be maintained in both tables, rather than in one table.

In order to illustrate these problems, consider the table structures in Figure 10-10. There is a many-to-many relationship between the STUDENTS table and the CLASSES table-one student can attend many classes, and one class can be associated with many students.

Currently there is no actual connection between the two tables. Unless you know how to properly establish a many-to-many relationship, you would probably be inclined to add the CLASS ID, CLASS NAME, and INSTRUCTOR ID fields from the CLASSES table to the STUDENTS table in order to identify the classes in which a student is currently enrolled. But you will see the problems that arise from such modifications if you "load" the revised STUDENTS table with sample data. Figure 10-11 shows the "amalgamated" version of this table loaded with sample data.

The problems with an improperly established many-to-many relationship are well-illustrated in Figure 10-11:

  • There are unnecessary duplicate fields. It is very likely that the CLASS NAME and INSTRUCTOR ID fields are not appropriate in the STUDENTS table-the CLASS ID field identifies the class sufficiently, and it can be used effectively to identify the classes a student is taking.
  • There is a large amount of redundant data. Even if the CLASS NAME and INSTRUCTOR ID fields are removed from the STUDENTS table, the presence of the CLASS ID field still produces a lot of redundant data.
  • It is difficult to insert a new record. If someone tries to enter a record in the STUDENTS table for a new class (instead of entering it in the CLASSES table) without also entering student data, the fields pertaining to the student would be Null-including the Primary key of the STUDENTS table. As you know, this would automatically violate the Elements of a Primary key; a Primary key cannot be Null.
  • It is difficult to modify the value of a duplicate field. It would be difficult to modify a value in the CLASS NAME field for two reasons: you have to be sure to make the modification in both tables, and you have to scan the values of the CLASS NAME field carefully in order to make certain that you make the modification to all the appropriate values. It's very common for values in a field such as this to be entered in several ways, thus making any modification to the values more difficult. For example, note the difficulty you would have if it were necessary to modify the name of the "Advanced Music Theory" class or the "Introduction to Political Science" class.
  • It is difficult to delete a record. This is especially true if the only data about a new class has been recorded in the particular student record you want to delete. Note the record for Gregory Piercy, for example. If Greg decides not to attend any classes this year and you delete his record, you will lose the data for the "Intro. to Database Design" class. That might not create a serious problem-unless someone neglected to enter the data about this class into the CLASSES table as well. Once you deleted Greg's record, that would mean you'd have to reenter all of the data for the class in the CLASSES table.

Fortunately, you will not have to worry about these problems: you're going to learn the proper way to establish a many-to-many relationship.

Establishing relationships always involves a three-step procedure: identifying the relationships that currently exist between the tables in the database, establishing each relationship in the appropriate manner, and then setting the proper characteristics for each relationship. When the relationships are in place, the tables are ready to be incorporated into Views. You'll learn how to define and use a View later, in Chapter 12.

Identifying Existing Relationships

When you were composing the table descriptions earlier in the database design process (back in Chapter 7, to be exact), you assembled a representative group of users and management to help you with that task. These people were also designated to represent the organization to help with decision making for the remainder of the database design process. At this stage you'll arrange for further meetings with this group to help you identify table relationships.

These people can provide valuable input because they can be expected to have a good perspective of how various subjects (or tables) are related. Although their perceptions of the manner in which these subjects are related may not always be complete or accurate, their contributions will still be useful in identifying most of the relationships. Relationships that are harder to identify can usually be clarified by "loading" given tables with some sample data; the sample data commonly reveals the type of relationship two tables should have.

In the first step of the procedure, you'll identify the relationships that currently exist between the tables. Begin this step by taking a particular table-you can choose a table at random because you'll repeat this process for every table anyway-and determine whether it has a relationship with any of the remaining tables. For example, assume you're working with these tables:

buildings rooms
classes staff
faculty students

Say you've decided to start with the CLASSES table. Make up a two-column list, writing "Classes" in the first column and writing the names of the remaining tables in the second column, as shown in Figure 10-1.

Now determine whether the CLASSES table bears a relationship with any of the other tables in the list by using the process of elimination. You're only looking for direct relationships; There must be a specific connection between tables participating in a direct relationship. Tables that are indirectly related will be implicitly connected through a third table. (You don't have to worry about indirect relationships yet.)

Eliminate a pair of tables on the list by asking the participants questions about the records in each table. You want to determine the relationship between a single record in one table to one or more records in the other table, and vice versa. (Remember that each record represents a single instance of the subject represented by the table.) There are two types of questions you can ask:

Associative. General in nature, this type of question can be generically stated as follows: "Can a single record in (name of first table) be associated with one or more records in (name
of second table)?" For the example in Figure 10-12, you might use an associative question such as

"Can a single record in CLASSES be associated with one or more records in BUILDINGS?"

Contextual. This type of question contrasts a single instance of the subject represented by the first table against multiple instances of the subject represented by the second table. There are two categories within this type of question: ownership oriented and action oriented.

Ownership-oriented questions are characterized by the use of words or phrases such as "owns," "has," "is part of," and "contains." (An example of this type of question is "Can a single order contain one or more products?") Action-oriented questions use action verbs such as "make," "visit," "place," "teach," and "attend." (An example of this type of question is "Does a single flight instructor teach one or more types of classes?")

Use the type of question you believe to be the most appropriate for the pair of tables you're working with. After you've chosen the type of question you want to use, pose the question twice-once from the perspective of the first table, and then again from the perspective of the second table. The answers to both of these questions will identify the type of relationship that exists between the tables.

Continuing with the example, assume that this is your first question:

"Is a single class held in one or more buildings?"

The answer to this question will reveal the type of relationship that exists between these tables from the perspective of the CLASSES table. If the answer is

"A single class is held in only one building."

then a one-to-one relationship exists between these tables. If the answer is

"A single class may be held in more than one building."

then there exists a one-to-many relationship between the two tables.

Assume you are told that a single class is held in only one building. You now follow up with this question:

"Is a single building used to conduct one or more classes?"

The answer to this question reveals the type of relationship from the perspective of the BUILDINGS table. If the answer is yes, then a one-to-many relationship exists between these tables; if no, it's a one-to-one relationship.

Often relationships will differ from one perspective to the other. When both a one-to-one and a one-to-many relationship are identified for a particular pair of tables, the one-to-many becomes the "official" relationship for these tables, because it accurately represents the relationship from both perspectives.

Now diagram the relationship as shown in Figure 10-13 and cross out this pair of tables on the list.

Although you began asking about relationships from the perspective of the CLASSES table, the relationship you discovered is diagrammed from the perspective of the BUILDINGS table. Always diagram one-to-many relationships from left to right-your diagrams will be consistent, and they'll be easier to read in the long run. Also note that each table's Primary key has been added to the diagram. From now on, make certain that each time you diagram a table you include its Primary key. (As you'll see in the following step, the Primary key is useful when you are establishing relationships.)

Continue this procedure of identifying relationships for each pair of tables on the list. Keep in mind that some pairs of tables will have a direct relationship; others will not. You want to identify only direct relationships. After you've crossed out each pair of tables on the list, select another table, create a new list, and repeat this process. When you've finished identifying all the appropriate relationships, move on to the next step.

Establishing Each Relationship

This step involves defining a connection between the tables for each relationship. The manner in which you define the connection is determined by the type of relationship that exists between the tables.

One-to-One and One-to-Many Relationships

One-to-one and one-to-many relationships are established by using a Primary key and a Foreign key to connect the tables within the relationship. You'll learn the definition of a Foreign key in just a moment.

The One-to-One Relationship

In this type of relationship, one of the tables is referred to as the "main" table and assumes a dominant role in the relationship; the other table is referred to as the "subordinate" table and assumes a subordinate role in the relationship. In some instances, you can arbitrarily decide what role each table is to play within the relationship. In the case where one of the tables is a subset table, the subset table is usually assigned the subordinate role. But there are instances in which a subset table can assume the dominant role within the relationship. As you can see, this type of one-to-one relationship exists between the STAFF and
FACULTY tables shown in Figure 10-14.

You establish a one-to-one relationship by taking a copy of the Primary key from the main table and inserting it into the subordinate table, where it becomes a Foreign key. The name "Foreign key" comes from the fact that the subordinate table has its own Primary key, and since the field being added is the Primary key of a different table, it is "foreign" to the subordinate table. As long as you can visualize this process generically, you'll be able to establish a one-to-one relationship properly for any pair of tables involved in this type of relationship.

As you can see in Figure 10-14, the Primary key of the main table (STAFF) already exists in the subordinate table (FACULTY). That is because FACULTY is already a properly defined subset table. (You learned how to define a subset table properly in Chapter 7, and you established its Primary key in Chapter 8.)

Next consider the one-to-one relationship between the two tables shown in Figure 10-15.

In this example, MANAGERS is a subset table of EMPLOYEES, but
it has a direct relationship to DEPARTMENTS. A single manager is associated with only one department, and a single department is associated with only one manager; MANAGERS is the main table, and DEPARTMENTS is the subordinate table. (This is a good example of an instance in which a subset table is in the dominant position within a relationship.) To establish the relationship between these tables, take a copy of the Primary key from the main table (MANGERS) and insert it into the subordinate table (DEPARTMENTS). Then identify the new Foreign key (Employee ID) by placing the letters "FK" next to its name. The result of these steps is shown in Figure 10-16.

Note that the relationship diagram now shows both the Primary key and the Foreign key, and that each end of the connecting line points specifically to them, making it easier to identify the relationship and the fields used to establish the connection.

The One-to-Many Relationship

The technique used to establish a one-to-many relationship is similar to the one used to establish a one-to-one relationship. In this case, you take a copy of the Primary key from the table on the "one" side of the relationship and insert it into the table on the "many" side, where that field becomes a Foreign key. For example, consider the one-to-many relationship between the BUILDINGS table and the ROOMS table shown in Figure 10-17.

In this example, a single building can contain one or more rooms, but a single room is contained within only one building. You establish this relationship by taking a copy of the Primary key from the table on the "One" side (BUILDINGS) and inserting it into the table on the "Many" side (ROOMS), where it becomes a Foreign key. Now diagram the relationship, making the proper adjustments as you did with the diagram for the one-to-one relationship. Your diagram should look like the one shown in Figure 10-18.

When you diagram a one-to-many relationship, make certain that the connection point on the "One" side points to the Primary key, and that the connection point on the "Many" side points to the Foreign key. (Note that the middle line of the "crow's-foot" is the significant connection point-it should point directly to the Foreign key.) Setting up your diagram this way makes it easier to identify the relationship and the fields used to establish the connection.

The Many-to-Many Relationship

A many-to-many relationship is established using a linking table. You create the linking table by taking a copy of the Primary key from each table involved in the relationship and using those Primary keys to create the new linking table. Next you give the linking table a meaningful name, one that represents the nature of the relationship between the two tables. Then add the linking table to the final table list and make the proper entries for "Table Type" and "Table Description." Figure 10-19 shows a diagram of a many-to-many relationship that has been established for the STUDENTS and CLASSES tables. (Note the new diagram symbol used to represent a linking table.)

There are several points to note about the results of creating this linking table:

The many-to-many relationship has been dissolved. There is no longer a direct relationship between the STUDENTS and CLASSES tables. It has been replaced by two direct one-to-many relationships: one between STUDENTS and STUDENT CLASSES and another between CLASSES and STUDENT CLASSES. In the first relationship, a single record in STUDENTS can be associated with one or more records in STUDENT CLASSES, but a single record in STUDENT CLASSES table can be associated with only one record in STUDENTS. In the second relationship, a single record in the CLASSES table can be associated with one or more records in STUDENT CLASSES table, but a single record in STUDENT CLASSES can be associated with only one record in CLASSES.

The STUDENT CLASSES linking table is assigned a Composite Primary key, composed of two fields: STUDENT ID and CLASS ID. Except in rare instances, a linking table always contains a Composite Primary key.

The STUDENT CLASSES linking table contains two Foreign keys. Each of the two fields in the linking table is a copy of a Primary key from another table. Therefore, each is a Foreign key by definition and is treated as such individually. It is only when the fields are treated as a unit that they are referred to as the Composite Primary key of the table.

The linking table helps to keep redundant data to an absolute minimum. There is no superfluous data in this table at all.
In fact, the main advantage of this table structure is that it allows you to enter as few or as many classes for a single student as is necessary. Later in the database design process, you'll learn how to create Views to draw the data from these tables together in order to present it as meaningful information.

The name of the linking table reflects the purpose of the relationship it helps establish. The data stored in the STUDENT CLASSES table represents a student and the classes in which he or she is enrolled.

As you work with many-to-many relationships, there will be instances in which you will need to add fields to the linking table in order to reduce data redundancy and further refine the table structures. For example, consider the many-to-many relationship between the ORDERS and PRODUCTS tables shown in Figure 10-20.

As you can see, this relationship is improperly established-the fields PRODUCT NUMBER, QUOTED PRICE, and QUANTITY ORDERED were inserted into the ORDERS table in order to associate various products with a particular order. But the presence of these fields in the ORDERS table produces a large amount of redundant data, as shown in Figure 10-21.

There is so much redundant data largely because only one PRODUCT NUMBER can be entered into each record. Therefore, if a customer orders eight items, eight records will have to be entered in this table for this one order-one record for every item the customer orders. You can completely avoid this problem by properly establishing the relationship between the ORDERS and PRODUCTS tables with a linking table. Figure 10-22 shows a diagram of this relationship as it should be established.

Although this arrangement eliminates the redundant data, there is still one minor problem: QUOTED PRICE and QUANTITY ORDERED are no longer appropriate in the ORDERS table. First and foremost, their values are not directly determined by the Primary key of the ORDERS table. Second, they bear no relationship to any of the remaining fields in the table. They do, however, relate to a particular PRODUCT NUMBER that appears within a given order. Therefore, the QUOTED PRICE and QUANTITY ORDERED fields should be removed from the ORDERS table and placed in the ORDER DETAILS linking table. Figure 10-23 shows the revised diagram of this relationship.

Whenever you establish many-to-many relationships between tables, check each table within the relationship to determine whether there are any fields that should be transferred to the linking table. When in doubt, load all the tables with sample data; you should be able to see immediately which fields should be transferred to the linking table.

With all these techniques in mind, establish each of the relationships you identified in the previous step. Make certain you create a diagram for each relationship-you'll add new information to each diagram as the design process further unfolds.

Reviewing the Structure of Each Table

Once you've established the relationships between tables, review all of the table structures. As you established each relationship, you made modifications to the existing table structures and created several new table structures. (Remember the linking tables?) Therefore, you want to make certain that each table conforms to the Elements of the Ideal Table:

Elements of the Ideal Table

It represents a single subject, which can be an object or event.

It has a Primary key.

It does not contain multipart fields.

It does not contain multivalued fields.

It does not contain calculated fields.

It does not contain unnecessary duplicate fields.

It contains only an absolute minimum amount of redundant data.

If you determine that a table is not in conformance with the Elements of the Ideal Table, identify the problem and make the necessary modifications. Then take the table through the appropriate stages of the database design process until you reach the stage we're at now. As long as you've been following proper procedures thus far, you shouldn't encounter any problems with the tables.

Refining All Foreign keys

As you've seen, a Primary key from one particular table becomes a Foreign key in another table when that field is used to establish a relationship between those two tables. A Foreign key must conform to a set of elements, just as all the other keys you've worked with so far.

Elements of a Foreign Key

It has the same name as the Primary key from which it was copied. This rule should be adhered to unless there is an absolutely compelling reason not to do so. (Such a reason is shown in the example under "Alias" in Chapter 9.)

It uses a replica of the Field Specifications for the Primary key from which it was copied. This is in accordance with the sixth element of the Elements of the Ideal Field, as you learned in Chapter 7. There are, however, a few settings in the Logical Elements category of the Field Specifications that will be slightly different for the Foreign key than for its parent Primary key. Figure 10-24 shows the Logical Elements for an EMPLOYEE ID field used as a Primary key in the EMPLOYEES table, and Figure 10-25 shows the Logical Elements for the same EMPLOYEE ID field when it is used as a Foreign key in the ORDERS table.

As you can see in Figure 10-25, three minor changes have been made to these elements. The Type of Key setting is indicated as "Foreign" because the EMPLOYEE ID field is being used as a Foreign key in this instance. The Uniqueness setting is designated as "Non-Unique" because you want to be able to associate a single employee with many orders; if you marked this as "Unique," you'd only be able to enter an employee into the ORDERS table one time, which would greatly limit his or her sales potential! The Edit Rule setting is designated as "Enter Now, Edits Allowed" because there will be times when the wrong employee is credited for an order and you'll need to be able to change the value to reflect the correct employee for the order.

The setting in the Specification Information category of the field specification is also different for a Foreign key as compared with that of the Primary key from which it was created. Figure 10-26 shows the Specification Information for the EMPLOYEE ID field used as a Foreign key in the ORDERS table.

A Foreign key uses a replica field specification because its specification is based on an existing Primary key's unique specification. Make certain you include the name of the Primary key's parent table in the Source Specification item, as shown in Figure 10-26. This makes it easy to compare the Primary key's specifications and the Foreign key's specifications as the need arises.

It draws its values from the Primary key to which it refers. A Foreign key's range of values is limited to existing values of the Primary key to which it refers. For example, you cannot enter an invalid Employee ID into the ORDERS table. Any Employee ID you enter into the ORDERS table must first exist as an Employee ID in the EMPLOYEES table. This ensures consistency in the values of both fields in both tables.

Review the Foreign keys in each table to make certain that they conform to the Elements of a Foreign key. If they are not in accordance with these elements, make the appropriate modifications. But if you've followed proper procedure up to this point, it should be a rare instance when you would have to make any changes.

Establishing Relationship Characteristics

The final step in this procedure is to establish the characteristics of each relationship. These characteristics indicate what will occur when a record is deleted, the type of participation each table bears within the relationship, and to what degree each table participates in the relationship.

Establishing a Deletion Rule for Each Relationship

Now you must establish a deletion rule for the relationship. This rule defines what will happen if a user places a request to delete a record in the main table of a one-to-one relationship or in the "one" side of a one-to-many relationship. Defining a deletion rule for a relationship helps to guard against "orphaned" records, which are records that exist in a subordinate table of a one-to-one relationship but have no related records in a main table, or records that exist in the "many" side of a one-to-many relationship that have no related records in the "one" side.

Two options are available for the deletion rule:

Restrict. The requested record cannot be deleted if there are related records in the subordinate table of a one-to-one relationship or the "many" side of a one-to-many relationship. Related records must be deleted before the requested record can be deleted.

Cascade. The requested record will be deleted as well as all related records in the subordinate table of a one-to-one relationship or the "many" side of a one-to-many relationship.

Use a restrict deletion rule as a matter of course; use a cascade deletion rule very judiciously. The best way to determine whether a cascade deletion rule is appropriate for a set of related tables is to study the relationship diagram for those tables. Consider the diagram shown in Figure 10-27.

Select a pair of related tables and pose the following question:

"If a record in [name of main or 'one' table] is deleted, should related records in [name of subordinate or 'many' table] be deleted as well?"

Here the question is framed so that you can understand the premise behind it. When you pose this question against a particular pair of tables, such as the CUSTOMERS and ORDERS tables, just "fill in the blanks." The question now becomes:

"If a record in the CUSTOMERS table is deleted, should related records in the ORDERS table be deleted as well?"

If the answer to this question is no, you must use a restrict deletion rule for this relationship; otherwise, the cascade deletion rule is appropriate. The answer to this question greatly depends on how the data is being used within the database. If you cannot easily provide an answer, make note of the relationship and continue with the next table relationship. You'll revisit these relationships when you establish Business Rules for the database later in Chapter 11.

After you've identified the type of deletion rule you want to use for a relationship, add it to the relationship diagram. Restrict deletion rules are indicated by an "(R)" and cascade deletion rules by a "(C)." Place the designation under the connection line of the main table or the table on the "one" side of the relationship as shown in Figure 10-28.

You probably noticed that the deletion rule only applies to a record in the main table or the "one" side of the relationship. There is no need to worry about deleting records in the subordinate table or the "many" side of the relationship because there can be no adverse affects as a result of doing so.

Identifying the Type of Participation

for Each Table

Each table participates within a relationship in a particular manner. A table's type of participation determines whether a record must exist in that table before a record can be entered into the other table. There are two types of participation:

Mandatory. There must be at least one record in this table before you can enter any records into the other table.

Optional. There is no requirement for any records to exist in this table before you can enter any records into the other table.

The type of participation for most tables is usually determined later when you're defining Business Rules. However, it's common to establish the type of participation for tables in relationships where the type of participation for each table is obvious, is a result of common sense, or is in accordance with some particular set of standards. Consider the one-to-many relationship between the EMPLOYEES and CUSTOMERS tables shown in Figure 10-29.

Say that in this instance, a particular employee must be assigned to each customer. This employee acts as the customer's "account representative" and takes care of all transactions and communications between the organization and that customer. Although each customer must be associated with a particular employee, not every employee is required to be associated with even a single customer. Remember that many of the people represented in the EMPLOYEES table perform some other function within the organization and may never come into contact with a customer at all.

This scenario does not describe or define any special circumstances; it is the way the organization conducts its business. Therefore, you can infer that

the type of participation for the EMPLOYEES table should be designated as "Mandatory." The EMPLOYEES table must have at least one record because each customer is required to be associated with a particular employee.

the type of participation for the CUSTOMERS table should be designated as "Optional." It is unnecessary for any records to exist in the CUSTOMERS table before you can enter an employee into the EMPLOYEES table.

Once you've determined the type of participation for each table within the relationship, modify the relationship diagram to reflect this new information. A mandatory type of participation is symbolized by a vertical line; an optional type of relationship is symbolized by a circle. Figure 10-30 shows the relationship diagram for the EMPLOYEES and CUSTOMERS tables and illustrates the use of these two symbols.

Identifying the Degree of Participation

for Each Table

Now you must determine the degree to which each table will participate within the relationship. This is a simple matter of identifying the total number of records in one table that can be related to a single record in the other table. The factors used in determining the degree of participation-obvious circumstances, common sense, or conformance to some set of standards-are the same as those used to determine the type of participation. Therefore you will identify the degree of participation for some tables now and leave the remaining tables to be revisited when you define Business Rules for the database.

The degree of participation is symbolized by two numbers, separated by a comma, and enclosed in parentheses, such as "(1,8)." The numbers in this symbol represent the lower and upper boundaries, respectively, of the total number of records in one table that can be related to a single record in the other table. In this instance, a single record in one table can be related to a minimum of one record and a maximum of eight records in the other table.

When you add this symbol to a relationship diagram, place it over the connection line of the appropriate table. Figure 10-31 illustrates the proper use of this symbol within a relationship diagram. This particular diagram indicates the degree of participation for the relationship between the EMPLOYEES and CUSTOMERS tables.

In this example, the diagram indicates that a customer must be related to at least one employee, but no more than one. It also indicates that an employee is not required to be associated with a customer. On the other hand, an employee cannot be associated with more than ten customers.

Figure 10-32 shows an example of an unlimited degree of participation, which is represented by the letter N inside the parenthesis. In this instance, a customer can exist within the CUSTOMERS table without being associated with an order in the ORDERS table. On the other hand, a particular customer can be associated with an unlimited number of records in the ORDERS table. You certainly wouldn't want to limit the amount of purchases a customer can place.

Now that you know how to define the characteristics of a relationship, execute this step for every relationship you've established thus far. As you identify these characteristics, revise the diagram for each relationship to reflect the new information.

Verifying Table Relationships with Users and Management

The very last order of business is to verify the relationships. This is a relatively easy task that can be performed using the following checklist:

1. Make sure that each relationship between a pair of tables has been properly identified.

2. Make certain that each relationship

Read More Show Less


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.

  1. 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.
  2. 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.
  3. Application development: The third phase involves creating an application that allows a single user or group of users to interact with the data stored in the database. The application-development phase itself can be divided into separate processes, such as determining end-user tasks and their appropriate sequences, determining information requirements for report output, and creating a menu system for navigating the application.

You should always go through the logical design first and execute it as completely as possible. After you've created a sound structure, you can then implement it within any database software you choose. As you begin the implementation phase, you may find that you need to modify the database structure based on the pros and cons or strengths and weaknesses of the database software you've chosen. You may even decide to make structural modifications to enhance data-processing performance. Performing the logical design first ensures that you make conscious, methodical, clear, and informed decisions concerning the structure of your database. As a result, you help minimize the potential number of further structural modifications you might need to make during the physical-implementation and application-development phases.

This book deals with only the logical-design phase of the overall development process, and the book's main purpose is to explain the process of relational database design without using the advanced, orthodox methodologies found in an overwhelming majority of database-design books. I've taken care to avoid the complexities of these methodologies by presenting a relatively straightforward, commonsense approach to the design process. I also use a simple and straightforward data-modeling method as a supplement to this approach, and present the entire process as clearly as possible and with a minimum of technical jargon.

There are many database-design books out on the market that include chapters on implementing the database within a specific database product, and some books even seem to meld the design and implementation phases together. (I've never particularly agreed with the idea of combining these phases, and I've always maintained that a database developer should perform the logical-design and implementation phases separately to ensure maximum focus, effectiveness, and efficiency.) The main drawback that I've encountered with these types of books is that it can be difficult for a reader to obtain any useful or relevant information from the implementation chapters if he or she doesn't work with the particular database software or programming language that the book incorporates. It is for this reason that I decided to write a book that focuses strictly on the logical design of the database.

NOTE: I do not cover implementation issues, SQL, or application-programming issues in this work, but there are various books that I do recommend on these topics. You can review my recommendations by accessing my Web site at

This book should be easier to read than other books you may have encountered on the subject. Many of the database-design books on the market are highly technical and can be difficult to assimilate. I think most of these books can be confusing and overwhelming if you are not a computer science major, database theorist, or experienced database developer. The design principles you'll learn within these pages are easy to understand and remember, and the examples are common and generic enough to be relevant to a wide variety of situations.

Most people I've met in my travels around the country have told me that they just want to learn how to create a sound database structure without having to learn about normal forms or advanced mathematical theories. Many people are not as worried about implementing a structure within a specific database software as they are about learning how to optimize their data structures and how to impose data integrity. In this book, you'll learn how to create efficient database structures, how to impose several levels of data integrity, as well as how to relate tables together to obtain information in an almost infinite number of ways. Don't worry; this isn't as difficult a task as you might think. You'll be able to accomplish all of this by understanding a few key terms and by learning and using a specific set of commonsense techniques and concepts.

You'll also learn how to analyze and leverage an existing database, determine information requirements, and determine and implement business rules. These are important topics because many of you will probably inherit old databases that you'll need to revamp using what you'll learn by reading this book. They'll also be just as important when you create a new database from scratch.

When you finish reading this book, you'll have the knowledge and tools necessary to create a good relational database structure. I'm confident that this entire approach will work for a majority of developers and the databases they need to create.

How to Read This Book

I strongly recommend that you read this book in sequence from beginning to end, regardless of whether you are a novice or a professional. You'll keep everything in context this way and avoid the confusion that generally comes from being unable to see the "big picture" first. It's also a good idea to learn the process as a whole before you begin to focus on any one part.

If you are reading this book to refresh your design skills, you could read just those sections that are of interest to you. As much as possible, I've tried to write each chapter so that it could stand on its own; nonetheless, I would still recommend that you glance through each of the chapters to make sure that you're not missing any new ideas or points on design that you may not have considered up to now.

How This Book Is Organized

Here's a brief overview of what you'll find in each part and each chapter.

Part I: Relational Database Design

This section provides an introduction to databases, the idea of database design, and some of the terminology you'll need to be familiar with in order to learn and understand the design process presented in this book.

Chapter 1, The Relational Database, provides a brief discussion of the types of databases you'll encounter, common database models, and a brief history of the relational database.

Chapter 2, Design Objectives, explores why you should be concerned with design, points out the objectives and advantages of good design, and provides a brief introduction to normalization and normal forms.

Chapter 3, Terminology, covers the terms you need to know in order to learn and understand the design methodology presented in this book.

Part II: The Design Process

Each aspect of the database-design process is discussed in detail in Part II, including establishing table structures, assigning primary keys, setting field specifications, establishing table relationships, setting up views, and establishing various levels of data integrity.

Chapter 4, Conceptual Overview, provides an overview of the design process, showing you how the different components of the process fit together.

Chapter 5, Starting the Process, covers how to define a mission statement and mission objectives for the database, both of which provide you with an initial focus for creating your database.

Chapter 6, Analyzing the Current Database, covers issues concerning the existing database. We look at reasons for analyzing the current database, how to look at current methods of collecting and presenting data, why and how to conduct interviews with users and management, and how to compile initial field lists.

Chapter 7, Establishing Table Structures, covers topics such as determining and defining what subjects the database should track, associating fields with tables, and refining table structures.

Chapter 8, Keys, covers the concept of keys and their importance to the design process, as well as how to define candidate and primary keys for each table.

Chapter 9, Field Specifications, covers a topic that a number of database developers tend to minimize. Besides indicating how each field is created, field specifications determine the very nature of the values a field contains. Topics in this chapter include the importance of field specifications, types of specification characteristics, and how to define specifications for each field in the database.

Chapter 10, Table Relationships, explains the importance of table relationships, types of relationships, setting up relationships, and establishing relationship characteristics.

Chapter 11, Business Rules, covers types of business rules, determining and establishing business rules, and using validation tables. Business rules are very important in any database because they provide a distinct level of data integrity.

Chapter 12, Views, looks into the concept of views and why they are important, types of views, and how to determine and set up views.

Chapter 13, Reviewing Data Integrity, reviews each of the levels of integrity that have been defined and discussed in previous chapters. Here you learn that it's a good idea to review the final design of the database structure to ensure that you've imposed data integrity as completely as you can.

Part III: Other Database-Design Issues

This section deals with topics such as avoiding bad design and bending the rules set forth in the design process.

Chapter 14, Bad Design--What Not to Do, covers the types of designs you should avoid, such as a flat-file design and a spreadsheet design.

Chapter 15, Bending or Breaking the Rules, discusses those rare instances in which it may be necessary to stray from the techniques and concepts of the design process. This chapter tells you when you should consider bending the rules, as well as how it should be done.

Part IV: Appendixes

Appendix A, Answers to Review Questions, contains the answers to all of the review questions in Chapters 1 through 12.

Appendix B, Diagram of the Database Design Process, provides a diagram that maps the entire database design process.

Appendix C, Design Guidelines, provides an easy reference to the various sets of design guidelines that appear throughout the book.

Appendix D, Documentation Forms, provides blank copies of the Field Specifications, Business Rule Specifications, and View Specifications sheets, which you can copy and use on your database projects.

Appendix E, Database Design Diagram Symbols, contains a quick and easy reference to the diagram symbols used throughout the book.

Appendix F, Sample Designs, contains sample database designs that can serve as the basis for ideas for databases you may want or need to create.

Appendix G, Recommended Reading, provides a list of books that you should read if you are interested in pursuing an in-depth study of database technology.

Glossary contains concise definitions of various words and phrases used throughout the book.


A Word About the Examples and Techniques in This Book

You'll notice that there are a wide variety of examples in this book. I've made sure that they are as generic and relevant as possible. However, you may notice that several of the examples are rather simplified, incomplete, or even on occasion incorrect. Believe it or not, I created them that way on purpose.I've created some examples with errors so that I could illustrate specific concepts and techniques. Without these examples, you wouldn't see how the concepts or techniques are put to use, as well as the results you should expect from using them. Other examples are simple because, once again, the focus is on the technique or concept and not on the example itself. For instance, there are many ways that you can design an order-tracking database. However, the structure of the sample order-tracking database I use in this book is simple because the focus is specifically on the design process, not on creating an elaborate order-tracking database system.

So what I'm really trying to emphasize here is this:

Focus on the concept or technique and its intended results, not on the example used to illustrate it.

A New Approach to Learning

Here's an approach to learning the design process (or pretty much anything else, for that matter) that I've found very useful in my database-design classes.Think of all the techniques used in the design process as a set of tools; each tool (or technique) is used for a specific purpose. The idea here is that once you learn generically how a tool is used, you can then use that tool in any number of situations. The reason you can do this is because you use the tool the same way in each situation.

Take a Crescent wrench, for example. Generically speaking, you use a Crescent wrench to fasten and unfasten a nut to a bolt. You open or close the jaw of the wrench to fit a given bolt by using the adjusting screw located on the head of the wrench. Now that you have that clear, try it out on a few bolts. Try it on the legs of an outdoor chair, or the valve cover on an engine, or the side panel of an outdoor cooling unit, or the hinge plates of an iron gate. Do you notice that regardless of where you encounter a nut and bolt, you can always fasten and unfasten the nut by using the Crescent wrench in the same manner?

The tools used to design a database work in exactly the same way. Once you understand how a tool is used generically, it will work the same way regardless of the circumstances under which it is used. For instance, consider the tool (or technique) for decomposing a field value. Say you have a single Address field in a CUSTOMERS table that contains the street address, city, state, and zip code for a given customer. You'll find it difficult to use this field in your database because it contains more than one item of data; you'll certainly have a hard time retrieving information for a particular city or sorting the information by a specific zip code.

The solution to this apparent dilemma is to decompose the Address field into smaller fields. You do this by identifying the distinct items that make up the value of the field, and then treating each item as its own separate field. That's all there is to it! This process constitutes a "tool" that you can now use on any field containing a value composed of two or more distinct data items, such as these sample fields. Figure I.1 shows the results of the decomposition process.

NOTE: You'll learn more about decomposing field values in Chapter 7, "Establishing Table Structures."

You can use all of the techniques ("tools") that are part of the design process presented in this book in the same manner. You'll be able to design a sound database structure using these techniques regardless of the type of database you need to create. Just be sure to remember this:

Focus on the concept or technique being presented and its intended results, not on the example used to illustrate it.


Read More Show Less

Customer Reviews

Be the first to write a review
( 0 )
Rating Distribution

5 Star


4 Star


3 Star


2 Star


1 Star


Your Rating:

Your Name: Create a Pen Name or

Barnes & Review Rules

Our reader reviews allow you to share your comments on titles you liked, or didn't, with others. By submitting an online review, you are representing to Barnes & that all information contained in your review is original and accurate in all respects, and that the submission of such content by you and the posting of such content by Barnes & does not and will not violate the rights of any third party. Please follow the rules below to help ensure that your review can be posted.

Reviews by Our Customers Under the Age of 13

We highly value and respect everyone's opinion concerning the titles we offer. However, we cannot allow persons under the age of 13 to have accounts at or to post customer reviews. Please see our Terms of Use for more details.

What to exclude from your review:

Please do not write about reviews, commentary, or information posted on the product page. If you see any errors in the information on the product page, please send us an email.

Reviews should not contain any of the following:

  • - HTML tags, profanity, obscenities, vulgarities, or comments that defame anyone
  • - Time-sensitive information such as tour dates, signings, lectures, etc.
  • - Single-word reviews. Other people will read your review to discover why you liked or didn't like the title. Be descriptive.
  • - Comments focusing on the author or that may ruin the ending for others
  • - Phone numbers, addresses, URLs
  • - Pricing and availability information or alternative ordering information
  • - Advertisements or commercial solicitation


  • - By submitting a review, you grant to Barnes & and its sublicensees the royalty-free, perpetual, irrevocable right and license to use the review in accordance with the Barnes & Terms of Use.
  • - Barnes & reserves the right not to post any review -- particularly those that do not follow the terms and conditions of these Rules. Barnes & also reserves the right to remove any review at any time without notice.
  • - See Terms of Use for other conditions and disclaimers.
Search for Products You'd Like to Recommend

Recommend other products that relate to your review. Just search for them below and share!

Create a Pen Name

Your Pen Name is your unique identity on It will appear on the reviews you write and other website activities. Your Pen Name cannot be edited, changed or deleted once submitted.

Your Pen Name can be any combination of alphanumeric characters (plus - and _), and must be at least two characters long.

Continue Anonymously
Sort by: Showing all of 6 Customer Reviews
  • Anonymous

    Posted April 20, 2003

    Excellent for nontechnical users

    A very well done text that avoids a lot of the mathematical jargon in the field. No set theory. No predicate calculus. This book is about the logical design of databases for someone who has never done that before. The tasks here are independent of any actual SQL software, and you do not need to know any SQL either. This is ostensibly a computer book. But interestingly enough, you do not need a computer to do anything it describes. From an object-oriented view, if that is your background, the author has done a skilful job of separating the design methodology from any software implementation or environment. It is also useful if you are not from a computing background, but you need to know what your technical coworkers, subordinates or the people in the department next door do when they maintain databases. Ok. The author does not discuss issues like transaction processing. But that is off-topic. Here, with a few hours reading, you can get the essence of database design.

    1 out of 2 people found this review helpful.

    Was this review helpful? Yes  No   Report this review
  • Anonymous

    Posted January 3, 2009

    No text was provided for this review.

  • Anonymous

    Posted January 29, 2009

    No text was provided for this review.

  • Anonymous

    Posted May 29, 2012

    No text was provided for this review.

  • Anonymous

    Posted November 13, 2009

    No text was provided for this review.

  • Anonymous

    Posted March 14, 2009

    No text was provided for this review.

Sort by: Showing all of 6 Customer Reviews

If you find inappropriate content, please report it to Barnes & Noble
Why is this product inappropriate?
Comments (optional)