Design Process

1. Defining a Mission Statement and Mission Objectives

These are statements that represent the general tasks your users can perform against the data in the database. You use these objectives to support your mission statement and to help you determine various aspects of the database structure.

There are two separate groups of people who will be involved in defining the mission statement and the mission objectives.
►    The first group the database developer (you)

►    The second group the database developer (you again), management personnel, and end users, and it will be responsible for defining the mission objectives.

2. Analyzing the Current Database

The second phase in the database design process involves analyzing the current database, if one exists. Depending on your organization, the database will typically be a legacy database or a paper-based database. 

Another part of the analysis involves conducting interviews with users and management to identify how they interact with the database on a daily basis.

Next, you use the information you’ve gathered from the analysis and the interviews to compile an initial list of fields. You then refine this list by removing all calculated fields and placing them on their own list—you’ll use these calculated fields later in the design process. 

Once your initial field list is complete, you send it to your users and management for a brief review and possible refinement. You encourage feedback and take their suggestions for modifications into consideration. If you think the suggestions are reasonable and well supported, you make the appropriate modifications, record the list in its current state, and move on to the next phase.

3. Creating the Data Structures

You define tables and fields, establish keys, and define field specifications for every field.

4. Determining and Establishing Table Relationships

The fourth phase of the database design process involves establishing table relationships. You conduct interviews with users and management once again, identify relationships, identify relationship characteristics, and establish relationship-level integrity.

After you’ve identified the relationships, you establish a logical connection between the tables in each relationship with a primary key or with a linking table. What you actually use depends upon the type of relationship you’re establishing between the tables.

Next, you determine the type of participation and degree of participation for the tables in each relationship. In some cases, these participation characteristics will be obvious to you due to the nature of the data stored in the tables. In other cases, you’ll base the participation characteristics on specific business rules.

5. Determining and Defining Business Rules

During this phase, you’ll hold interviews, identify limitations on various aspects of the database, establish business rules, and define and implement validation tables.

6. Determining and Defining Views

The sixth phase of the design process involves determining and defining views. Here you’ll conduct interviews (once again), identify various ways of working with the data, and establish the views.

7. Reviewing Data Integrity

First, you review each table to ensure that it meets the criteria of a properly designed table and you check the fields within each table for proper structure.
Second, you review and check the field specifications for each field.
Third, you review the validity of each relationship, confirm the relationship type, and confirm the participation characteristics for each table within the relationship.
Finally, you review the business rules that you identified earlier in the database design process and confirm the constraints you’ve placed on various aspects of the database.

You’re ready to implement your logical database structure in an RDBMS program once you’ve completed the entire database design process. However, the process is never really complete because the database structure will always need refinement as your organization evolves.