0

Imagine you have an employee database and a table for each employee which contains their start date. There are other tables which describe their vacation allowances based on role, seniority and month of hire. There is also a table which contains the vacation used for each employee.

The question:

From a general, balanced perspective of performance and ease of code and maintenance, is it better to compute an employee's vacation balance by evaluating all the table data or to store the available vacation in buckets which are then adjusted as vacation is taken.

It seems to me the overhead and complexity of calculating the available vacation, then computing the vacation which has been taken is unnecessary and a more efficient and graceful solution would be to create a table which holds the available vacation, populate it at the beginning of the year and decrement it as vacation is taken.

1 Answer 1

5

Computers don't get tired.

Nobody has so many employees that the summation over a well-indexed set of rows of vacation actually taken would grind their servers to a halt.

What is a problem is precalculated data getting out of whack with respect to your raw transactional data. If you're tracking vacation you're going to need to keep a list of days taken no matter what, for audit purposes, if nothing else. What happens if an employee challenges your calculation of their vacation taken? The only way you'll be able to justify your number is to go back and show the details.

Since you'll already have the transactional details, there's not much point in keeping a precalculated sum of these details.

From a code complexity/maintenance standpoint you're not really saving anything. You write the code that calculates the remaining vacation entitlement once. It's a single select statement that has three or four more keywords in it than the single select statement that would read a precalculated row. That's not much of a savings.

What you're thinking about is often called pre-optimization and it's something to be avoided, because it actually makes things worse more often than it makes things better.

4
  • This is exactly the type of insight I was hoping for. I will wait to mark the answer, but a huge thank you! May 27, 2017 at 12:17
  • I've run into cases where we keep both. For example, imagine tracking inventory of 2 liter bottles of Coca Cola at a Wal-mart that has been open a decade. Typically you will have a "what do I have on hand by SKU" table that shows the CURRENT count, and also a inventory transaction table that shows you receiving and selling items. If you only have the transactional table, you would have to sum up 10 years of inventory to know how much Coke you had on hand today.
    – CalZ
    May 27, 2017 at 14:37
  • 1
    @CalZ Believe me, I've done lots of data warehousing and there are plenty of good reasons to keep denormalized data for reporting. This question is about employee vacation data not product inventory at a huge retailer, so the scale is an important consideration for this question, I think.
    – Joel Brown
    May 27, 2017 at 17:19
  • 1
    Sure, if it was only 1,000 employees I would say don't bother pre-calculating. If we're talking Wal-mart's 1,000,000+ employees, I might want to pre-calculate into some form of aggregate table.
    – CalZ
    May 28, 2017 at 23:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.