Geek Answers Handbook

Table Structure for Messaging

I am looking to create a messaging system that handles the conversation just like Facebook does. I am wondering what is the best way to do this regarding the structure of the table. Will I have one table like:

  id
 reply_id - the id of the original message that started the conversation
 to_id
 from_id
 subject
 content
 date_sent
 read_status

Or two tables:

  table 1 - for the start of new messages

 id
 to_id
 from_id
 subject
 content
 date_sent
 read_status

 table 2 - when someone replies to a message

 id
 message_id
 to_id
 from_id
 subject
 content
 date_sent
 read_status
+4
source share
3 answers

I would say one table. Why duplicate identical data? In addition, you will remove the source of the error when your message data structure is changed without taking care that the other table is identical to the first.

+1
source

It depends on many things, but trying the first guess may be a self-referential table of "messages." For instance:

message: sender_id: User recipient_id: User in_reply_to_id: Message subject, content, etc

The message looks like this:

  belongs_to :sender, :class => 'User' belongs_to :recipient, :class => 'User' has_many :replies, :dependent => :destroy belongs_to :in_reply_to, :class => 'Message'

This will allow you to create a response tree (because the message may be in_reply_to the message, which in turn may be in_reply_to another message). You might also consider using something like act_as_ordered_tree for more flexibility and control.

+2
source

The real best answer: MySQL is an extremely poor tool for building email and chat systems.

A messaging system already exists. Chat system already exists. Why are you trying to reinvent the wheel?

-5
source

More articles:


All Articles