Innovation isn't driven by force or continuing to ask the same questions.
Innovation is born of passion, freedom and sincerity.

Happy Easter!

--steve s

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.

Having been explicitly told not to modify the Time Matters database, Data Equity is unable to adequately provide two-way database services for our Time Matters add-on products. This issue most directly impacts our newest product, GoogleConnect 4TM. We are releasing GoogleConnect with the restriction of allowing Time Matters to “push to Google” only. GoogleConnect will not update Time Matters with changes or new data from Google. The directive also restricts our ability to meet consumer requests to provide two-way database services for MobileTM.

Data Equity has worked with LexisNexis management by extending proposals as well as volunteering to assist in any effort to overcome this unfortunate restriction. Having worked diligently toward this effort for over two years, we are not hopeful  that the restrictions will be lifted in the foreseeable future.

During the 2009 LexisNexis Practice Management conference, Data Equity received strong support from the Time Matters community by encouraging LexisNexis management to rethink this strategy. Unfortunately, the momentum from this outpouring has subsided.  We encourage our customers and other concerned Time Matters users to continue to let LexisNexis know the value of a vibrant third party community.

With hundreds of sites using our Time Matters add-ons, we expect to continue helping to make Time Matters the best practice management software in the industry.  Data Equity is committed to building on our successes by continuing to improve our existing products, releasing new products, and providing a foundation for the future of practice management.


Steve Stockstill
President, Data Equity LLC