I have an application for a business customer and one of the new components is a time tracking system. The actual time tracking is done (pretty standard schema), but the structure this company uses to price their time is really odd and I don’t know how to properly store this.
0-10 minutes: €16
10-15 minutes: €23
35-40 minutes: €58
more minutes: €63
In short, it’s a completely irregular structure and while I could make a table with a column per interval, that seems to me like a smelly solution. A second table with a foreign key and
price also doesn’t sound ideal. There should also be multiple of these timetables (different per employee / role).
Does anyone have any advice on storing this?
Store it in a way that makes sense, which is to say ignore the interval approach and just store the breakpoints like such:
Role | MaxMinuteAmt | Cost Plumber | 10 | 16 Plumber | 15 | 23 Plumber | 40 | 58 Plumber | 32767 | 63
The primary key for this table is
(Role,MaxMinuteAmt). This prevents any data integrity issues with improperly entered intervals and assists in the quick lookup of the appropriate value.
To return the appropriate
Cost, you would use the following query (which can be hardened into a stored procedure):
SELECT EmpRoleCost.Cost FROM EmployeeRoleCost EmpRoleCost WHERE EmpRoleCost.Role = 'Plumber' AND EmpRoleCost.MaxMinuteAmt = ( SELECT MIN(MaxMinuteAmt) FROM EmployeeRoleCost WHERE Role = EmpRoleCost.Role AND MaxMinuteAmt >= <Number of Minutes> )
So if we query with a duration of 1 minute, the minimum value greater than or equal to that value is 10 and so on. Anything over 40 minutes up to the maximum
SMALLINT value would return the same value (you could go with a larger datatype if needed).
This is assuming that anything up to 32,767 minutes would be charged €63, but my guess is there’s probably some fixed rate once you get above a certain value (say 60 minutes). In that case you’d change the above table to:
Role | MaxMinuteAmt | Cost Plumber | 10 | 16 Plumber | 15 | 23 Plumber | 40 | 58 Plumber | 60 | 63
And create a second table to store the rate for amounts over, along with the duration of the rate (let’s say 15 minutes for this example):
Role | MinuteAmt | Rate Plumber | 15 | 15.75
Then we’d change the query to utilize that rate if no flat cost is found:
SELECT COALESCE ( EmpRoleCost.Cost ,EmpOverage.Rate * CEILING(<Actual number of minutes>/EmpOverage.MinuteAmt) ) AS Cost FROM EmployeeRole EmployeeRole LEFT JOIN EmployeeRoleOverageRate EmpOverage ON EmpOverage.Role = EmployeeRole.Role LEFT JOIN EmployeeRoleCost EmpRoleCost ON EmpRoleCost.Role = EmployeeRole.Role AND EmpRoleCost.MaxMinuteAmt = ( SELECT MIN(MaxMinuteAmt) FROM EmployeeRoleCost WHERE Role = EmployeeRole.Role AND MaxMinuteAmt >= <Number of Minutes> ) WHERE EmployeeRole.Role = 'Plumber'
So in this case if the number of minutes was 65, the cost would be €78.75 (
CEILING(65/15) * 15.75).
See this Fiddle.