Creating a cron job for job pool for my membership website - whats a good table design?

Question

Hi guys I want to set up a job pool for my membership based website. Users on my website can choose to execute certain tasks that take a while to be done like sending invite emails etc. Instead of having it be run on a form submit, I was thinking of creating a table where an entry would be made for the job requested. Eg a user wishes to send request emails to all of his buddies. Instead of setting a code to email upon a form submit I want to set it up so that when the user submits his form an entry is made in a table for a job. In the backend a cron job periodically checks the table for any job and when it finds one it executes the job based upon the entry made.

For now I'm just setting it to handle email requests made by members of my website - however I want the database design to be scaleable enough to handle different types of jobs so what would be good as a table schema for this kind of job pool table? WHat can I use for a unique id - or do I need a primary key in this table anyway? Should I delete jobs upon completion or merely mark them with a status?

Anyone could answer the above questions and give an idea of a table it would be greatly appreciated.

Answer 1

Even if you do not "need" a primary key it is good to have a primary key because it facilitates manipulation.

Since you are going to be using cron in the backend, you might want to check the at command which allows background tasks to be scheduled. You might find that you do not need a table at all and just leave it to the OS to queue, trigger see what can be done and execute it. You can specify queues from a-z where higher letters run with a higher niceness, i.e. they will steal less CPU from other processes, keeping your website more responsive.

If you schedule your own scripts, it might be good to have a table to report the result of the batch job in with details like time of completion, status, meaningfull description of the status, the user it was run for and if the user has read/acknowledged it so you can give feedback to the user. This is often an achilles heel in background operations that the users find it difficult to "see" what is happening.