Storing time pricing information

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 interval 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.