<p>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?</p>

<p>If you save it to a table in your database (e.g. create a table called Mail), it gets very large very quickly.</p>

<p>Some strategies I've seen are:</p>

<ol>
<li>Save it to the file system</li>
<li>Run a scheduled task to clear old entries from the database - but then you wind up not having a copy;</li>
<li>Create a separate table for each time frame (one each year, or one each month)</li>
</ol>

<p>What strategies have you used?</p>

[{'answer_id': 51538, 'author': 'Matthias Winkelmann', 'author_id': 4494, 'author_profile': 'https://Stackoverflow.com/users/4494', 'pm_score': 2, 'selected': False, 'text': "<p>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.</p>\n"}, {'answer_id': 51541, 'author': 'Paul Hargreaves', 'author_id': 5330, 'author_profile': 'https://Stackoverflow.com/users/5330', 'pm_score': 2, 'selected': False, 'text': '<p>Lots of assumptions:\n1. You\'re running windows / would like an archive in windows\n2. The ability to search in the mails is important.</p>\n\n<p>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 <a href="http://www.mailstore.com/en/mailstore-home.aspx" rel="nofollow noreferrer">MailStore (home)</a> to pull the mails out from your account and put them into it\'s own compressed database.</p>\n'}, {'answer_id': 51542, 'author': 'Johnno Nolan', 'author_id': 1116, 'author_profile': 'https://Stackoverflow.com/users/1116', 'pm_score': 1, 'selected': False, 'text': '<p>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.</p>\n'}, {'answer_id': 51546, 'author': 'Galwegian', 'author_id': 3201, 'author_profile': 'https://Stackoverflow.com/users/3201', 'pm_score': 3, 'selected': False, 'text': "<p>I don't agree that gmail is an effective backup for business data.</p>\n\n<p>Why trust your business information to a provider who makes no guarantees of service, or over who you have no control whatsoever?</p>\n\n<p>Makes no sense to me.</p>\n\n<p>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.</p>\n"}, {'answer_id': 51552, 'author': 'Andrew', 'author_id': 826, 'author_profile': 'https://Stackoverflow.com/users/826', 'pm_score': 2, 'selected': False, 'text': '<p>Another option (depending on the email content) is to not save the email, but make sure you can <em>recreate</em> the email by archiving the original content that went into generating the email.</p>\n'}, {'answer_id': 51555, 'author': 'seanyboy', 'author_id': 1726, 'author_profile': 'https://Stackoverflow.com/users/1726', 'pm_score': 0, 'selected': False, 'text': "<p>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. </p>\n\n<p>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. </p>\n"}, {'answer_id': 51617, 'author': 'Tom Carter', 'author_id': 2839, 'author_profile': 'https://Stackoverflow.com/users/2839', 'pm_score': 0, 'selected': False, 'text': "<p>I'd think about putting in place some sort of general archiving functionality.  How you implement that depends on your specific retrieval needs.</p>\n\n<p>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 <em>pointer</em> to the appropriate directory but a naming convention for your directories and files might be sufficient</p>\n\n<p>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</p>\n\n<p>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</p>\n"}]
['https://Stackoverflow.com/questions/51528', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/5352/']