ASP.NET 3.5 Enterprise Application Development with Visual Studio® 2008: Problem - Design - Solution

7.3. Solution

Let's turn our focus back to Mary and her requirements for requesting paid time off. After returning for another conversation with Mary, this is what I was able to drag out of her:

• The system must capture who is making the request.

• The system must capture the day being requested off.

• The system must enable the user to choose either a full day or a half day.

• The system must enable the user to choose among three types of days off: vacation, personal, or unpaid leave.

• The system must enable the HR administrator to enter each employee vacation bank for vacation and personal days.

• The system must ensure that people cannot request a day off that is a company holiday.

• The system must allow the user to copy each user's vacation bank from year to year.

• The system must allow a user to carry over five unused vacation days each year.

7.3.1. PTO Table Design

OK, this isn't so bad. Figure 7-14 shows the data model that will support these requirements.

Figure 7.14. Figure 7-14

The PTORequest table is the main table that contains all the user's requests. The ENTUserAccountId field reflects the user making the request. The RequestDate field stores the date the user is requesting off. The PTODayTypeId field is a foreign key to the PTODayType table that denotes either a full day off, or just the a.m. or p.m. The PTORequestTypeId field is a foreign key to the PTORequestTypeId table that ...