I'm developing a system to handle sending transactional emails to our customers. This is how it works:
An event occurs during the order's life cycle, for example 'shipped'
This event will trigger the creation of an email in the database (email queue)
A separate windows service is polling the db table for new emails to send. When it finds one it calls a Web service with all the required data. It's the Web service's responsibility to handle the actual sending of the email.
My question relates to step 2. When an email triggering event occurs, should I take a snapshot of all the data required by the service (thereby duplicating data and introducing new tables) or should I get the required data from the transactional db tables only at the point where I'm ready to call the Web service.
[Note: I'm not at all concerned with the body of the email, all I'm doing is passing the data to a Web service. The email templates etc are handled by another system. My concern is purely to queue the email notification and pass the required data to the Web service.]