Database Structure for Web Messaging System

mysql database database-design architecture messaging

22,636

After learning the hard way (times ago, during a final project...), I can advise you to separate and organize the things whenever possible. Self-relationship is a nice thing not to be near, when possible (there are rare exceptions). Design your classes at first instance; build then a database in which things fit well, but keeping things simple as they should be.

My preference is... better drawn than said:

Diagram

Here is the MySQL Script exported via MySQL Workbench.

A possible query to list messages from a certain header would be

SELECT
  h.id AS `header_id`, h.`subject`, h.`status`,
  m.id AS `message_id`, m.content, m.`time`,
  IF(m.is_from_sender, x.`name`, y.`name`) AS `written_by`
FROM (SELECT * FROM header WHERE id = @VAR) h
  INNER JOIN message m ON (h.id = m.header_id)
  INNER JOIN user x    ON (h.from_id = x.id)
  INNER JOIN user y    ON (h.to_id = y.id);

You'll see a personal preference of mine to bit fields. For instance, you don't really have to remember a certain from_id more than one time, once your purpose is a two person messaging system.

22,636

Author by

deka

Updated on September 20, 2021

Comments

deka over 2 years
I want to make an web messaging system like facebook have. I already think of many alternative for the database structure, but not sure which is the best practice for it. I have two alternative here, the first is using two table, the second is using three table but make a cycle in ERD.

First: Two Table, where the message table refer to itself
```
user
----------
id
name

message
--------------
id
from_id
to_id
message_id --> refer to this table itself, to make me know which message is the topic
subject
content
time
status --> inbox, outbox, archive
read --> read, unread
```
Second: Three Table, but make a cycle in erd
```
user
----------
id
name

message_header
--------------
id
from_id
to_id
subject
status --> inbox, outbox, archive
time

message
--------
id
message_header_id
content
time
read --> read, unread
author_id
```
Personally, I like this structure, because it's only use one message header and many message (content). The author_id itself cannot be removed because I need it to know whether the message is at the left side (as a sender) or right side (as a receiver). This system is just for two person messaging system.

Basically this two table is the same, but which is the best practice to implement this messaging system? Thank you before.
Abhishek about 8 years

Should the message be chained by order of reply to a certain message or should they be listed as per timestamp
leonardo_assumpcao about 8 years

Using reply order (higher ids first) may be a little cheaper, but you may consider timestamp ordering (of course, with an appropriate index) more friendly in case, let's say, someone answers without internet and it takes some time to effectively send the message to the database. That said, you decide :>
Ahmed Samir Shahin almost 8 years

What is the use of "is_from_sender" attribute?
leonardo_assumpcao almost 8 years

It tells you whether the message's author is the one specified in table header with id=from_id or id=to_id.
Max O. about 7 years

This is good sql practive because when you have a seperate header, you don't double store from and to id's, it's not just good for the sake of seperating itself.
Harun-Ur-Rashid over 3 years

A message is always written by the author, so what is is_from_sender?
leonardo_assumpcao over 3 years

Hi, it tells you whether the message's author is the one specified in table header with id=from_id or id=to_id.