Process string before or after insert into mysql

Question

I have a device network where each device has different sensors (temperature, humidity, etc..). Each device uploads raw measured weather data every 10 seconds to a apache/php/mysql REST API. There are a few 100 devices sending data and this will become more over time. Currently the data is added to a mysql table without any processing.

The string format of the device message is something like:

&01AA*020122#

Which means that sensor 01 measured AA and sensor 02 measured 0122.

I want to put the decimal converted hex values in a table so they can be processed further. What I do now is run a script every minute to see what new strings were added to the database. I will then convert these strings to decimal values and put them in a table.

Performance wise I have the feeling that polling new data every minute is pretty silly.

What would be the best way to do this?

1. have the rest api split the string and convert the hex values to decimal values and insert this immediately into the database

2. poll the database for new inserted strings and convert the hex values

3. use a mysql insert trigger to process the string by the database

4. any other ideas.

Answer 1

The best option is the one with the fewest things that can go wrong. This is the "the web API converts the value and inserts it into the database." There is no significant amount of computation necessary, and changes are you don't even need to convert it into decimal first. You can insert using the unhex function in MySQL (docs).

If you were going for something a bit more enterprisy, this is the model for a message queue where something would be getting the data from the sensors, and sticking into a message queue as a producer, and then something else takes the messages from the queue as a consumer and does something with them (insert them into a database). More on this at a RabbitMQ tutorial.

That said, that message queue is overkill and you can just stick it in the database from the start. Do that.

Answer 2

Option 1. The string data should definitely be processed into more appropriate data types before insertion. That measurements are provided in hexadecimal is a detail of data transfer, and isn't important when it comes to storage (hexadecimal being merely a representation of numbers). Note that the script wouldn't be converting the string data to decimal (which is just another representation), but to integers (which is a data type).

Also, processing the data allows the table to be normalized. The raw string isn't even 1^st normal form, as it contains a repeating group of compound data (sensor number & measurement).