Previous installments in this series have covered the core email architecture of Time Matters, Internal Time Matters Email,and Internet Email in Time Matters. In the last installment of this series we cover the impact and connectivity issues of the email attachment.

History of the Time Matters Email Attachment

Throughout the history of Time Matters, email attachments have been somewhat problematic, primarily due to the resource load they put on the database. Email attachments, in part, contribute to the instability of the professional edition of Time Matters and are susceptible to database corruption. Several recent changes to the Time Matters architecture have significantly improved this issue. For instance, as of Time Matters 9 SR3, email attachments may optionally be stored outside the database. I highly recommend this option when using the professional edition. A more robust option, like TMTools, is offered by OTB Consulting. TMTools has a more robust method for managing attachments outside the database.

Anatomy of the Time Matters Email Attachment

When an email with an attachment is sent from, or received into Time Matters, the attachment creates entries in several tables and is either stored within or outside the database.

Email Table
This table stores the primary email information for email saved to the “Email List”.  The “Attachment” column in the Email Table is extremely important as it identifies the email as an email having an attachment (for instance when Time Matters displays the paper clip in the user interface).

Attach
This is the table that contains the physical attachment or (as of 9+) the acknowledgment that the attachment file is being saved externally.

AttLnk
Is the table that relates (or matches) an email to the attachment(s) for that email. Since a single email can have many attachments, this table will provide the connectivity between a single email and many attachments.

Inbox Table
New internet email is saved to the Inbox Table. No external attachments or attachments records are created for new email stored in the inbox. This is a very important factor that many Time Matters sites do not recognize.
 

The Life Cycle of a Time Matters Email Attachment

CASE #1: Create and send an Internet email with a single attachment to one recipient.
In addition to the activity covered in previous articles, the following attachment specific database activity occurs:

One record for each attachment is created in the Attachment Table.
One record for each attachment will be added to the AttLnk Table.

CASE #2: Create and send an internal email with a single attachment to one recipient.
In addition to the activity covered in previous articles, the following attachment specific database activity occurs:

One record for each attachment is created in the Attachment Table.

For each attachment, one record for the sender and each recipient will be added to the AttLnk Table (internal emails are duplicated for each user). For example, there will be four AttLnk records when sending two attachments to one person.

CASE #3:  Receiving an Internet Email
In addition to the activity covered in previous articles, the following attachment specific database activity occurs:

Initially there is no activity in the Attachment or AttLnk Tables. New internet email is initially saved to the Inbox Table and all attachments are part of the Inbox.Mime column in the Inbox Table. This column holds a special version of the email that must be parsed to create the individual components of the email, including the attachment.
 

Implementing What We Know

Summarizing: When an email with an attachment is sent from, or received into Time Matters,  the attachment creates entries in several tables and is either stored within or outside the database. There are several factors that contribute to managing this process.

Options for saving Email Attachments outside the database should be carefully considered. When choosing to save files outside the database take measures to provide mirrored backups at the same time the SQL Database is backed up. Failing to do so can potentially create orphaned attachments in the event of a database restoration. 

Attachments for incoming Internet email are initially saved in the Inbox Table.

Attachments are not perished until the email record is permanently removed from the system. Many firms end up leaving a lot of deleted email in both the “Deleted” folder of the inbox as well as the “Recycle Bin”. This accumulation of otherwise hidden attachments can create unnecessary overhead in the database.

When the sender of an internal email deletes the email record, the attachment will persist until the recipient deletes the email. If the recipient leaves the firm, it may be possible for the attachment to become orphaned in the database.

Data Equity’s SharpShooter has several tests to aid in the management of Email Attachments.

• < Prev
• Next >