Creating and Indexing Email Database using SqlCe

Question

I am creating a simple email client program. I am using MS SqlCe as a storage of emails. The database schema for storing the message is as follows:

StorageId int IDENTITY NOT NULL PRIMARY KEY,
FolderName nvarchar(255) NOT NULL,
MessageId nvarchar(3999) NOT NULL,
MessageDate datetime NOT NULL,
StorageData ntext NULL

In the StorageData field I am going to store the MIME message as byte array. But the problem arises when I am going to implement search on the stored messages. I have no idea how I am going to index the messages on top of this schema.

Can anyone please help me in suggesting a good but simple schema, so that it will be effective in terms of storage space and search friendliness as well?

Regards,

Anindya Chatterjee

Answer 1

Some notes, not too helpful, I'm afraid:

• I believe rfc5322 limits the length of any individual line in an email message to 999 characters. While it is possible to extend a header field over multiple lines, it seems to me it's a reasonable upper bound for the length of a message-id.
• SQL CE doesn't support full-text search, so basically you'll have to write your own search engine. Chop the text up into words, then create a table of words, paired with a field containing a list of the StorageId fields they link to. Quite a bit of work, and you're probably better off with a third-party solution
• Consider adding a "parent" field that links threads together based on their message-ids and In-Reply-To/References headers.