Custom Alerts System - Need a hint

[Rohlan]

Hello everyone.

 

Maybe this belongs in the Application Design forum but I wasn't too sure so I posted it here.

 

For my current application, I am trying to develop an "Alerts System".

 

The main function of the app is to allow a user to manage "jobs". Jobs can be created and modified with ease, each job carries a ton of information, customer id, date, hour rate, duration, address, state, payment state, payment method, etc. So basically we have the table "jobs" with all these fields and some more.

 

When a job has been changed to "Executed" state, it means the job has been finished and is awaiting payment by the customer, if the payment is already due and the "payment state" has not yet been filled, then an alert should be generated so the manager can quickly take care of it.

 

I have been brainstorming on how to achieve this, this alert system has to query the DB often to check for "Executed" jobs with "payment = 0". Right now there are more than 50k jobs in my DB, and to run this query all the time is unthinkable...

 

I can make it so the query runs when the manager decides to check for Alerts manually, but that defeats the purpose of what I am trying to achieve.

 

This alert system would also be useful for more things:

• customers with no telephone number information or other sensitive data
  
• jobs that have had their final date pass and still haven't been changed to "executed"
  
• customers who haven't had any jobs done in the last year so the manager can either run a campaign or take some other action
   
  

 

I wonder if such a thing is achievable nowadays without compromising the application's speed too much or taxing the server resources too much.

 

Any suggestions would be very appreciated.

[sinista]

could you not make another table filled with the completed jobs that have not been paid for, when they are paid for remove them from the additional table.

 

perform a cron on the new table that will only have unpaid jobs, run it once a day or hour or when ever,

 

any good?

 

[jcbones]

Index...Index...Index

 

I would put an index on any column that is being used as a search parameter.

 

You could also run a Cron on the tables at midnight, or at end of shift (when resources are un-used), and generate a file of all the alerts.  Displaying them on login, at shift start.

You could also make a manual system that runs concurrently with the above, or as a stand alone.

[Rohlan]

I have little to no experience with CRON but will definitely look into it.

And indexing... also not not sure how that helps since I'm not sure as to what the usage is, but will also research immediately.

[Rohlan]

Ok I was doing some quick reading and this caught my eye:

 

Indexes are something extra that you can enable on your MySQL tables to increase performance,cbut they do have some downsides. When you create a new index MySQL builds a separate block of information that needs to be updated every time there are changes made to the table. This means that if you are constantly updating, inserting and removing entries in your table this could have a negative impact on performance.

 

The bolded bit worries me as this "jobs" table does suffer a lot of updates to its entries on a hourly basis.

 

Insight?

[sinista]

I cant answer the question behind the indexes, have never used them,

 

but a second normalised table, that includes only unpaid jobs will be small in theory, since you wouldn't leave a job being unpaid for that long.

 

the job needs to hold little to no information more than likely a time stamp, and reference to either your customer of job table.

 

so small table, small search time less load on the system

 

 

 

[Rohlan]

Tables:

jobs (* fields)

unpayed_jobs (id field)

 

1. Run a cron over night or every so often to populate the unpayed_jobs table from the jobs table.

2. Display data from the unpayed_jobs table as alerts on every page on the application, limit alerts to prevent heavy taxing on resources, provide link for "all alerts" on its own page.

 

How does this sound?

[sinista]

yeah that's more or less how I would do it, normalise as much of the data as you can

[Rohlan]

Ok i've decided to go the Cron job way.

 

Thank you guys for the help.