Storing messages in a database

Giganews Newsgroups
Subject: Storing messages in a database
Posted by:  Adam H (ahairsub5@removeme.jvxp.com)
Date: Tue, 19 Feb 2013

Hi,

I was just looking for tips as to how best to store emails in a database.

In the past, I've had a table that has various fields such as Folder,
From, To, Subject, Body - and a separate table for attachments.

The problem with this is that only selected parts of the message are
stored. (Any other data such as headers, or the original message are
lost for good).

I have recently realised that I could simply save the entire message
(Tidmessage) into a stream, and store it as a BLOB value.

However, going back through that table to find the relevant message
might be a bit of a pain.

So far - I can see three options that are available to me - and I was
wondering if there are additional options, or whether there are some
traps I need to be aware of:

Option 1: Store all the information separately in fields of their own.

This is effectively what I have done in existing projects, as per above.
The benefits are that you can easilly view the table and navigate to the
email message you may want to view. The downside is that only certain
parts of the message are saved.

Option 2: Store all the information into a Blob field. The benefits of
this is that the entire message content is saved, and can be restored at
any time, but navigating through the table to find a particular email
would be difficult.

Option 3: Store both some separate information (body, email, subject,
date, etc) as well as the idmessage in a BLOB file. The benefits is that
I have easy navigation, as well as access to the actual message content
itself, but would significantly increase the size of the data stored.
(Effectively doubling up a lot of information).

Option 4: Store all the information only into a blob - and extract data
from the idmessage 'on the fly' to create 'calculated' fields such as
subject, date, email message. The benefits are easy navigation, the
entire message is stored, and I'm not using any additional storage space
than what is required. The downside (more of an unknown to me at this
stage) is whether this is possible to achive, and/or what the
performance issues might be in extracting data for each record on the
fly for calculated fields.

So... I was just wondering what others prefer to do when it comes to
storing emails in a database, and/or whether you have any comments or
suggestions as to my options above.

I'd like to try Option 4, but don't want to waste a lot of time if this
has been tried before and is bad performance, or whether there is a
better way to achieve this.

Thanks & Regards

Adam

Replies