--THOMAS CARLYLE
The Importance of Completing the Design Process
Defining a Mission Statement and Mission Objectives
Analyzing the Current Database
Creating the Data Structures
Determining and Establishing Table Relationships
Determining and Defining Business Rules
Determining and Establishing Views
Reviewing Data Integrity
Summary
Understanding how to design a relational database isn't quite as hard as understanding the universe; in fact, it's much easier. It is important, however, to have an overall idea of the way the database design process works and a general idea of the steps involved within the process. The purpose of this chapter is to provide an overview of the database design process.
For the purpose of this overview, all the techniques used in the design process are consolidated into seven phases, and each phase is discussed in general terms. This discussion provides a good overall picture of the database design process, and I hope it will give you a much clearer understanding of each of the design techniques discussed in Chapters 5-13.
| Note: A database can be designed by a single individual or a design team composed of two or more individuals. Throughout the remainder of this book, the phrase "database developer" is used to refer to the person designing the database. |
One thing I want to make perfectly clear at this point is the importance of completing the design process. I'm often asked if it's truly necessary to go through the entire design process. My answer is always a resounding Yes! Then I'm asked whether it's still necessary if someone is only going to create a "simple" database. ("Simple" is one of the most dangerous words known to a database developer. Nothing is ever "simple.") Again, my answer is "Yes! It's still necessary." The type, size, or purpose of the database is totally irrelevant to the value of undertaking a fully developed design. The database design process should be implemented and followed from beginning to end.
As has been shown -- more examples will follow -- designing a database without undergoing a complete database design process is a very bad idea. Many database problems can be attributed to poor database design. And partially following the design process is just about as bad as not using it at all. An incomplete design is a poor design. Only following through with a whole, unabbreviated design process assures a sound structure and data integrity.
An important point to keep in mind is that the level of structural integrity and data integrity is in direct proportion to how thoroughly the design process is followed. The less time spent on the design process, the greater the risk of encountering problems with the database. While thoroughly following the database design process may not eliminate all of the problems you may encounter when designing a database, it will greatly help to minimize them. Also in an RDBMS software program a well-designed database is easier to implement than a poorly designed database.
Databases are not hard to design; it just takes a little time to design them properly. Whenever it seems as if the design process is taking too long, don't use shortcuts -- just be patient and remember what a wise old sage once said: "There's never time to do it right, but there's always time to do it over!"
The first phase in the database design process is to define a mission statement and mission objectives for the database. The mission statement establishes the purpose of the database and provides a focus for the database developer.
Every database is created for a specific purpose, whether it's to solve a specific business problem, to manage the daily transactions of a business or organization, or to be used as part of an information system. By identifying the purpose of the database and defining it in a mission statement, you will ensure that the appropriate design is created and the appropriate data is collected to support the intended purpose of the database.
Along with the mission statement, mission objectives are defined in this phase. Mission objectives are statements representing the general tasks to be performed against the data collected in the database. These objectives are used to support the defined mission statement and aid in determining various aspects of the structure of the database.
There are two separate groups of people who will be involved in defining the mission statement and the mission objectives. The first group, which includes the database developer, the owner or head of the organization, and management personnel, is responsible for defining the mission statement. The second group which includes the database developer, management personnel, and end users, will be responsible for the definition of the mission objectives.
The second phase in the database design process involves analyzing the current database, if one exists. Depending on the organization, the database will typically be a legacy database or a paper-based database. A database that has been in existence and in use for several years or more is considered a legacy database (also known as an inherited database). And, as many people know, a paper-based database is a loose collection of forms, index cards, manila folders, and the like. Whatever the database type or condition, analyzing it will yield valuable information about the way data is currently being managed and used. In addition, the analysis involves reviewing the way data is currently collected and presented. As the database developer you will look at how paper is used to collect data (via forms) and present data (via reports). Similarly, if some software application program is being used to manage and manipulate data for the database, you will study the way data is collected and presented on-screen.
Another part of the analysis involves conducting interviews with users and management to identify how they interact with the database on a daily basis. As the database developer you ask users how they work with the database and what their information requirements are at the current time. You then interview management personnel and ask them about the information they currently receive, and about their perception of the overall information requirements of their organization.
You then compile a list of fields and calculations, using the information gathered from the analysis and the interviews. This list then constitutes the fundamental data requirements of the organization and provides a starting point for the design of a new database. It can be anticipated that this list will be extended or refined as the design is developed.
Once the list has been compiled, it is briefly reviewed by users and management for refinement. You encourage feedback and take into consideration any suggestions for modifications. If you think the suggestions are reasonable and well-supported, you make the modifications, record the list in its current state, and move on to the next phase.
Creating the data structures for the database is the third phase in the database design process. You define tables and fields, establish keys, and define field specifications for every field.
Tables are the first structures you define in the database. The various subjects that each of the tables represents are determined from the mission objectives stated in the first phase of the design process, together with the data requirements gathered in the second phase of the design process. Once you have identified the subjects, you establish them as tables, and then you associate each field from the field list compiled in the second phase with an appropriate table. You then review each table to ensure that it represents only one subject and that it contains no duplicate fields.
Next you go on to review the fields within each table. If you find multipart or multivalued fields in a table, you modify the table so that each field stores only a single value. A field that does not represent a characteristic of the subject of the table is moved to a more appropriate table or deleted entirely. When your review is complete, you establish a Primary key that will uniquely identify each record within the table.
The final step in this phase is to establish field specifications for each field in the database. At this point, you conduct interviews with users and management to help identify any specific field characteristics that may be important to them. You also review and discuss any characteristics that they may be unfamiliar with. When the interviews are completed, you define and document field specifications for each field. You then review the table structures and field specifications with users and management once more for possible refinements. When the refinements, if any, are completed, your tables are ready for the next phase.
In the fourth phase of the database design process you establish table relationships. You conduct interviews with users and management once again, identify relationships, identify relationship characteristics, and establish relationship-level integrity.
Working with users and management to identify relationships is extremely helpful because you cannot possibly be familiar with every aspect of the data being used by the organization. Most people have a good perspective of the data they work with and can usually identify relationships among the data rather easily. Therefore interviewing users yields very useful information.
Once the relationships have been identified, you need to establish the logical connection for each relationship. Depending on the type of relationship, you use either a Primary key or a "linking" table to make the connection between a pair of tables based on the type of relationship you want to establish. Next you'll determine the type of participation and the degree of participation for each relationship. In some cases, these participation characteristics will be obvious due to the nature of the data stored in the tables. In other cases, the type of participation and degree of participation will be based on specific Business Rules.
Determining and defining Business Rules is the fifth phase of the database design process. As the database developer you hold interviews, identify limitations on various aspects of the database, establish Business Rules, and define and implement Validation Tables.
The way an organization views and uses its data will dictate a set of limitations and requirements that have to be built into the database. Your interviews with users and management will determine what specific limitations and requirements will be imposed on the data, data structures, or relationships. You then establish and document these specifications as Business Rules.
The interviews held with users will reveal specific limitations on various aspects of the database. For example, a user working with an Order Processing database is very aware of specific details, such as the fact that a SHIP DATE must be later than an ORDER DATE, that there must always be a DAYTIME PHONE NUMBER, and that a METHOD OF SHIPMENT should always be indicated. On the other hand, management interviews are intended to reveal general limitations on various aspects of the database. The office manager for an entertainment agency, for example, is familiar with general issues, such as the fact that an agent can represent no more than twenty entertainers and that promotional information for each entertainer must be updated every year.
Next, you define and implement Validation Tables, if necessary, to support certain Business Rules. For example, if certain fields are found to have a finite range of values owing to the manner in which they are used by the organization, Validation Tables are used to ensure the consistency and validity of the values stored in those fields.
The level of integrity established by Business Rules at this point is significant because it relates directly to the way the organization views and uses its data. As the organization grows, its perspective on the data will change, which means that the Business Rules must change as well. This means that determining and establishing Business Rules is an ongoing, iterative process. Constant diligence is necessary at all times to maintain this level of integrity properly.
Determining and establishing Views is the sixth phase of the database design process. Once more, you'll need to conduct interviews, identify various ways of looking at the data, and establish the Views.
You'll ask users and management to identify the various ways that they look at the data in the database. Whereas one group may view the data from a shared perspective, another group within the organization may use a different perspective; some individuals have unique ways of visualizing the data based on the work they perform. For example, some individuals need to retrieve data from several tables at the same time in order to see summary information; others only need to see specific fields from a certain table.
Once you have identified the various ways of seeing the data, you establish them formally as Views. Each view is defined using the appropriate table or tables, and, in the case of multitable Views, fields from the appropriate tables are assigned to the View. Once you have established all of the Views, you'll need to identify criteria for certain Views so that they will only display specific records.
The seventh and last phase in the database design process is reviewing the final database structure for data integrity. First, you'll review each table to ensure that it meets the criteria of a properly designed table, and you will initially check the fields within each table for proper structure. Any inconsistencies or problems will be resolved and reviewed once more. After the appropriate refinements are made, you'll check table-level integrity.
Second, you review and check field specifications for each field. You then make refinements to fields as necessary and check field-level integrity after any needed refinements have been made. This review reaffirms the field-level integrity identified and established earlier in the database design process.
Third, you have to review the validity of each relationship, confirming the type of relationship, as well as the type of participation and degree of participation for each table within the relationship. You then study relationship integrity to ensure that there are matching values between shared fields, and that there are no problems inserting, updating, or deleting data in any of the tables within the relationship.
Finally, you go over the Business Rules to confirm the limitations placed on various aspects of the database, as identified earlier in the database design process. If there are any other limitations that have come to light since the last set of personnel interviews, you establish them as new Business Rules and add them to the existing set of Business Rules.
Once the entire database design process is complete, the logical database structure is ready to be implemented in an RDBMS software program. However, the process is never really complete because the database structure will always need refinement as the organization grows.
We began this chapter with a discussion of the importance of completing the design process. Designing a database without the benefit of a good design method leads to poor and improper design. We also discussed the fact that the level of structural integrity and data integrity is totally dependent on how thoroughly the design process was followed. Inconsistent data and inaccurate information are two problems typically encountered with poorly designed databases.
Next we looked at an overview of the entire database design process. To provide a clear overall picture, the process was consolidated into the following phases:
1. Define a mission statement and mission objectives for the database. The mission statement defines the purpose of the database. The mission objectives define the tasks that are to be performed by users against the data in the database.
2. Analyze the current database. You identify the data requirements of the organization by reviewing the way data is currently collected and presented and by conducting interviews with users and management to determine how they use the database on a daily basis.
3. Create the data structures. You establish tables by identifying the subjects that will be tracked by the database. Next you assign each table fields that best characterize its subject, and you designate a Primary key as well. Then you establish field specifications for every field in the table.
4. Determine and establish table relationships. You'll identify relationships that exist between the tables in the database and then establish the logical connection for each relationship using Primary keys and Foreign keys, or linking tables. Finally, you'll set the various characteristics for each relationship.
5. Determine and define Business Rules. Next you conduct interviews with users and management to identify constraints on the data in the database based on the way the organization views and uses its data. These constraints are then declared as Business Rules, which will serve to establish various levels of data integrity.
6. Determine and establish Views. Users and management are interviewed to identify the various ways they look at the data in the database. After these various perspectives have been identified, you establish them as Views. Each View is defined using the appropriate table or tables, and certain Views use criteria that limit the records that they display.
7. Review data integrity. This phase involves four steps. First, you review each table to ensure that it meets proper design criteria. Second, you review and check all field specifications. Third, you test the validity of each relationship. Fourth, you go over and confirm the business rules.