Linking versus Embedding
In the past linked documents in e-Quip have been exactly that; links to a document held in a file system. This particularly well-suited to situations where documents are not just read by e-Quip but by arbitrary users. Policy documents, spare part catalogues, brochures, contracts etc are all documents which almost certainly exist in structured locations within the server file system. For example, there may be a Contracts folder on a server with sub-folders for separate years, suppliers etc. In this situation linking database objects to documents in the file system is probably the best option. The approach is simple, reduces database size and allows flexibility with document storage.
However this method is not without limitations. Perhaps the most obvious is that if somebody moves or renames the file in the file system then the e-Quip link will no longer be valid. Another shortcoming is that if a user creates a link to a document which is not on a server, perhaps on the local C:\ drive, then the document will not be visible to other users. A similar problem arises from Windows drive mappings. If a user links to a document using a mapped drive (e.g. Z:\\Docs\Contracts\2015) then the link will only function for users with a Z:\ drive which maps to the correct location.
Linking presents an even bigger problem for documents which need to be visible via the web. In general web browsers can’t see file systems and vice-versa.
Now that mobile working is becoming more widespread a new situation has arisen; that of users with no access to a file system at all. When working remotely (perhaps using the Detached Client or a Windows tablet) the user may well not be able to see the file server. A remote engineer may capture a customer’s signature on a job sheet but how can this document be linked to an e-Quip job? The obvious answer is to actually store the document within the database. As from version 3.3.0 e-Quip now supports this.
How Embedded Documents Work
When creating a document link the user now has a choice of either creating a linked or embedded document. If the latter is chosen then the entire binary contents of the file are written to a field in the ExplorerDocument table in the database. The original extension (.xlsx, .docx, .txt, .jpeg etc) is also written to a field in the same table. Once saved the original document path is deleted. You may have linked C:\My Documents\Test Results\2015\12345.txt but once the file contents are stored in the database the only part of that which is relevant is the extension: “.txt”. This is because Windows uses file extensions to work out how to open documents. This is the same for any document, whether linked or embedded.
Above you can see how the original location is no longer needed once the document has been embedded.
When you try to open an embedded document e-Quip creates a temporary file to hold the contents. This might be C:\Users\Graham\AppData\Local\Temp\tmpB830.tmp. The extension is changed to match the original file – in this cae: C:\Users\Graham\AppData\Local\Temp\tmpB830.txt . Then the document contents are copied from the database to the temporary file. From then on the document is passed to Windows to be opened in the same was an linked documents.
Which is Best?
There is no right or wrong answer to this; each method is appropriate in the right circumstances. For displaying files in a browser or for remote engineers then embedding is an attractive solution. For other documents, Microsoft research has indicated that documents smaller than 256K are handled more efficiently in a database than in a file system, with the reverse being true for large files. However, these performance differences are only slight and speed of access may not be a key deciding factor. If you reference very large documents then linking may be better for another reason. When the e-Quip client processes embedded documents it must hold the binary data in its internal data structures in order to construct a temporary file. i.e. the data is read into memory and then copied from memory to a file. But while the file is being built it too resides in memory. This means that clients with limited memory may not be able to process very large embedded documents.
If a document is stored in the database then it can only be viewed by e-Quip users. This makes this approach unsuitable for publicly visible documents. However, a counter-argument says that public documents are more likely to move or be renamed, in which case the e-Quip links would break. This implies a management overhead in keeping links working. The counter-counter-argument says that if the original document changes then the embedded data is out-of-date while a link would not be. As I said, there is no right or wrong answer.
If you have a problem with engineers accidentally linking to documents on their local computers then embedding will solve that problem.
Of course, storing documents in the database will make the database larger. Although it is possible to configure SQL-Server so that different tables (and indexes) are physically located on different storage devices, it is much more difficult to do this than it is to simply store contracts on the P:\ drive, pictures on the Q:\ drive and test results on the R:\ drive.
What Next?
Depending on the feedback that we receive we plan to enhance e-Quip roles so that default linking/embedding preferences or rules can be defined. We are already in the process of creating a new browser application for e-Quip which should be ready before Christmas 2015. We will certainly be extending this to support embedded documents