The Database Schema

The Online Appraisal application uses the existing ElectricComp database to store the information regarding policies and appraisals. Some tables are already present in this database, such as tblLogins and tblEmployees, and these are used for validation purposes. These tables are maintained by other applications, like the payroll system and time office system. Figure 18.7 shows the database schema for the ElectricComp database.

Figure 18.7: The schema for the ElectricComp database.

The database schema includes the following tables:

• tblLogins. Contains login data, such as employee code, password, and the roles that the employee is allowed.

• tblEmployees. Contains employee data such as employee code, employee name, country, designation, grade, hire date, reviewer's employee code, and department.

• tblQtrlyAppraisals. Stores the appraisal information, such as appraisal ID, employee code, reviewer's employee code, appraisal period, appraisal year, score, and status.

• tblQtrlyAppraisalDetails. Contains the information about the quarterly appraisal, such as appraisal ID, targets given for a quarter, self-ratings, and the ratings given by the reviewer.

• tblAnnualAppraisalResults. Contains annual appraisal data, such as employee code, evaluation year, final score, and recommendations.

• tblPolicies. Contains the policy information, such as salary increments for a score range. It also stores the recommendations.

Relationship Between the Tables

Every employee has four quarterly appraisals, each with a maximum of six targets for an employee. The Employee code is the foreign key in the tblQtrlyAppraisals table. The Appraisal ID is a foreign key in tblQtrlyAppraisalDetails. The Employee code is also a foreign key in the tblLogins and tblAnnualAppraisalResults tables. There's a one-to-many relationship between

• tblEmployees and tblQtrlyAppraisals

• tblQtrlyAppraisals and tblQtrlyAppraisalDetails

• tblEmployees and tblAnnualAppraisalResults

Database Structure

The employees' login data is stored in the tblLogins table, as shown in Figure 18.8.

Figure 18.8: The design of the tblLogins table.

The tblLogins table contains EmpCode as the primary key column, and its datatype is varchar. The other fields in this table are Password and Roles.

The information about employees is stored in the tblEmployees table, as shown in Figure 18.9. The other columns in the table are EmployeeName, Designation, Grade, DateOfJoining, ReviewerCode, and Department.

Figure 18.9: The design of the tblEmployees table.

The tblQtrlyAppraisals table stores the quarterly employee appraisal information. The structure of this table is shown in Figure 18.10. Its primary key column is AppraisalID. The datatype for this field is auto number. The other columns in the table are EmpCode, ReviewerCode, AppraisalPeriod, AppraisalYear, Score, and Status.

Figure 18.10: The design of the tblQtrlyAppraisals table.

The tblQtrlyAppraisalDetails table stores the targets and ratings for a particular appraisal ID. The structure of this table is shown in Figure 18.11. The primary key columns for this table are AppraisalID and TargetID. The other columns in the table are Targets, SelfRating, and ReviewerRating.

Figure 18.11: The design of the tblQtrlyAppraisalDetails table.

The tblAnnualAppraisalResults table stores the annual scores and recommendations for each employee for every year. The structure of this table is shown in Figure 18.12. The tblAnnualAppraisalResults table contains EmpCode and AppraisalYear as the primary key columns. The other columns are FinalScore and Recommendations.

Figure 18.12: The design of the tblAnnualAppraisalResults table.

The administrator uses the tblPolicies table to set the salary increments and other benefits and recommendations, based on the final scores obtained by the employees. The structure of the tblPolicies table is shown in Figure 18.13. This table contains PolicyID as the primary key column. The datatype for this field is auto number. The other columns in the table are Country, StartRange, EndRange, SalaryIncrement, and Recommendations.

Figure 18.13: The design of the tblPolicies table.