Try to understand how well can denormalize work wi

Question

So basically i'm building all previous application with mysql to store data and consider switch to firebase database which make me feel a little bit crazy. I'm trying to implement a chat system but there is some trouble when understanding how it work. Based on the Denormalizing Your Data is Normal I get the idea and concept on how does denormalize work.

I know I could just create a message structure to store chat message like below (the example from doc)

"messages": {
    "one": {
      "m1": {
        "name": "eclarke",
        "message": "The relay seems to be malfunctioning.",
        "timestamp": 1459361875337
      },
      "m2": { ... },
      "m3": { ... }
    },
    "two": { ... },
    "three": { ... }
}

But here is my scenario let say I having a group chat which can contain up to 1000 people.

• Scenario 1 : I'm allow user to delete the message. When a user send something gross to the group chat and one of the member feel uncomfortable and decide to delete it, this will lead other user unable to watch the message.
• Scenario 2 If one of the member kick out by the admin I want him to still be able to watch previous message before he have been kick out.

For scenario one in the version on SQL I create a table contain the messageID with the userID when a user delete and do a query as below which the message is still available for other user.

SELECT * FROM message WHERE roomId = $userId AND messageId NOT IN ($messageId) 

But firebase database doesn't support this feature, so the only way I can think right now is to write every new message to all the member tree

"messages": {
    "john": {
      "m1": {
        "name": "eclarke",
        "message": "The relay seems to be malfunctioning.",
        "timestamp": 1459361875337,
        "groupId" : "1"
      },
      "m2": { ... },
      "m3": { ... }
    },
    "jimmy": { ... },
    "paul": { ... }
}

So when a user delete the message I could just easily remove it from his own message node so the message is still available for other user.

Here come my confusion if the group chat is up to 1000 people I might need to write to 1000 user node tree, will it be a problem when writing for such amount of data in firebase database? I mean problem like the time of writing take very long, disk space etc. Or is there a better way to do this? If this is fine I think I will be able to solve my scenario 2 as well. I feel terrible sorry if any misunderstanding from the title because it is really hard switch from sql to no sql.

Thank for any advance.

Answer 1

No, you don't need to write data to 1000 user nodes you only need to write it once. To achieve this, you need to have a database structure that looks like this:

Firebase-root
  |
  ---- Groups
  |      |
  |      ---- groupId
  |             |
  |             ---- groupName: "Android"
  |             |
  |             ---- users
  |             |      |
  |             |      ---- userId1: true
  |             |      |
  |             |      ---- userId2: true
  |             |
  |             ---- messages
  |                    |
  |                    ---- messageId1: "Hello" 
  |                    |
  |                    ---- messageId2: "Hy" 
  |                    |
  |                    //and so on
  |
  ---- messages
          |
          ---- messageId1
          |      |
          |      ---- users
          |             |
          |             ---- userId1: true
          |             |
          |             ---- userId2: true
          |
          ---- messageId2
                 |
                 ---- users
                        |
                        ---- userId1: true
                        |
                        ---- userId2: false // false for second message

Hope it helps.