We began this chapter with a discussion of the importance of completing the design process, and you learned that 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 and data integrity is in direct proportion to how thoroughly you follow the design process. You then learned that inconsistent data and inaccurate information are two problems typically associated with poorly designed databases.
Next we looked at an overview of the entire database-design process. The process was consolidated into the following phases in order to provide you with a clear picture of the general steps involved in designing a database:
Define a mission statement and mission objectives for the database. The mission statement defines the purpose of the database, and the mission objectives define the tasks that are to be performed by users against the data in the database.
Analyze the current database. You identify your organization's data requirements by reviewing the way your organization currently collects and presents its data and by conducting interviews with users and management to determine how they use the database on a daily basis.
Create the data structures. You establish tables by identifying the subjects that the database will track. Next, you associate each table with fields that represent distinct characteristics of the table's subject, and you designate a particular field (or group of fields) as the primary key. You then establish field specifications for every field in the table.
Determine and establish table relationships. You identify relationships that exist between the tables in the database and establish the logical connection for each relationship using primary keys and foreign keys or by using linking tables. Then you set the appropriate characteristics for each relationship.
Determine and define business rules. You conduct interviews with users and management to identify constraints that must be imposed upon the data in the database. The manner in which your organization views and uses its data typically determines the types of constraints you must impose on the database. You then declare these constraints as business rules, and they will serve to establish various levels of data integrity.
Determine and establish views. You interview users and management to identify the various ways they work with the data in the database. When your interviews are complete, you establish views as appropriate. You define each view using the appropriate tables and fields, and you establish criteria for those views that must display a limited or finite set of records.
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 review and confirm the business rules.