How should I store outgoing emails such that the recipient also can read the email via a link?

Introduction

Ok, so I have a web application that sends a lot of different emails every day. I want to include a "Click here if you can't read this email" link in the emails.

I'm wondering how to best store this, and how other people do it.

Problem

Today I have a database table like this:

CREATE  TABLE IF NOT EXISTS `mydb`.`httpsemail` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
  `from` VARCHAR(255) NOT NULL ,
  `to` VARCHAR(255) NOT NULL ,
  `subject` VARCHAR(255) NOT NULL ,
  `body` MEDIUMTEXT NULL ,
  `hash` CHAR(60) NOT NULL ,
  PRIMARY KEY (`id`) )
ENGINE = InnoDB

The email contents are generated from many different templates with different customer-related data inserted depending on the recipient.

The table quickly grows to tens of thousands of rows (one row per sent email), with a size of several gigabytes.

I'm not even sure this is a problem. I always retrieve emails by primary key so it's fast. The hard drive is big. However, backups are slow.

Po开发者_StackOverflow社区ssible solutions/improvements

A possible improvement is to regenerate the contents of the email when the user clicks the link instead of storing the generated result in the database. However, the inputs may change in the meantime, and I want the email to be "fixed" - meaning that the user should see the email as it was at the time of sending.

Another improvement might be to automatically delete stored emails older than X days. However, this means the link will stop working some day, depending on the X. A lower X is good for free space, but bad for the user. How to decide X?

Thoughts?

You can still generate a per-view copy, but you'll have to make a copy of all the variable data into a seperate table at the time the email is generated. That would save you have to keep a per-user copy around, but still allow regenerating the same email, even though some of the data has changed in the original parent table in the meantime.

As for the expiry period, that's up to you. a couple months would be reasonable, but it would also depend on the data in the email as well - storing a sale flyer for longer than the sale period wouldn't make much sense.