Structure a NoSQL database for a chat application (using FireBase)

Question

Coming from years of using relational databases, i am trying to develop a pretty basic chat/messaging app using FireBase

FireBase uses a NoSQL data structure approach using JSON formatted strings.

I did a lot of research in order to understand how to structure the database with performance in mind. I have tried to "denormalize" the structure and ended up with the following:

{

"chats" : {

    "1" : {
        "10" : {
            "conversationId" : "x123332"
         },
        "17": {
            "conversationId" : "x124442"
        }
    }
},

"conversations" : {

    "x123332" : {

      "message1" : {

        "time" : 12344556,
        "text" : "hello, how are you?",
        "userId" : 10
      },
      "message2" : {

        "time" : 12344560,
        "text" : "Good",
        "userId" : 1
      }
    }
  }
}

The numbers 1, 10, 17 are sample user id's.

My question is, can this be structured in a better way? The goal is to scale up as the app users grow and still get the best performance possible.

Answer 1

Using the document-oriented database structure such Firestore, you can store the conversations as below;

{
   "chat_rooms":[
      {
         "cid":100,
         "members":[1, 2],
         "messages":[
           {"from":1, "to":2, "text":"Hey Dude! Bring it"},
           {"from":2, "to":1, "text":"Sure man"}
          ]
      },
      {
         "cid":101,
         "members":[3, 4],
         "messages":[
           {"from":3, "to":4, "text":"I can do that work"},
           {"from":4, "to":3, "text":"Then we can proceed"}
          ]
      }
   ]
}

---

Few examples of NoSQL queries you could run through this structure.

Get all the conversations of a logged-in user with the user id of 1.

db.chat_rooms.find({ members: 1 })

Get all the documents, messages sent by the user id of 1.

db.chat_rooms.find({ messages: { from: 1 } })

The above database structure is also capable of implementing in RDMS database as table relationships using MySQL or MSSQL. This is also can be implemented for group chat room applications.

This structure is optimized to reduce your database document reading usage which can save your money from paying more for infrastructure.

According to our above example still, you will get 2 document reads since we have 4 messages but if you store all the messages individually and run the query by filtering sender id, you will get 4 database queries which are the kind of massive amount when you have heavy conversation histories in your database.