Now, consider a situation where you need to change the rate for Candle stand. You would need to change the rate for Ord_Id 0045 also. This problem is similar to the update anomaly; however, you noticed this only when you added this record. This kind of problem is referred to as an insertion anomaly.

Therefore, to design a database without having to encounter these anomalies, you need to normalize the database.

To summarize, the following rules help you design a robust database:

A table should have a unique identifier.

A table should not have repeating values or columns.

A table should store data for only a single type of entity.

A table should avoid columns with null values.

Designing a Database

After applying all the concepts discussed so far, you would arrive at the database structure shown in Figure 7-1 for the CMS database.

FIGURE 7-1 Structure of the CMS database

The following section discusses the details of each table in the CMS database.

128 Project 1 CREATING A CUSTOMER MAINTENANCE PROJECT

The tblWorker Table

The tblWorker table is used to store information about a worker. Table 7-11 shows the details of the tblWorker table.

Table 7-11 Details of the tblWorker Table

The tblJobDetails Table

The tblJobDetails table stores information about the job done by a worker in a particular month. It also stores the information about the amount of work done on a car in a particular month. Table 7-13 displays the details of the tblJobDetails table.

Team-Fly®

After discussing the database design, the next section will look at the design of the forms that display data from the tables in the databases.The next section discusses the forms used in the Customer Maintenance project.

130 Project 1 CREATING A CUSTOMER MAINTENANCE PROJECT

Designing the Windows Forms Used

in Customer Maintenance Project

The Customer Maintenance project includes the WorkerForm, CustomerForm, and

JobDetails forms to access data from the tblWorker, tblCustomer, and tblJobDe-

tails tables, respectively. In addition,the Customer Maintenance project includes the main form, Form1, and the Reports form.

Form1

Form1 contains links that a user uses to view different forms created in the Customer Maintenance project. Figure 7-2 shows the layout of Form1.

FIGURE 7-2 Layout of Form1

The WorkerForm Form

The layout of the WorkerForm form is displayed in Figure 7-3.

FIGURE 7-3 Layout of the WorkerForm form

The CustomerForm Form

The layout of the CustomerForm form is displayed in Figure 7-4.

FIGURE 7-4 Layout of the CustomerForm form

The JobDetails Form

The layout of the JobDetails form is displayed in Figure 7-5.

FIGURE 7-5 Layout of the JobDetails form