August 23, 2022 at 1:17 pm
I'm in discussion with my development team regarding the best way to model staff members taking annual leave.
Rather than post a lot of DDL stuff it's easier to describe narratively.
I personally think that the best way to model this is as follows.
A table called StaffLeaveEntitlement, where a member of staff has their annual leave allowance stored (in a column called TimeAllocated).
A table called StaffLeaveRequests, where a member of staff has their request for holiday or other absence stored (in a column called TimeRequested).
To find out the ratio/percentage of leave taken it is necessary to do the following:
StaffLeaveEntitlement.TimeAllocated - SUM(StaffLeaveRequest.TimeRequested)
However, my team thinks that a better approach is to have an additional column in the StaffLeaveEntitlement table called TimeTaken, which is updated whenever a row in the StaffLeaveRequest table has its status set to Approved.
My feeling is that having this additional column (which merely replicates SUM(StaffLeaveRequest.TimeRequested)) adds an extra possibility for error. For example, if at some point the service fails so that the StaffLeaveRequest.TimeRequested is updated, but the StaffLeaveEntitlement.TimeTaken column is not.
I hope my approach to describing the problem domain is compliant with forum best practice.
Edward
August 23, 2022 at 1:50 pm
Either method will work.
Personally I prefer using the TimeTaken column. Triggers with transactions can be used to ensure that the times are kept in sync. Of course you'd want a proc that can "true up" the totals just in case, but it should never be needed.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
August 24, 2022 at 6:27 am
This was removed by the editor as SPAM
August 24, 2022 at 12:18 pm
How are leave cancellations handled?
Just checking that you're not including any future leave that could be cancelled.
For example, before today, it's regarded as booked and taken, whereas after today, it's just booked because it's not yet taken.
August 24, 2022 at 12:29 pm
The SUM would be of StaffLeaveRequest.TimeRequested where the status was either APPROVED or PENDING. I omitted some of the nuance for clarity.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply