Database concept for messaging function

Questions
1

Hi.

If I’d like to implement a messaging feature in my bubble app, how would a solid database layout look like?

My first thought would be to have just one table for all users and messages and to restrict access to all users who are not involved in an actual communication between two users. But this would make the database extremly heavy over time which might lead to high latency and instable behaviour.

Another concept could be to create a “messages” database table for each user. On one hand, this would result in small and handy tables. On the other hand though, this would meen, that 500 users would have 500 “messages” database tables. Could be pretty messy. And I am not sure if a database query with 500 database tables would be very performant.

What do you guys think?

2

Although it may depend on a few things, I’d probably just do something simple like this:

image
image1751×844 36.6 KB

Then, I’d display a repeating group of messages (“from current user to user B” :mergedwith “from user B to current user”), something like this:

image
image1809×978 159 KB

Hope that helps!

3

Hi.
Just think of facebook or whatsapp.

You have users.
You have contacts. When 2 or more people accept a request to join you create a
Conversation.
Each conversation has a list of messages.
Then maybe each convo has a lists of files/images exchanged.

Then you have to join stuff/optimize eg.
Each user has a list of contacts and also a list of conversations.
Each conversation has a list of messages.

I have actually created a playlist on database setup just this weekend. Might help you think about this and how to join data and fields.

regards
Julius