Tips for creating PHP-MySQL background tasks

Question

I am looking for advice on something that I am creating: I have a PHP application that creates, updates and deletes records, but it is tightly integrated into a legacy database other than sql and it becomes really very slow when you start a lot of calls DB I want to make this a more experienced experience for the user, so when the user creates or edits something, all variables, arrays and objects must be written to the MySQL database, and then the background script will start with read these records and process the request to outdated database.

Therefore, I will need one table that tracks the task, and then another table that will track all the variables, objects, arrays and their values.

Here is what I thought about the DB structure of the second table should be:

• Column for storing task_id
• A column to store if var is an array or object, or NULL if it is a simple var.
• Column for storing the name of the array / object var.
• Column if it is an object, then save the type of the object.
• Column for storing the identifier of the array / object group. (To track that vars belongs to an object / array)
• Column for storing the name of a simple var, method or name in an object / array
• Column for storing var value

Here are some examples:

1 | NULL | NULL | NULL | NULL | 'foo' | 'bar' 1 | 'array' | 'foo_array' | NULL | 1 | 'foo' | 'bar' 1 | 'array' | 'foo_array' | NULL | 1 | 'foo2' | 'bar2' 1 | 'object' | 'foo_obj' | foobar_object | 2 | 'foo_method' | 'bar' 1 | 'object' | 'foo_obj' | foobar_object | 2 | 'bar_method' | 'foo' 

Does this sound like an overly complicated approach? Am I crazy and changed my mind? Can anyone think of a better approach to this?

Thanks.

Answer 1

Use serialize ()

It will create a specially formatted string (you can save in your database). The line is different in that unserialize () can translate it back to the original php value. It supports the type, value and structure of variables. Custom objects and multidimensional arrays are not a problem.

You still need part of your db table, it just does a lot of work for you and does it very well.

Answer 2

I would create a table with a primary key (possibly task_id) and then a column to store a serialized hash of variables (or maybe a json encoded one). You can even save this in the source table that tracks the task. I assume that the table will have a status column with values ​​for:

expectations, processing, complete and error

When we do this in the house, we simply use the unsigned tinyint (1) column. You can also add a timestamp field or three when a task has been created, started and completed. All this is pretty straight forward as soon as you start rolling.