Saving emails

Question

If you have a site which sends out emails to the customer, and you want to save a copy of the mail, what is an effective strategy?

If you save it to a table in your database (eg create a table called Mail), it gets very large very quickly.

Some strategies I've seen are:

1. Save it to the file system
2. Run a scheduled task to clear old entries from the database - but then you land up not having a copy;
3. Create a sepearate table for each time frame (one each year, or one each month)

What strategies have you used?

Answer 1

Data you want to save is saved in a database. The only exception that is justified is large binary data (images, videos). Who cares how large the table gets? If the mails are automated and template-based, you just have to save the variable parts anyway. The size will be about the same wherever you save it, but you probably already have a mechanism to backup your database, so you won't have to invent one to handle millions of files.

Answer 2

Lots of assumptions: 1. You're running windows / would like an archive in windows 2. The ability to search in the mails is important.

Since you are sending mails to your customers there isn't any reason you can't bcc a mail account of your own. Assuming you have a suitable account on your own server then I'd look at using MailStore (home) to pull the mails out from your account and put them into it's own compressed database.

Answer 3

It depends on the content of your email. If it contains large images. I would plump for the file system. Otherwise if your Mail table table is getting very large very quickly I would go for the separate table, archiving off dead customers.

Answer 4

I don't agree that gmail is an effective backup for business data.

Why trust your business information to a provider who makes no guarantees of service, or over who you have no control whatsoever?

Makes no sense to me.

Depending on how frequently you need to access this information, I'd say go with the filesystem or database archive. At least that way, you have control over your own data.

Answer 5

Another option (depending on the email content) is to not save the email, but make sure you can recreate the email by archiving the original content that went into generating the email.

Answer 6

We save the email to a database table. It really doesn't get that big that quickly. We've a table with 32,000 emails in it (they're biggish emails too @ 50kb per email) and with compression, the file only uses 16MB.

If you're sending a shed load of email, then know that GMail(free) currently only allows 7GB of data. I'd be happy holding that on a disk.

Answer 7

I'd think about putting in place some sort of general archiving functionality. How you implement that depends on your specific retrieval needs.

For example if you wish just to retrieve emails sent to a particular customer for a certain month then stocking them in an appropriate heirachy on the File System (zip them up if necessary) should be simple to do. You might want to record a list of sent emails in a database table with a pointer to the appropriate directory but a naming convention for your directories and files might be sufficient

You might not need to access very old emails very infrequently so you might archive these to DVD for example if online storage is a problem

If you're wanting to often search the actual content of emails then your going to have to put the content in a DB table or use an indexer like Lucerne to examine the files stocked on disk