聊天应用数据库中的行太多

Question

I have a laravel application and i want to build an instant chat Module for my application.

I have made a database structure by using the logic of Eloquent relationships (hasMany, BelongsTo).

Here is the structure:

The problem about it is that there is 1 row for every chat between every user.

Which means: (so bad) :(

for 50 users, 50•((50-1)/2) = 1225 rows

for 100 -> 4950

and for 250 users which is possible -> 31125 rows on channels table which will be queried in every message.

I need suggestions to make this smaler somehow

Thanks!

Answer 1

I would create my database like this

**Users**
user_id
username
etc



**Chats**
chat_id
chat_name
etc

**Messages**
message_id
message_user_id     ->foreighn key to users table field user_id
message_text
chat_id             ->foreighn key to chats field chat_id

The amount of rows will be

1(unique chat) + amount of messages

You dont have to save the users in the chat table, you can find them by looking in the messages table field chat_id

If you want to save the users which joined a chat (without posting a message) you could add a table

 **Chat_Users**
 chat_id            ->foreighn key to chats table field chat_id
 user_id            ->foreighn key to users table field user_id

The amount of rows will be

1 (unique chat) + amount of messages + amount of users in chat