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

Reviewing and Refining Data Integrity

Reviewing data integrity is a simple task if you take a modular approach, that is, if you sequentially review each component of overall data integrity: table-level, field-level, and relationship-level integrity and business rules. If you have carefully followed the design method presented in this book, you should encounter very few problems here. The following sections briefly outline the points you should keep in mind as you conduct the review, and they contain references to earlier chapters in case you encounter any problems.

At the Table Level

In order to ensure that you've properly established table-level integrity, review each table and make certain that the table conforms to all of the following points:

• There are no duplicate fields in the table.

• There are no calculated fields in the table.

• There are no multivalued fields in the table.

• There are no multipart fields in the table.

• There are no duplicate records in the table.

• Every record in the table is identified by a primary key value.

• Each primary key conforms to the Elements of a Primary Key.

If you believe you have problems with any of these items, resolve them using the techniques and concepts discussed in Chapters 6 through 8.

At the Field Level

You can ensure that you've properly established field-level integrity after you've done the following:

• Made sure each field conforms to the Elements of the Ideal Field

• Made certain you've defined a set of field specifications for each field

You can resolve field-level integrity problems with the techniques discussed in Chapter 9.

At the Relationship Level

Examine each table relationship to ensure that you've properly established relationship-level integrity. You've achieved this level of integrity when you've completed these tasks :

• Properly established the relationship

• Defined the appropriate deletion rules

• Correctly identified the type of participation for each table

• Established the proper degree of participation for each table

If you identify a problem with a relationship, use the techniques in Chapter 10 to resolve it.

At the Level of Business Rules

You can ensure that your business rules are sound by making certain these tasks are complete:

• You're sure that each rule imposes a meaningful constraint.

• You've determined the proper category for the rule.

• You've properly defined and established each rule.

• You've modified the appropriate field specification elements or table-relationship characteristics.

• You've established the appropriate validation tables.

• You've completed a Business Rule Specifications sheet for each rule.

If you encounter problems with any of your business rules, refer to Chapter 11 for the techniques necessary to solve them.

At the Level of Views

Although views are not directly connected to any component of data integrity, you should nevertheless review all of your view structures. As you examine each view, make certain you've addressed these items:

• Each view contains the base tables necessary to provide the required information.

• You've assigned the appropriate fields to each view.

• Each calculated field provides pertinent information or enhances the manner in which the view presents its data.

• Each filter returns the appropriate set of records.

• Each view has a view diagram.

• Each view diagram is accompanied by a View Specifications sheet.

If you encounter problems with any view, resolve them by using the techniques discussed in Chapter 12.

Once you've completed this entire review, you can be confident that the database structure is sound, the data within the database is consistent and valid, and the information you retrieve from the database will be accurate.