1

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?

2
  • David's answer is very good. However, you may want to consider a timeseries database, like InfluxDB or Druid. These databases are designed exactly for the purpose of storing values that change over time. I'm leaving out all details because this is actually not what you asked. Mar 8, 2018 at 10:58
  • 7M is not a "big" table, but you could write some awful statistics queries. You need to think about them now.
    – Rick James
    Mar 10, 2018 at 16:24

1 Answer 1

2

You are duplicating the common data on multiple rows which can lead to consistency issues. Look up "normal form" for a discussion about the standard ways to deal with this and help ensure your data is consistent and efficient. Read around database normalisation for more about this.

To break that duplicated data out you could do something like:

                    Message             MessageData
Client              ==============      ================
==============      id_msg    (PK) <--- id_msg (FK) (PK)
id_client (PK) <--- id_client (FK)      key         (PK)
client_name         time_stamp          value
contact_no

You potentially have a second problem though: the message data table presented above is still a property bag or Entity-Attribute-Value (EAV) model which while flexible can be problematical in a number of ways. You'll find many discussions online (including here on DBA.SE) and in print (Bill Karwin's excellent "SQL Anitpatterns" has a relevant chapter) about the pros and cons of EAV structures so I'll not delve into more detail here.

If those array keys in your JSON example are fixed then a better layout might be:

                    Message        
Client              ============== 
==============      id_msg    (PK) 
id_client (PK) <--- id_client (FK) 
client_name         time_stamp     
contact_no          temperature
                    humidity
                    battery
                    cnt1

Or if those keys are common in the data but there are potentially others that and you need to allow any key/value pair, you can use a hybrid of fixed columns and property bag:

                    Message             OtherMessageData
Client              ==============      ================
==============      id_msg    (PK) <--- id_msg (FK) (PK)
id_client (PK) <--- id_client (FK)      key         (PK)
client_name         time_stamp          value
contact_no          temperature
                    humidity
                    battery

7.300.000 rows per year

With appropriate table and index design 7.3 million rows is not a lot at all.

4
  • The keys are optional and selected from a large set of keys available in another table of the db. Each client usually transmits few entries (< 10) but the actual keys can be among the all available. Hence your second hint is better.
    – Mark
    Mar 8, 2018 at 11:14
  • I would go with the 2nd (or 3rd) version, but replace the PK with PRIMARY KEY(id_client, time_stamp). This is for performance and shrinking the table.
    – Rick James
    Mar 10, 2018 at 16:23
  • @RickJames: that would not be valid if it is possible for a client to send two messages at exactly the same time (or at least within the granularity of the date/time type chosen). We don't know enough detail about the system being modelled to know if that is a concern so I defaulted to using a surrogate key instead of assuming that client+time is a valid (guaranteed to be unique) natural key. Mar 11, 2018 at 18:08
  • @DavidSpillett - True. In that case, PRIMARY KEY(id_client, time_stamp, id), INDEX(id_msg)
    – Rick James
    Mar 11, 2018 at 21:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.