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
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 messagesWhat 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 -- Posted via http://www.ruby-forum.com/.
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. On 12/19/05, Andreas S. <f@andreas-s.net> wrote:> > 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 > > -- > Posted via http://www.ruby-forum.com/. > _______________________________________________ > Rails mailing list > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > http://lists.rubyonrails.org/mailman/listinfo/rails >_______________________________________________ Rails mailing list Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org http://lists.rubyonrails.org/mailman/listinfo/rails
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 Hall <christopher.k.hall-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> 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. > > > On 12/19/05, Andreas S. <f@andreas-s.net> wrote: > > 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 > > > > -- > > Posted via http://www.ruby-forum.com/. > > _______________________________________________ > > Rails mailing list > > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > > http://lists.rubyonrails.org/mailman/listinfo/rails > > > > > _______________________________________________ > Rails mailing list > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > http://lists.rubyonrails.org/mailman/listinfo/rails > > >-- - Ramin http://www.getintothis.com/blog
Chris, thanks for the multiple recipients tip. Something I will definitely consider for my application. On 12/19/05, Ramin <i8ramin-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> 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 Hall <christopher.k.hall-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote: > > 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. > > > > > > On 12/19/05, Andreas S. <f@andreas-s.net> wrote: > > > 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 > > > > > > -- > > > Posted via http://www.ruby-forum.com/. > > > _______________________________________________ > > > Rails mailing list > > > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > > > http://lists.rubyonrails.org/mailman/listinfo/rails > > > > > > > > > _______________________________________________ > > Rails mailing list > > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > > http://lists.rubyonrails.org/mailman/listinfo/rails > > > > > > > > > -- > - Ramin > http://www.getintothis.com/blog >-- - Ramin http://www.getintothis.com/blog