I'm not sure if this is the best SE section to ask my question. If not, I kindly ask you to help me to find out the best one.
Developing a web application in php and MySql I'm in doubt how to store a huge quantity of data from arrays.
Scenario
The application receives text messages from clients around the world. The number of clients might reach several thousands. Each one sends few times per day a message with a text payload (json).
The json contains few objects to identify the client itself and an array of about 10 objects, in example:
{
"id_client": "abcdefgh",
"id_msg": 3245,
"datetime": "20180308101130",
"data": [
{ "temp": 20.5 },
{ "humy": 45.1 },
{ "batt": 12.0 },
{ "cnt1": 123456 },
...
]
}
I need to store this information in the database.
My thoughts
The keys of the dictionary passed in the data
array are stored into a separate table, along with their description, units, etc...
The first three fields (id_client
, id_msg
, datetime
) are required to identify exact message received. Hence, when I receive such a message, currently, I create a row in a table for each entry in the dictionary:
+-----------+--------+----------------+------+--------+
| id_client | id_msg | datetime | key | value |
+-----------+--------+----------------+------+--------+
| abcdefgh | 3245 | 20180308101130 | temp | 20.5 |
| abcdefgh | 3245 | 20180308101130 | humy | 40.1 |
| abcdefgh | 3245 | 20180308101130 | batt | 12.0 |
| abcdefgh | 3245 | 20180308101130 | cnt1 | 123456 |
+-----------+--------+----------------+------+--------+
I wondering if this is the right approach for such a situation. I'm afraid about the number of lines: if I have 1000 clients that send a message with 10 entries 2 times per day, I will append:
- 1000 * 10 * 2 = 20.000 rows per day
- 20.000 * 30 = 600.000 rows per months
- 20.000 * 365 = 7.300.000 rows per year
The data should be online at least year-by-year in order to make queries and statistics.
What about this approach? Is there something better for this kind of applications?