Driving with Databases

Business problem
In a prior work life, our intranet had two design flaws that I resolved to avoid the next time around:

• it was a huge effort to shuffle content around if the site was redesigned; and,
• search results included every document containing the search string (trust me, that's a problem).

Intranet with a Backbone
Ordinary search engines return a list of all documents that contain the search string anywhere in the document. There are times when this all-inclusive search is useful, but more commonly I find it only means a long list with a lot of irrelevant stuff. I'll probably find what I need, but it should be possible to provide cleaner search results.

The only way to provide cleaner search results is to limit the scope of the search. We found that putting everything about the intranet structure in a database solved both of the design flaws from the earlier intranet.

Three key tables drive structure and search for every bit of content on the intranet:

• Backbone — contains the basic information for every item on the intranet
• BackboneCategories — contains the structure of the entire intranet
• BackboneLocation — ties Backbone and BackboneCategories together

The screen shot below shows the form used to modify basic information about any type of document:

There are several important notes about Backbone document maintenance:

• Every document has an owner and a department. If the owner leaves the organization, the department manager is automatically e-mailed to reassign the document to another worker. If that is not done by the expiration date, the document is automatically assigned to the department manager.
• Every document has an expiration date no greater than 12 months in the future (the only exception is policies, which are valid until their next official review date). About a month before any document expires, the owner is reminded by e-mail to renew the document with a special page on the intranet. If, despite repeated reminders, the owner does not renew the document, the document is automatically inactivated and disappears from the intranet. After another 90 days, if no one has objected, the document is automatically deleted.
• Many reports generate hundreds of documents each time they are refreshed (e.g., one document per department or per clinician). These have a document profile to define the parameters for this process, as discussed in Automated Reporting.
• Many documents have restricted access, which is defined with a security profile, as discussed in Transparent Security and Permissions.
• Each category where the document appears is listed; in some cases, the document is linked in more than one place on the intranet for improved ease of use.

Focused search
With this database design, we don't need a search engine — just a database query. The basic search accepts up to five words, and does an every-word match with the combined contents of four fields for all records not excluded from search:

• Title
• Description
• SearchWords
• Filename

The "Include in search" parameter allows us to control search results for groups of documents. For example, a report of clinician productivity might have several hundred separate reports. If I search for "clinician productivity" and all the individual reports are included in the search results, the list would be much too long for someone like a department chief who has permissions for a few dozen clinicians' reports.

The solution is to include only the category for this report in the search results — not the reports themselves. In this case, the search results include only a single item for the report (plus any other matching search results). Anyone interested in that report can select it from the search results and see the full list of reports for which they have permissions. This leaves the initial search results clean and easy to scan.

For those few who need a more advance search process, we offer several alternative ways to define the search, which can be used in combination:

Shuffling content
Another major advantage of having the complete structure in a database is the ease with which the content can be reorganized. A simple database update query will automatically change categories, and another update query will change the path to a new location for specified documents. When the entire intranet navigation is regenerated a few minutes later (Speeding with Static Pages), voila! the new navigation is visible to users.

Keeping it fresh
Lest it get buried in the detail above, I should emphasize another major benefit of driving with databases: removal of outdated content. Keeping a large web site current is a huge challenge. Automatically expiring and removing documents is a major step forward in helping to clear the intranet of "deadwood," and this is only practicable with a database.

Lessons learned

• A focused search really does work. Our users rely heavily on search for routine navigation because it's fast and easy.
• Fine-tuning the search is a constant task. Every day we review the list of failed searches to see which documents may need to be updated with new search words, or with commonly-misspelled versions of words.
• The daily review of failed searches also prompted us to add selected external domains to the search process, since people were using the intranet search — like a Google search — to navigate to other sites.
• We still struggle with the tendency of document owners to simply renew a document without really thinking about whether it's outdated.
• We still lust over Google's "did you mean" feature for mistyped search words, but so far it's beyond our reach.

Posted 30 March 2008