# Need some advice on DB design

**URL:** <https://www.ruby-forum.com/t/need-some-advice-on-db-design/52578>\
**Category:** Rails\
**Created:** [December 6, 2005, 9:26pm UTC](https://www.ruby-forum.com/t/need-some-advice-on-db-design/52578 "2005-12-06T21:26:02Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![i8ramin](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/i/e36b37/32.png) [@i8ramin](https://www.ruby-forum.com/u/i8ramin)\
**Post date:** [December 6, 2005, 9:26pm UTC](https://www.ruby-forum.com/t/need-some-advice-on-db-design/52578/1 "2005-12-06T21:26:02Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![i8ramin](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/i/e36b37/32.png) [@i8ramin](https://www.ruby-forum.com/u/i8ramin)\
**Post date:** [December 7, 2005, 1:30pm UTC](https://www.ruby-forum.com/t/need-some-advice-on-db-design/52578/2 "2005-12-07T13:30:45Z")

</div>

> 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

---

<div class="post-metadata">

**Author:** ![i8ramin](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/i/e36b37/32.png) [@i8ramin](https://www.ruby-forum.com/u/i8ramin)\
**Post date:** [December 7, 2005, 6:12pm UTC](https://www.ruby-forum.com/t/need-some-advice-on-db-design/52578/3 "2005-12-07T18:12:27Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![i8ramin](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/i/e36b37/32.png) [@i8ramin](https://www.ruby-forum.com/u/i8ramin)\
**Post date:** [December 7, 2005, 7:13pm UTC](https://www.ruby-forum.com/t/need-some-advice-on-db-design/52578/4 "2005-12-07T19:13:46Z")

</div>

On 12/7/05, Tom D. [removed\_email\_address@domain.invalid](mailto: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

---

<div class="post-metadata">

**Author:** ![i8ramin](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/i/e36b37/32.png) [@i8ramin](https://www.ruby-forum.com/u/i8ramin)\
**Post date:** [December 7, 2005, 6:28pm UTC](https://www.ruby-forum.com/t/need-some-advice-on-db-design/52578/5 "2005-12-07T18:28:51Z")

</div>

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](mailto: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](http://lists.rubyonrails.org/mailman/listinfo/rails)

–

- Ramin  
[http://www.getintothis.com/blog](http://www.getintothis.com/blog)

---

<div class="post-metadata">

**Author:** ![i8ramin](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/i/e36b37/32.png) [@i8ramin](https://www.ruby-forum.com/u/i8ramin)\
**Post date:** [December 7, 2005, 8:56pm UTC](https://www.ruby-forum.com/t/need-some-advice-on-db-design/52578/6 "2005-12-07T20:56:08Z")

</div>

Good catch Pat… my mistake.

The parent\_id would have to be implemented by you. The created\_at is  
built in to RoR.

Tom

---

<div class="post-metadata">

**Author:** ![i8ramin](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/i/e36b37/32.png) [@i8ramin](https://www.ruby-forum.com/u/i8ramin)\
**Post date:** [December 7, 2005, 10:13pm UTC](https://www.ruby-forum.com/t/need-some-advice-on-db-design/52578/7 "2005-12-07T22:13:55Z")

</div>

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