What would a basic database structure for user private messaging look like?

Question

I'm using Rails 3.2 (not a ruby on rails question) and coming close to the end of a project I've been working on. Right now I'm building a user private messaging feature.

I've been following http://www.novawave.net/public/rails_messaging_tutorial.html#start but some things don't make any sense, some things don't work.. most likely because that version of ROR is outdated.

I now intend to do this myself now but need some help to get started. I'm going in circles having trouble trying to figure out the best way to implement this.

I do already have something similar.. "wall posting" where several users can comment on 1 wall post in thread like fashion. With this though if the original wall post is deleted then all the comments are too.

With my messaging system I'd like it simple with just Inbox. A user will see messages they have sent and received ordered by sent_at date. I want users to have the ability to delete their messages with out the others being deleted.

What I'm thinking

-I'm thinking a way to achieve this is have 2 copies of messages.

-1 for User A(sender) and 1 for User B(recipient) this way they can indepenently manage messages with out affecting the other user.

So far I have thought about having 3 tables:

1. Messages

2. Message_copies

3. Folders (this will store a row for each user which is created at signup automatically then when ever they receive a message they'll be ..... acts_as_tree... ?? .... erm... blue stars?? ..... back ache.. (my mind starts wandering)..

and this is where my head starts to hurt. I feel like I have no idea what I'm trying to do even though I do. When I feel like yes I think I've got this now I meet a brick wall.

Solution

I was hoping if anyone who has implemented such a feature would be able to provide me info for the tables in a database I need to achieve what I intend to achieve. I 100% know I can do it but just need a kick start. This has been the biggest obstacle since I started this project.

I can't seem to get my head round this and trying to avoid having the same message stored twice.. once for sender and once for recipient. That way just doesn't seem right.

I feel it would be best to have 1 table where all messages are stored then maybe a folder table for each user created automatically on signup and from his folder table the messages stored in the message table are used by sender and recipient and wen a user actually deletes a message from their folder it will still remain in the message table until the other user sender/recipient also deletes the message from their folder.

Kind regards

Answer 1

How about one table for messages and one table for message recipients? In the recip table you could have a column for "what to do with this message" and options for "keep, replied, delete(don't show), delete(show)", etc. Then you can join to this to get whether or not a given recip cares about what happens with this message.

Making multiple copies of a message seems like overkill.