Building the Database

The choice of database technology is usually made more for political reasons than for technical reasons. Database and platform companies have done a good job of convincing the market that these choices are of critical importance. Loyalties and allegiances form around database and platform providers for reasons that are more human than technical. So you should not read too much into our choice of Microsoft SQL Server to persist the data for our application.

The schema that we'll be using is shown in Figure 37-1. The Employee table is central. It stores the immediate data for an employee, along with string constants that determine the PaymentSchedule, PaymentMethod, and PaymentClassification. PaymentClassifications have data of their own that will be persisted in the corresponding HourlyClassification, SalariedClassification, and CommissionedClassification tables. Each references the Employee it belongs to, via the EmpId column. This column has a constraint to make sure that an Employee record with the given EmpId exists in the Employee table. DirectDepositAccount and PaycheckAddress hold the data appropriate to their PaymentMethod and are likewise constrained by the EmpId column. SalesReceipt and TimeCard are straightforward. The Affiliation table holds such data as the union members and is linked to the Employee table via EmpoyeeAffiliation.

Figure 37-1. Payroll schema