Laravel Livechat System Database Design

Question

Laravel version 7.0, MySQL version 5.8

I am going to design database for livechat system. Here are related models & tables.

User model - users table (id, name, email, address..)

Guest model - guests table (id, name, ipAddress)

Team model - teams table (id, name, image)

team_has_users (id, team_id, user_id)

User should be able to send messages to User, Team, Guest.

Here are the possible cases that I could think.

first approach

messsages table (id, content, from_id, to_id, from_model, to_model) // I think this is insufficient to save read_status for each users.

second approach

messages table (id, content)

message_from_to table (id, message_id, from_id, to_id, read_status)

from_id will be id of either users table or guests table.

But to_id and read status is a bit tricky for me.

Can anyone help me?

Thank you

Can you make your question a bit more specific? Why are those fields tricky? — Fabian Bettag
– Fabian Bettag, Commented Jul 31, 2020 at 23:45
Well, Let's say a user sent message to a team. Then all team members should get that message and each team members will get read_status for that message. And also let's say a user sent message to the other user. Then that other user will also get message and read_status. This part is a bit tricky for me and I am not sure which is the best way to tackle all those case. Also, database should be well-designed for Laravel Eloquent Usage. Thanks — LoveCoding
– LoveCoding, Commented Jul 31, 2020 at 23:51

Obzi · Accepted Answer · 2020-08-01 00:31:01Z

2

Models User, Guest & Team look fine too me.

But if you want to track why a user see a Message, you need to track it.
To do it, message_from_to need a team_id :

$table->integer('team_id')->nullable();

Wich will be null if it's a directe message, or contain the Team id's.

PS : You still need from_model & to_model into message_from_to, or you know if it's for User or Guest.

Here is a better version on the database structure :

users
    id
    name
    email
    ...

guests
    id
    name
    ipAdress

teams
    id
    name

(https://laravel.com/docs/7.x/eloquent-relationships#many-to-many)
team_user
    id_team
    id_user

(https://laravel.com/docs/7.x/eloquent-relationships#one-to-many)
messages
    id
    content
    from_id
    from_type
    team_id [nullable]

(https://laravel.com/docs/7.x/eloquent-relationships#one-to-many-inverse)
message_to
    id
    id_message
    to_id
    to_type
    read_status

User & Guest should implement the same class.
With eloquent, you could do :

$user->messages(); // return all message
$message->from(); // return User or Guest instance
$message->to(); // return User & Guest array
$message->isRead(Guest|User); // retourn boolean
$message->hasTeam(); // retourn boolean

Maybe tip : https://laravel.com/docs/7.x/eloquent#query-scopes

edited Aug 1, 2020 at 0:31

answered Aug 1, 2020 at 0:01

Obzi

2,3901 gold badge12 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

LoveCoding Over a year ago

So second approach would be better than first one? Of course with team_id.

LoveCoding Over a year ago

$table->timestamp('team_id')->nullable(); why is it timestamp ?

Obzi Over a year ago

Houps, bad Copy + Paste ... I'm writing a more details response actual.

LoveCoding Over a year ago

Could you please write Eloquent relation too if it's possible?

Obzi Over a year ago

Does Guest can be in Team ?

|

Collectives™ on Stack Overflow

Laravel Livechat System Database Design

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related