How to store VK correspondence in a mobile application?

Question

Qt

Good day, community!
At home, in my free time, I develop a native application for VK, which is not in my mobile OS (Sailfish OS). And I had some doubts about how to save VK correspondence for the client application. DB - SqLite.
All data comes in json format, and I thought it would be easy to parse it and add it to the table. And after writing to the database, show the correspondence to the user at the request of sql. But, in addition to the message itself and its attributes, forwarded messages, attachments and a geotag can come, which in json are represented as objects. I thought to create appropriate tables for each such object, and store only a set of id for the corresponding records in the message table. Create 4 tables in the database - Messages, Geotags, Attachments, FWD_Messages
The algorithm of work assumed the following:
1) a request to VK for messages
2) parsing json, a and writing to the database
3) receiving messages from the database and displaying them to the user.
Also, data about changing the status of messages (especially during active correspondence) is received via longpoll requests, which requires editing the data fields in the message.
Tell me how to build a database?

Answer 1

I think that in principle the concept you have chosen is correct.
Those. we have the following database tables: messages, geo_tags, attachments
Lines in geo_tags and attachments are linked to lines in messages, i.e. in the attachments table you have a message_id column, I think it's clear here. Keeping "nested" messages separately is not correct, they all need to be stored in one table, and there must be an ancestor -> descendant relationship between them, one ancestor can have as many descendants as you like. If VK allows you to have several levels of message nesting (i.e., there can be other nested messages inside nested ones, etc.), then the main thing here is not to do recursions, you need to apply a pattern for storing such relationships, for example, here is a successful pattern https://www. percona.com/blog/2011/02/14/moving-sub...
Be sure to put down indexes and composite indexes based on the logic of your code, this will speed up the selection.