3) Store the subject line (and possibly the message body) in a
separate field from the attachments.

In fact, have a separate table for the attachments altogether.

EmailMessage
(
ID integer
Sender Varchar([something])
Subject Varchar([something]),
OtherHeaders([something])
Body Varchar([something])
)

EmailAttachment
(
ID integer,
MessageID integer,
AttachmentName VARCHAR([something]),
AttachmentIcon BLOB
Attachment BLOB
)

This means that you can efficiently get the name and Icon for each
attachment and only download the attachment when they double click
it. Alternatively, a background thread could retrieve the attachments
for the current message into a local cache ready for when they are
requested.

I am not aware of the size limits for BLOBs (they are there but you
are talking GB)

As long as the text appears instantly, it will seem fast to the user.
By the way, emails encode attachments generally in Base64, which is
pathetically verbose (but necessary). You can compress the emails
before storing them and the compression resource requirements will
very quickly be offset by the over the wire savings.

> 1) one is to save email in a blob field of the Firebird database.
> 2) save email as .msg file, but keep the directory path in the

database.

>
> For option 1), my concerns is the capacity, compatibility and
> reliability of the BLOB field. What is the size limit of a blob

field,

> and can it store all different kind of attachments from emails, like
> pictures and video clips?

Apart from the max size limitation, BLOBs do not care what you put in
them. They can not be indexed, and are stored uncompressed.

Is there performance issue when retrieving

> an email with large attachments?

Of course. But this can be managed if the attachments and text are
stored separately

Adam