Need some advice on DB design


#1

Hey everyone. I’m trying to create a table of “messages” for my users.
It works just like an email application. Each user can send and
receive messages to/from other users in the system.

I’m not sure what sort of relationship to create between the user and
the messages. Is it has_many or has_and_belongs_to_many? I want each
message to have the following information:

from
to
subject
body
status (new, read, replied, trashed)
type (user_msg, system_msg, friend_request, etc.)

I also want to be able to filter the messages based on their status
for the user. So they can view all their sent messages, new messages,
etc.

I appreciate any feedback. thanks.


#2

I’m not sure what sort of relationship to create between the user and
the messages. Is it has_many or has_and_belongs_to_many? I want each
message to have the following information:

has_many


#3

I believe it should be

user has_many messages
message has_one user

Also, you may want to rethink your statuses. A message could be both
read and replied, or read and trashed. Perhaps have a flag for
read/unread separate from the status.

You may also want to add a created_at field for your messages to keep
track of their when they occurred. And perhaps even add a parent_id
for the parent message if you plan on supporting conversation chains.

Tom


#4

On 12/7/05, Tom D. removed_email_address@domain.invalid wrote:

I believe it should be

user has_many messages
message has_one user

That should be message belongs_to user


#5

Thanks alot Tom. I’ll work on your suggestions.

Is parent_id part of the whole ActiveRecord magic? or is it something
that I have to manually set and then save when working with my model?

Has anyone written a full messaging system in their application and
would care to share their code? I would only use it to learn and make
my own and not just copy it.

Thank you

On 12/7/05, Tom D. removed_email_address@domain.invalid wrote:

track of their when they occurred. And perhaps even add a parent_id

the messages. Is it has_many or has_and_belongs_to_many? I want each
for the user. So they can view all their sent messages, new messages,
removed_email_address@domain.invalid
http://lists.rubyonrails.org/mailman/listinfo/rails


#6

Good catch Pat… my mistake.

The parent_id would have to be implemented by you. The created_at is
built in to RoR.

Tom


#7

One other thing, saying that he has to implement parent_id is a bit
misleading. What he needs to do is put an integer field called
parent_id in the messages table, and then to assign a parent to a
message:
messageobject.parent = parentobject

Or he can access it through the Parent#messages array:
parentobject.messages << messageobject

Tom isn’t wrong, I just wanted to make sure that nobody misunderstands
and starts doing stuff like
messageobject.parent_id = parentobject.id

Pat