The Content Wrangler
Is Microsoft Access a Technical Publications Content Management System?
July 14, 2006
6 Comments
Mary writes: “My boss has this crazy idea that Microsoft Access can be used as a content management system for technical documentation. He wants me to write topic-based content and use Access as a CMS. Can I use Microsoft Access to manage technical documentation? If it can’t be done, what concrete reasons do I give my boss? If it can be done, do you know of anyone who is doing it?” What are your thoughts? What should Mary tell her boss?
Mary:
I asked the members of Content Management Professionals (http://www.cmpros.org) to help you out. Here’s what I learned.
Barry from X.Systems, Inc. (http://www.xsystems.com) offered the following:
1 – Access is a database that limits fields to the standard 255 characters. Not very hospitable to text. Anyone attempting to make tables and fields represent text must develop a highly complex (one might say impossibly complex) table structure to allowing text more than 255 characters to be stored in multiple database fields. Frankly, the work involved to do even a simple structure makes this approach unusable.
2 – File system is a good text content manager: files can be of any size; files can be infinitely nested; access can be restricted; visibility can be modified to meet reasonable needs. The challenge with the file system is that it doesn’t support multiple views or searches across the repository. That is where Access could come in. If each topic related text module were stored in a well designed file system directory structure and metadata representing it and its location were stored in Access for seaching, then Access could be used to create “pick lists” of text modules for use.
3 – This approach, which we have seen in use by a number of clients, takes best advantage of Access and File Explorer without asking too much of either.
Think it is fair to say that text should not be stored in Access itself (or in Oracle except as a BLOB or CLOB neither of which Access supports.) Sounds also like the boss hasn’t done much thinking about he/she wants to do and what level and type of management would be appropriate.
Barry
X.Systems, Inc. Content Solutions for the Internet Age
703-819-2135 FAX:703-935-5616 http://www.xsystems.com
More to come. If you’ve got an opinion that would help Mary, please leave a comment below.
Scott Abel
TheContentWrangler.com
Travis, another knowledgable Content Management Professional also responded. He offered a different vantage point.
Mary, sure applications can be built inside of MS Access. Let’s just assume that your technical documentation format is structured similarly to the DITA format. I don’t see any reason why MS Access couldn’t be built to perform like a CMS to store DITA structured data.
Should it be done? I guess it depends on what type of features you want out of a CMS because you would have to build all of it, that’s the interface, business rules, and the database schema. So, I think this is just a few basic pieces of functionality that people like to have out of their CMS.
+ Do you want versioning?
+ Do you want the ability to review past versions?
+ Would you like to have any version of the document the effective version while other versions are in draft modes?
+ Do you want the ability to add custom metadata against the document?
+ Do you want to use an editor like Microsoft Word or an XML Editor?
+ Do you need spell check in your editor?
+ Do you bold, italic, underline, subscript, etc. in your content editor?
+ Do you want multiple users to the system?
+ Do you have the need to restrict some users from the document?
+ Do you have the need to restrict some users from some documents that are in certain states in a workflow?
+ Do you want workflow?
+ Do you one type of workflow per technical document or do all technical documents have the same workflow?
+ Are the participants of the workflow always the same?
+ Do you want the ability to choose the workflows task owner during the run time of the workflow?
+ Does your workflow need to notify participants of the workflow during the workflow?
+ Do you want the ability to modify the workflow task layout while the workflow is active?
+ Do you want to have an administrator change a task’s owner during the workflows execution?
+ Do you want the ability to do free text searches?
+ Do want to research/search for content based on metadata/classification terms?
+ Would you like the ability to save search query attributes?
+ Would you like the ability to save the search results?
+ Do you want the search results to be sorted? If so, by what values?
+ Do you want to have the ability to have the system publish the content?
+ do you want the CMS to deploy the document via ftp?
+ Do you want the CMS to deploy the document to a web service (WSDL/REST based)?
+ Do you want published content to be in a certain format?
I feel like I could type a several dozen more of these…
Then after the system is built and deployed you may get your first request for change. The change may not be easy because of the database table (schema) design. So you would be looking at a large redesign/build of the application. If you have to redesign the application then you may need to alter the data (read: migration issues).
I think if multiple people are using the Access based system that you will have concurrency issue, which is when two people are accessing the same tables and/or rows the system may crash. Additionally, with multi users at some point you will have a scale problem (only so many people will be able to use it). I really don’t know about access to tell when you may hit those issues but I know you will. I guess everything in this paragraph can be applied to all computer systems but I think that MS Access’s threshold is smaller.
Do you know of anyone who is doing it? Not really as I usually work with CMS vendor products and don’t build too many custom CMS’s. The custom CMS’s that I have done have used at least a server side RDBMS, application server, and a web server or installable client. I once replaced an MS Access database with a vendors WCM. The access database didn’t really have any of the features listed above but a person did script it enough to collect data and output a static html web site (approx. 100 pages).
Hope this helps
-Travis
Raimond Kempees offers Mary this advice:
It can be done. Access is a multipurpose generic database in which you can store all sorts of stuff. But (I hope you already could here the ‘but’), luckily Content Management is not just (or maybe not at all) about storing text in a database. So what you would get when you store documents (or just the document texts) in Access, you get a nice big and log database with very limited options; for instance: generating useful output (to more than just the standard Access Report as well), multiple users in the same database, good versioning and good user management (if needed) would require a strong effort in hacking that into Access, because Access does not have that out of the box.
Considering the amount of time that takes, Mary would be better off in spending time in looking for better suited systems, that can easily be adopted with minimal work to have what is needed.
I would say that even a network share with a properly set up document tree filled with either Word or PDF docs would be better.
You could also consider putting documentation in your current CMS, if and when that CMS is up for it. Otherwise, a system like CVS or Subversion, the last which would be my preference for keeping technical documentation, would be very handy and easy to set up. If the documentation should go on the web as well, a good look into some other (free) content management systems might save Mary (and especially her boss) a lot of trouble. If you only need a database filled with documentation, then I would opt for either the MSDE (the free version of SQL-server) or some other open source database. But that really depends on the functional aspects and the size of the documentation.
So, in short, it could be done, but it never shouldn’t be done.
Raimond Kempees, Technical Architect
ICS Web Communication / Infra
Leiden University
CM Professional and technical writing guru Jack DeLand offered Mary this advice:
Well, you may not believe it, Scott (and Mary), but I actually do have a sample Access database that interacts with Word to store docs. It’s only at the PROTOTYPE stage (pre-alpha), but I can share with anyone who’s interested. c. 1998
I also have some screen shots of Microsoft GLUE. It used a SQL server with VB interface. All the doc from all units was munged through SGML, then extracted as Help text via GLUE. It’s fantastically intricate, and it really worked. c. 1995
In sum: it works, but you need a full-time programmer, plus use MS software for just about everything. I would say no, if I were her.
Jack DeLand
NOTE: If you’d like me to connect you with Jack to obtain a copy of the prototype Access database he mentioned, let me know.
Scott Abel
TheContentWrangler.com
Adriaan Bloem offered this advice to Mary:
Well—now that most of the technical reasons why (not) to use Access for this purpose have been so eloquently put (yes, ofcourse you could use Access, but in much the same way as you could outfit a Yugo for Nascar; a Yugo has it’s niche, but not in racing) I can only add one of my favourite anecdotes from my sysadmin days.
A team of experts was building a database for cucumber crop predictions, the theory being that way they could capitalise on cucumber options and futures and enter the jetset world of high-stakes vegetable trade. They’d import all this data in their Access database, because an actual “enterprise strenght” datawarehouse would have been “too costly and too cumbersome” to implement.
Allthough there were no more than 4 or 6 concurrent users of this database, the amount of data (although not much more than several hundreds of megabytes), the inconsistencies in the database because of working with concurrent users, the transactions crossing, well you can imagine all that could go wrong in an Access database, added mounds to the already tangible stress in predicting next years’ cucumber sizes, colours, quality and quantity.
In short, what I, as sysadmin, most dreaded was being called to the cucumber department to fix a drive or mend a printer. These people were so constantly on the brink of total breakdown that the printer toner cartridge running out would have two people sobbing for half an hour. Tension was running so high I once even witnessed a fist-fight between the cucumber manager and the tomato manager – the latter being much more succesful (they used Oracle) – and another witness of this explained it as “well, ofcourse – cucumbers and tomatoes will always be arch nemeses”.
In short, there isn’t many circumstances I’d advise building anything your business depends upon in Access. But that’s mainly because I don’t like to see people cry.
Adriaan M. Bloem. Project Manager
ICS Webcommunication / Infra
Bob Doyle shared these comments with Mary on the Content Management Professioanls listserv:
Note: the first CMS I wrote (in the 1990’s) used Microsoft Access as its database. I estimate that perhaps 100 of the 1890 or so CMS’s in my 2005 world survey [1] still use Access.
But as another member mentioned, one would not want to store text, documents, HTML, XML, even topic-based DITA content as Mary has, in the Access database. She should use it to store the metadata about each file in a central repository or file system.
So what would Mary store in the database? The metadata that allows a real CMS to function. A database is only one third of the classic three-tier structure of a CMS. These tiers are backend database server, application server (where middleware implements the business logic – all the processes and strategic rules of the organization), and front-end client user interface (usually a web browser).
To learn more about what a CMS does, see the CM Pros Glossary [2].
Here are the first three paragraphs:
___________________________
A Content Management System (CMS) is a software tool designed to help content managers create, manage, and publish their content. The CMS uses a database to track the location of, and relationships among, content elements in a central repository or file system.
Broadly speaking, a content management system describes application software that allows people to more easily change and update content, especially on their websites (WCM). When the content (number of pages, images, etc.), and/or the number of contributors, grows large, a CMS helps collect, create, and aggregate content in ways that makes it easy to reuse.
A CMS allows a team of contributors to work on the same pages without conflicting (CheckInCheckOut and Work Flow control). It can schedule pages to appear and disappear at designated times, and archive the old pages with versioning and revision control.
___________________________
To see the different stages in the content management lifecycle, view our draft poster [3].
And our CMSML effort will attempt to define all the capabilities and features found in a CMS [4].
Finally, today’s enterprise CMS often work with many databases, not just one. The defining property of ECM is that it integrates many kinds of content formerly managed by separate systems. And it manages both unstructured and structured (XML) content, despite the outmoded AIIM definition that ECM manages only unstructured content [5]. Note that ECM also fails to mention the information organization and content creation stages of the content lifecycle. Here’s their definition.
___________________________
What is ECM?
It’s not enough to “manage” content.
Enterprise Content Management (ECM) is the technologies used to capture, manage, store, preserve, and deliver content and documents related to organizational processes. ECM tools and strategies allow the management of an organization’s unstructured information, wherever that information exists.
____________________________
Even our modest CM Pros website makes use of several databases and multiple “loosely coupled” CMS’s. In this Web 2.0 world, multiple content repositories can be connected in a “services-oriented architecture” and different CMS’s communicate with one another using web services like RSS and XML-RPC. These all can appear to come through a single AJAX webpage using Dynamic HTML.
There is a Mailman database (proprietary) with our various mailing lists, the Glossary runs from a TikiWiki CMS using MySQL, the CM Pros Bloggers on the home page are aggregated by a Drupal CMS (also using MySQL as its database), the SWISH-E search engine has its own index database, the HTML content, our blogs, our page templates and CSS, are stored in the file system (best for very easy content migration), we plan to store native XML content in a eXist Native XML database, and all our member directory data, our e-commerce, our content ownership privileges, our content scheduling (and archiving info), our syndicated calendar of events, our resource library, etc, is stored in an industrial strength PostgreSQL database behind skyBuilders middleware. Note that everything is open source (GPL) free software.
Mary should see our work-in-progress Requirements document to understand why so many different tools and technologies (and databases) lie behind CM Pros website techniques, like personalization and globalization [6].
Hope this helps,
Bob.
[1] http://econtentmag.com/Articles/ArticleReader.aspx?ArticleID=14534&AuthorID=155
[2] http://www.cmscalendar.com/cmspros-glossary.html?term=ContentManagementSystem
[3] http://www.cmprofessionals.org/resources/poster/
[4] http://www.cmprofessionals.org/resources/cmsml/
[5] http://aiim.org/about-ecm.asp
[6] http://www.cmprofessionals.org/resources/website/requirements.html