Modeling a "chat" database

data_modelling

#1

I am trying to model a simple chat application that has “conversations” that contain “messages”. Each message being a separate document.

The “messages” documents are not synchronized, they serve as a local cache for messages (among other things) that are delivered through XMPP.

I am having trouble modelling the view that shows conversations ordered by “last message” like any messenger app does. Currently we have a separate document that only contains the conversation ID and a “last message date” property that is updated for every single message. This way we can create a really simple view that emits the last date.

Can we find a schema where we don’t have this third document type and have a view that will emit values in a way that allows us to order the conversations by “last message” ?


#2

Hey @jschmid,

Do you mind adding your current document model to this conversation? It will help in coming up with a solution as everyone will be able to see our thought process.

Without getting ahead of myself, have you thought about using a composite key where the first part is say a document id and the second part is say a timestamp of when the message was sent? Then you can create a query around that view and order it.

Let’s start with your current document model and we’ll work our way up :slightly_smiling:

Best,


#3

You could check out https://github.com/couchbaselabs/CouchChat-iOS – it’s a bit dated, but should give you some guidance.


#4

Thank you Traun. It does indeed give me a direction on where to start.

However, I don’t think this is the best solution. It might be! Maybe someone who already did it can share his feelings about this.

The view/query maps the messages and uses the reduce function to fetch the latest message and the latest date from each chat room: see the code. This means that every time they want to display the chat rooms, they have to reduce all the message key/value pairs in order to get the result.

Now, I understand that it is a good case to show the use of map/reduce in an example app, but in the real world, where we might have thousands of messages, is it really efficient to reduce every time?

Any thoughts on that? Thank you


#5

Sorry @nraboy I somehow missed your reply.

The schema is like:

_id: auto-generated
type: "conversation"
name: String
participants: [ID] // List of user IDs
_id: auto-generated
type: "message"
conversation: ID // "conversation"
from: ID // User
to: ID // User
date: Date
message: String
_id: auto-generated
type: "conversation-metadata"
conversation: ID
lastActivity: Date
lastMessage: String

Currently, each time a message is sent/received, I update the conversation-metadata with an updated view of the conversation. The conversation documents are synced between the participants, the conversation-metadata documents are only stored locally to prevent unnecessary traffic. Also, as I said in my OP, message document are only used as a cache. They are delivered through XMPP.

We can create a view of message documents with keys like [conversation, lastDate] like @nraboy suggests. However, I am not sure how to get the ordered list of conversations? Grouping using the first key, then reducing with the last element in each group? As I said in my reply to @traun, it means that the reduce function has to run each time with all the emit key/value pairs. Is that really efficient?

Thanks!