I'm hoping I can get some advice.
I have accounting ledgers in my PHP/MySQL system, with invoices that hit the ledger at intervals (for this example, let's just say once a month). I also have late fees that need to be applied after a certain date. The "posting" date of the invoice, as well as the "this is late" date are in the DB already.
I'm wondering which is a better method to run my script which applies a ledger item automatically (once a month) - and enters a late fee automatically (will also be once a month, if needed). For all users of the system, these dates are different - and like I said, all stored in the DB. My options as I see it are:
Have a CRON job that runs once a day (i.e. 12:00am each day) - and goes through every item, doing work if the date conditions are met.
I think I like this method, because it takes care of everything all at once, at the same time every day.
I am worried about this method because: (1) what if my server is down when my CRON job was scheduled, does CPANEL completely skip that day now? And (2) if I have 100k or even 100Million entries to sift through, will this be an unbearable load on my server whenever this is ran?
Call this script to "do work" if date conditions are met, only when that related user logs in.
The reason why I don't like this method as much, is because it gets increasingly complex to tell how many invoices to apply, and how many late fees to apply. Further, I have an "administrator" user who can see EVERYTHING from EVERYONE - that user may have not up to date ledger info if the tenant hasn't logged in recently enough...
The reasn I do like this method, is because it obviously is a less load on the server...
Perhaps I'm over thinking things. Not sure... but advice would be much appreciated. Thanks.
I think the best bet is to use a cron tab, for all the benefits you mention. I would add to that though that if you use a transaction safe table, such as INNODB, and log the completion of the cron tab, if the table crashes during the crontab the changes will be reverted and you can check the log to see what dates had crashes so that you can manually make the changes. In fact, if you log the crontab task's completion in a table, you can use that log (grabbing how many days ago the script was run last) to automatically handle missed days.
As far as the server load, you probably won't have too much of a problem updating many records at once. The thing is that with a properly structured query, you should only be updating (or inserting) based on the records that have a date that matches your selected parameters. You shouldn't need to actually loop through every record in the db.