JSON string vs multiple rows in MYSQL

[Division]

I am working on a phone system.

I've decided to save phone contacts as JSON string in the phone table, cuz its no heavy overload.

When it comes to messages between players, I am not quite sure if I should store huge JSON strings.

What would you do? Something like JSON:

messages: [
{
	sender: 01234567
    messages: [
    {
    	timestamp: x,
        message: 'xyzxyzxyzxyz'
     },
     {
     	timestamp: x2,
        message: 'xyz2xyz2xyz2',
     }, .....]
},
{
	sender: 76543210,
    messages: ....
}
   

or would you store multiple rows in database like:

phone_messages:

- ID (PK, AI): int
- sender_phone (FK): int
- receiver_phone (FK): int
- timestamp: DateTime
- message: string

 

With multiple rows its quite easier to maintain when it comes to filter and max limit while a JSON always needs to be deserialized, serialized, ...

 

[alberto2345]

I've wondered the same. 

Maybe this might help:  https://stackoverflow.com/questions/5986745/json-column-vs-multiple-columns

Edited July 4, 2019 by alberto2345

[DevGrab]

Why you would store JSON Strings to a database when MySQL is made for massive data flows? Even it it makes no difference for you, because your database will never be that huge like a big online portal.. But tell me why you would store them in plain?

 

Just create different tables at mysql.. eg. "phone_messages", "phone_whatever".. and use your queries or orm's to receive them. After this you create a JSON string at the server side and send it over to the client.

 

And if you open your messages.. make sure you load up just 5 and if you scroll up or down in your phone.. you load up the next ones.

Edited July 4, 2019 by DevGrab

[sparx]

Multiple rows with dynamic loading as mentioned in one of the previous comments. Use JSON for less important areas of your application. You'll have an easier, faster and more secure experience with RDBs.