Need some help designing my messaging system

Rails
1

I am trying to create a messaging system for my users but I’m having a
hard time designing my db. This is what i have in mind, but I am not
sure if its the best approach.

user has_one inbox
user has_one outbox

inbox has_many messages
outbox has_many messages

inbox table
user_id

outbox table
user_id

messages table
box_id (refers to either inbox or outbox - how?)
from_id
to_id
msg_status (new, sent, read, deleted, etc.)
subject
body

does this make sense? is there a better way of acheiving this? since
both inbox and outbox both have many messages, how do I created the
association between them? will the addition of a from_id and to_id in
my messages table slow anything down? is there a better way or an OO
way of getting this information w/o hitting the db alot?

Thank you,
R

2

Ramin Bozorg wrote:

I am trying to create a messaging system for my users but I’m having a
hard time designing my db. This is what i have in mind, but I am not
sure if its the best approach.

user has_one inbox
user has_one outbox

inbox has_many messages
outbox has_many messages

What do you need the boxes for? Why not:

message belongs_to sender, class = User
message belongs_to recipient, class = User

user has_many sent_messages, class = Message, foreign_key = sender_id
user has_many received_messages, class = Message, foreign_key =
recipient_id

table messages:
sender_id
recipient_id

3

you could take that a step further even to handle multiple recipients:

(untested)

users

id int
…

messages

id int
sender_id int
…

messages_recipients

message_id int
recipient_id int
read_at datetime default NULL
…

class User < ActiveRecord::Base

received messages (INBOX)

has_and_belongs_to_many :received_messages, :class_name => “Message”,
:join_table => “messages_recipients”

sent messages (OUTBOX)

has_many :sent_messages, :class_name => “Message”, :foreign_key =>
“sender_id”
end

class Message < ActiveRecord::Base

recipients of the message

has_and_belongs_to_many :recipients, :class_name => “User”,
:join_table =>
“messages_recipients”, :association_foreign_key => “recipient_id”

sender of the message

belongs_to :sender, :class_name => “User”, :foreign_key => “sender_id”
end

then to take it even FURTHER and implement a ‘read_at’ field in the join
table to show what’s been read and when. this could allow you to
hilight
unread messages or even a simple read receipt.

4

That’s the part I was confused about myself. I didn’t think I needed
the boxes, but wasn’t sure how to write the Rails code to handle it. I
dont quiet yet have a good understanding of how the ActiveRecord stuff
works. Can you guys recommend a chapter in the book that will explain
all of this better?

On 12/19/05, Chris H. [email protected] wrote:

class Message < ActiveRecord::Base
unread messages or even a simple read receipt.

recipient_id
http://lists.rubyonrails.org/mailman/listinfo/rails

Rails mailing list
[email protected]
http://lists.rubyonrails.org/mailman/listinfo/rails

–

5

Chris, thanks for the multiple recipients tip. Something I will
definitely consider for my application.

On 12/19/05, Ramin [email protected] wrote:

(untested)
…
has_and_belongs_to_many :received_messages, :class_name => “Message”,
=> “messages_recipients”, :association_foreign_key => “recipient_id”
On 12/19/05, Andreas S. [email protected] wrote:

sender_id

–