Mobile Groupchat Database Design

Working with Data
, ,
1

I am building a mobile groupchat app using the MERN stack and want to understand if this datamodel is scalable and makes sense. The app should support realtime groupchat (for community members) and 1-1 direct message chats. I am wondering if what I have is an overkill by having two membership models - Community membership which only stores which users are part of the community and conversation membership which stores who is part of a given “chat” - which stores all members of a community chat and also used to store 1-1 DMs. Since the users in a groupchat for a community is an unbounded list though, I don’t see a way around splitting the models up.

Additionally, the messages model is not the best to support groupchats because i need the user’s name and profile picture to display in the chat. If I embed these values for easy access, then I can easily access them when i get all messages for a chat in a single call - but then if the user updates their profile I will have to update all the records. If i dont embed the values then I will have to call to get the user’s name and profile pic for each message that’s loaded. I am not as concerned about the profile picture changing since I can always have that stored as the same path but if the name us updated or hometown of the user is updated for example I would have to update all the message records which is a huge update operation.

Community

const communitySchema = new mongoose.Schema({
  name: {type: String, unique: true, required: true},
  description: {type: String, required: true},
});

Community Membership

const conversationMembershipSchema = new mongoose.Schema({
  conversation_id: {
    type: mongoose.Schema.Types.ObjectId,
    ref: 'Conversation',
    required: true,
  },
  community: {
    _id: mongoose.Schema.Types.ObjectId,
    name: String,
    description: String,
  },
  user_id: {type: mongoose.Schema.Types.ObjectId, ref: 'User', required: true},
  type: {type: String, default: 'MEMBER'},
  status: {type: String, default: 'ACTIVE'},
});

Conversation

const conversationSchema = new mongoose.Schema({
  type: {type: String, default: 'COMMUNITY'}, // Types DM, COMMUNITY
  status: {type: String, default: 'ACTIVE'},
});

Conversation Membership

const conversationMembershipSchema = new mongoose.Schema({
  conversation_id: {
    type: mongoose.Schema.Types.ObjectId,
    ref: 'Conversation',
    required: true,
  },
  community: {
    _id: mongoose.Schema.Types.ObjectId,
    name: String,
    description: String,
  },
  user_id: {type: mongoose.Schema.Types.ObjectId, ref: 'User', required: true},
  type: {type: String, default: 'MEMBER'},
  status: {type: String, default: 'ACTIVE'},
});

Message

const messageSchema = new mongoose.Schema({
  sender_id: {
    type: mongoose.Schema.Types.ObjectId,
    ref: 'User',
    required: true,
  },

  
  conversation_id: {
    type: mongoose.Schema.Types.ObjectId,
    ref: 'ConversationMembership',
    required: true,
  },

  community_id: {
    type: mongoose.Schema.Types.ObjectId,
    ref: 'Community',
    required: false,
  },

  // set if its a thread message
  message_id: {
    type: mongoose.Schema.Types.ObjectId,
    ref: 'Message',
    required: false,
  },

  content: {type: String, required: true},
  image_url: {type: String, required: false},
  timestamp: {type: Date, default: Date.now},
});