CODEWITHSUNDEEP
androiddatabasesqlite
Tobias Reich
Tobias Reich

Reputation: 5160

Android chat - database architecture

For my new app - a (kind of) chat for Android - I want to store my messages locally in a database. I recently got a glimpse on how others do it. So I saw the SQLite database of Whatsapp on my mobile. There they are putting all chat messages of every conversation in the same table. The point is when reading / writing new messages sorting hundred thousands of messages in order to show only the ones with your conversation partner seems a lot of efford.

I thought by myself I wanted to make one table for each chat. So I can simply load the messages directly without checking the author - since the table name would be the user name I'm talking to. I assumed this might go much faster - especially if you have many messages.

My question is, why are they doing it all in one table? Is it wrong to make one for each chat (E.g. any limitations like "Android can only handle 10 tables per Database")?

I know it's a very theoretical question but perhaps someone might give me a hint how to do it properly!

Thanks!

Upvotes: 1

Views: 1525

Answers (1)

Tobias Reich
Tobias Reich

Reputation: 5160

Okay, after testing and thinking a lot about the database structure. We agreed to do it as most apps do. Even though loading speed showed to be a bit faster to have one table for each conversation, the impact only showed on many messages (e.g. 100.000+ messages in total).

The down side is a higher cost for organization and a structure which is more prone to errors. For instance chat tables named by the conversation UUID. But a UUID might start with a number, which was not allowed for table names.

Besides some systems seem to have difficulties with creating new tables on runtime and also many DAO frameworks like GreenDAO (which seems to be the best option for us) are having trouble organizing the database structure on runtime. This makes it impossible to create new tables and relations.

Last but not least, the idea to generate one table for each conversation proved to be difficult to organize in situations like "getting the last chat message". We thought about finding the values from each table (i.e. get the single last message of each table and use their values) but this makes a lot overhead.

So long story short, we made three tables

  1. Chat messages
  2. Authors (List of participants for each chat)
  3. Chats (list of conversations)

Hope this helps.

Upvotes: 1

Related Questions