-2

I want a database table that can hold all of the group chat messages from all of the group chats.

  • The max amount of users per group chat would be 20.

  • The max amount of group chats per user would 25-30.

Using Python3 Flask as my backend. (Clarifying for speed expectations when looping on the second option)

The columns would be:

Chat_Name, members,  User_Who_Posted, date_posted,  message

With this configuration the users' names would be split by commas, the date of the message would be added to help with putting the messages in the right order, and a single message would become a new row.

Every time a user opened a group chat in the website it would query the database table and find all the messages from the group chat name and members.

Assume each user has 20 chats in total each containing 200 messages on average. Thats 4000 rows per user. With a million users this would be an average of 4,000,000,000 rows in the database.

With my other suggestion each row would be one entire group chat's worth of messages in one row. In this scenario each user would have 20 rows on average, making the total rows 20,000,000.

In this scenario the table columns would be:

Group_Name,  Members,  messages

In this case i would make the messages column a BLOB format structured as so.

{User:"Users_Name", Date:"the_date", Message:"the_users_message"}, {User:"Users_Name", Date:"the_date", Message:"the_users_message"}, ...

I would just continue 'appending' these to the same row, and when i query this chat i would just loop through the messages and add them to the chat chronologically.

I'm wondering which way would give the fastest retrievals and updates to the chat, I'm assuming the second way with the lesser amount of rows would retrieve the quickest. The downside would be looping through the data once its retrieved.

the messages do not need to be editable

open to other suggestions on how to structure the table or tables

6
  • 1
    Have you considered simply measuring times? Writing and running a simple test program that adds messages in each format is not rocket science, and you will have a realistic answer to your question. What still remains to be seen is whether you're asking the right question, i.e. is storing messages your main optimization target or are query operations more important? Voting to close for not showing own research efforts. Commented Jun 12, 2021 at 8:20
  • 1
    You have to look at "best" in the context of some workload. Such workload would necessarily contain both updates and queries. (If there were no queries, then why store the data at all?) There will be no universal best that works for all workloads, so to find best you'll need to focus on your actual application.
    – Erik Eidt
    Commented Jun 12, 2021 at 9:25
  • @DocBrown Ive updated the question.
    – TheAdmin
    Commented Jun 12, 2021 at 14:35
  • 2
    Cool, you updated with some specifics. To really complete that, say what you mean by speedy/efficient. Are you looking for fastest retrieval when the user opens the app? Cheapest infrastructure footprint? Fastest time to post a new message? What workload should we assume for a group? 5 to 20 users, 100 messages per hour peak? 50,000 users, 100 messages per day peak? Figuring out the right workload assumptions and metrics to use is a good part of answering the question. Voting to re-open.
    – joshp
    Commented Jun 12, 2021 at 16:14
  • @joshp Ok i will add more. Thank you.
    – TheAdmin
    Commented Jun 12, 2021 at 16:20

2 Answers 2

3

It depends what you want to do with the messages, which you don't elaborate (so we have to guess from the two words "group chat").

But this one has almost a general answer (with possible corner case exceptions). You are using a relational database; row per message is the way you can leverage the abilities from the relational database query system, and all its performance optimizations. A relational database will not be designed to split a long string efficiently, that's not its strong capability.

If you want to get the most from the relational database, you need to know how to normalize further; maybe you need more tables for the message data, not just one. That being said, even in relational databases sometimes you will accept some redundancy to gain performance.

The very basic case you mention: it will be more efficient to add a "small" row every message, than update a long string appending data to it (even when this operation may be efficient in the right data structures)

1
  • Ive updated the question.
    – TheAdmin
    Commented Jun 12, 2021 at 14:36
-2

If you put everything into one blob, why do you need a database? And how would you get say all unread messages, if all the messages are inside one blob? And third question, why does my spelling checker insist on changing “blob” to “blib”?

2
  • Ive updated the question
    – TheAdmin
    Commented Jun 12, 2021 at 14:36
  • Ahem. You should listen to the spell checker.
    – joshp
    Commented Jun 12, 2021 at 16:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.