In a conventional RDBMS (relational DBMS), a contact management application invariably stores company data in one table and contact information in another. Because there's a many-to-one relationship between contacts and companies, many contact records for a company can share a single company record.
But Notes is a document database, not a relational database, and it just can't work this way. Instead, applications designers typically rely on a useful feature of Notes called named-field inheritance. For instance, an application might present a company form with a button called Create New Contact. Its effect is to compose a new document that inherits the company name, phone number, fax number, and other fields, le
aving just the contact name to be entered.
Unfortunately, this company information is now stored in the database twice. But other than their common data, no connection links the two notes, so when you edit one, the other remains unchanged. Worse, if you change the organization note and then create another contact, the new note has the new address, but the older one does not.
One can argue that duplicating data is the only sane approach in a highly distributed, occasionally connected world. Nevertheless, it's often necessary to layer relational capability on top of Notes. There are three ways of doing this: You can build a custom Notes user interface to enforce relational integrity, build a server- or client-based agent to police the data, or tightly link Notes to a relational database.
A custom user interface for Lotus Notes is typically a Windows client program that's written in C and uses the Notes API. Alternatives include Visual Basic (with the right VBX [Visual Basic custom control])
, PowerBuilder, and ViP, the Lotus visual programming interface for Notes. To add relational capability, a custom user interface must notice when a user saves a note so it can search the database for related notes and update them as well.
A server-based agent, scheduled to run at timed intervals, can achieve the same results. Written in C to the Notes API, it runs on the server as an add-in task. Of course, you have to compile the program with a C compiler that's native to the server operating system, dedicate a server for development and testing, and (if you want to use the add-in on a different platform) port it. Moreover, if the add-in program blows up while running on a production machine, it usually takes the server down with it.
A good alternative to the server add-in program is a Notes API program running on a client PC. A simple periodic task launcher can run an agent (or a series of them), wait a few minutes, and then start from the top again. If the task launcher and the agents take th
eir configuration parameters from a Notes database on a server, the whole show can be controlled remotely (except for reboots) and the workstation can be locked in a cupboard.
A tight link between a Lotus Notes database and a SQL server usually takes the form of a program that's linked with both servers' API libraries (e.g., Notes and the Sybase DBLIB) and run by a scheduler task (see the figure "
Synchronizing Notes and SQL
"). Each time it runs, it must look first at one side (e.g., the Notes database) and ensure that all modifications made since the last time the database was visited are used to update the SQL server. Then it picks up all modifications made to the SQL database since the last time and uses them to update the Notes database.
illustration_link (19 Kbytes)
A program linked to two APIs--that of Notes an
d that of a SQL server--can run on a scheduled basis to synchronize a Notes database with a relational database. Alternatively, it can be activated by SQL triggers or the Notes equivalant, DBHOOKVEC.
Notes and Relational Data
