Topic: Email History Table Design Question

I have a program where I can send emails and I'm looking for a way to create a history table for emails sent.

I'm debating over one main thing. If a user sends the same email to multiple people, should I store that data in one row and have the "to" field just be able to accept multiple usernames? or should I store each email sent in an individual row?  I'm leaning towards the latter, but am interested in people's thoughts.

Thanks!

matthewvb

Re: Email History Table Design Question

I definitely prefer keeping it in a single row. It will be less duplication in the database. I don't know of any email client that would store it as separate rows.

If lumping multiple email addresses in one "to" field feels wrong, consider creating a new table to hold the addresses and doing a one-to-many association.

Railscasts - Free Ruby on Rails Screencasts

Re: Email History Table Design Question

Thanks ryanb. I like that idea of putting the "to" addresses in a 1:many db. I think I'll give that a whirl and see where it takes me.

Great idea!

matthewvb

Re: Email History Table Design Question

I had a similar issue with designing a messaging system in my app -- both the sender and recipient are guaranteed to be either real users or virtual users (the system, etc).  It solved a lot of problems to have each message inserted into the table twice -- once for the sender, once for the recipient. 

What this lets you do:

* Have a consistent interface towards showing/hiding/deleting/etc emails without caring whether they're to you or from you. 
* Saves from having to do additional code writing to allow the sender to delete the mail without borking it for the recipient.  Instead of having two  separate status columns and some logic to say

if (user == mail.sender)
  mail.sender_status = "DELETED"
else
  mail.recipient_status = "DELETED"
end
mail.save

(and then write cleanup code if both people delete the message to actually remove it from the system), you get to just fire off mail.delete.

* Not really a factor for my application, but one ID per table row plus an index means find_all_mail_by_user_id executes *fast*.