1

I'm having trouble figuring out how to store, retrieve and calculate running balances in PHP. It is not related to PHP - it is more programming theory, but anyway:

Imagine the following table:

id | user_id | date       | start_time | end_time | total | balance | notes
19 | 17      | 2014-04-01 | 09:00:00   | 17:30:00 | 7.50  | 0       | 
20 | 18      | 2014-04-02 | 09:00:00   | 18:30:00 | 8     | 0.5     | 
20 | 18      | 2014-04-01 | 09:00:00   | 17:00:00 | 7     | -0.5    |

Displaying the balance for the month is relatively easy:

  1. Figure out how many hours are available in the month (April 2014). (Let's assume 7.5 working hours per day and we don't work weekends):

    22 Days * 7.5 = 165 Hours

  2. Count the total time booked for a month using SQL, eg the following:

    SELECT SUM(total) FROM booking WHERE date >= '2014-04-01' AND date <= '2014-04-01';

    //30

  3. Balance then is total hours worked minus total available hours:

    30 - 165 = -135

I need to display a running total of these values in an efficient way across multiple months, therefore I didn't just want to SUM() the total column as there could be thousands of rows in the future, surely this is in-efficient?

Therefore I thought about having a running_totals table, with a row per-user, per-month which would auto-update whenever a record is edited, added or removed. However, this would be problematic due to the fact the records in previous months can be updated, it would not only require updating the current month row, it would have to cascade to the months in front of that month. For example if you were to go back to March to update a record, I would have to update the running_balance row for March and April.

Unless you SUM all records in running_balance, and work out the balance in PHP? Again this seems inefficient to me as the application grows, I would rather get it right at the beginning.

Other Problems

I only want to calculate the running balance up until today, however you are allowed to book time in the future, this should be ignored when calculating the running balance.

How I currently do it

I imagine hopefully pretty fast, but I think it's too complicated and there has already been bugs in it. So I need to get a simple, efficient solution in quick.

Inserting a new record

  1. First time you book a day in a new month, I work out the total hours available in that month( EG April 2014)

    165

  2. Load the running_balance row (1 per user) and minus the month total from it:

    0 - 165 = -165

  3. Add the currently booked total on (Eg 7.5):

    -165 + 7.5 = -157.5

Displaying the running balance

  1. Load the row from running_balance. Calculate how many hours are remaining in the current month. Eg we are on 28th April so there are 15 hours remaining (2 Days * 7.5).

  2. Add that to the running balance:

    -157.5 + 15 = -142.5

  3. Your running balance is therefore: -142.5

This is just proving to be too complicated with lots of edge cases especially when editing & removing. In particular when removing the last entry for a particular month. So, I am wondering if there is a simpler approach, you clever folk can think of?

Thanks.

PS: The source for the code is here: https://github.com/WeareJH/Flexitime It's not really in any state to be used by the public before issues have been ruled out and docs written, but it may help with context.

Aydin Hassan
  • 1,465
  • 2
  • 20
  • 41
  • 1
    In my experience, with things that are running a "balance" so to speak, say financial/credit card data even... you don't allow modifications of existing data. You only allow the issue of "credits" which are subsequently just a different transaction. Regardless, if you need to edit that's fine in your case. Storing the balance in the same table as the transactions themselves is against the principles of database normalization though. Separating that is ultimately a better idea. – skrilled Apr 28 '14 at 16:57
  • That makes sense, that is sort of what I'm doing, having a running_balance row for each user. I just hate the logic of having to subtract the months total hours for each new month. – Aydin Hassan Apr 28 '14 at 19:18
  • You shouldn't worry about efficiency if you haven't yet tested and confirmed it's a problem. 1 entry per user per day is a really small ammount of data even with a few thousand users. – Jakub Kania Apr 29 '14 at 07:52
  • I guess as well as the efficiency - which as you said is not a massive issue, it's a lot to do with the way I am storing and calculating. It doesn't seem like a clean process and there are lots of edge cases. So I would like to hear how others would solve the problem. – Aydin Hassan Apr 29 '14 at 08:51

1 Answers1

1

A "materialized view" can store aggregated values which are maintained by the database, resulting in fast read performance at the expense of write performance.

However, "MySQL doesn't support materialized views natively, but workarounds can be implemented by using triggers or stored procedures or by using the open-source application Flexviews."

See also other answers discussing stored aggregates and normalization in general:

For other background, see also:

Community
  • 1
  • 1
Riley Major
  • 1,904
  • 23
  • 36