Database Structure for Web Messaging System

Question

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.

Answer 1

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:

---

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.