Database Structure for The Best Efficiency

Question

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

Answer 1

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)

Answer 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”?