Implementing Referential Integrity in a Domino Database

Collaboration & Messaging
  • Smaller Small Medium Big Bigger
  • Default Helvetica Segoe Georgia Times

In "An Introduction to Maintaining Referential Integrity in Domino," I gave you an overview of Domino and referential integrity, which included brief descriptions of a few techniques for maintaining Domino data consistency. In this article, I will dive deeper into these techniques. Four topics are covered: record locking, creating unique identifiers, normalization techniques, and working with replication and/or save conflicts.

Record Locking

Introduce any diehard RDBMS developer/administrator to Domino and the inevitable topic raised is record locking. That is, how do you keep others from tainting data during simultaneous access? Of course, this is a standard feature of relational systems like Oracle, DB/2, and Sybase, but it doesn't transfer to the connectionless architecture of Domino.

The Lotus development language, LotusScript, provides one solution to this problem, but it is appropriate only in a constantly connected environment. It's operational with a server-based application and is a good approach for applications with scores of remote users. The locking of records (documents) occurs in real time. Disconnected users do not work in real time, so the logic is broken. Here is a closer look at the approach:

The Domino development environment is event-driven. Various events are fired when a database opens and closes; when views open, close, and refresh; when documents open or close; and so forth. Documents are locked when opened in edit mode. The document is unlocked upon closure.

This approach uses two Domino form events: QueryOpen and QuerySave. The QueryOpen event fires before the document is opened and presented to the user. The QuerySave event is triggered upon the saving of a document. Any code associated with the QuerySave event is executed before the document is actually saved. Figure 1 shows the standard QueryOpen event in Domino Designer with the associated LotusScript code. The code's purpose is to let a user know when a document is already in use.

Figure 1: Domino Designer allows you to create and associate code with a document's QueryOpen event.

Figure 2 contains the LotusScript code for the QueryOpen event. A simple flag variable is used to signal document locking. A value of one (1) is locked, and zero (0) is unlocked. The flag is set to one during the QueryOpen event when the document is opened in edit mode. The value is set to zero in the QuerySave event. There is one caveat in the QueryOpen event: You must determine if the document is already locked.

1. Sub QueryOpen(Source As Notesuidocument, Mode As Integer, Isnewdoc As Variant, Continue As Variant)
2.    If (Mode = 1) Then
3.  If (Source.Document.LockFlag = "1") Then
4.   MsgBox "I am sorry, this document is in use.",0,"Document Locked"
5.   Mode = 0
6.   Source.Document.LockFlag = "1"
7.   Call Source.Document.Save(True, False)
End If
      End If
   End Sub


1. The QueryOpen event is declared.  This is a standard portion of all Domino forms.
2. The Mode variable signals whether the document is opened in read (0) or edit (1) mode.
3. Determine if the document is in use (locked) if trying to open in read mode.
4. A message is displayed if the document is already locked.
5. The mode is reset to reading.
6. The document is locked if not currently used.
7. The document is saved to preserve the lock.

Figure 2: Through the document's QueryOpen event, this code updates the lock status or alerts the user when the document is being used by someone else.

Next, unravel the QuerySave event (see Figure 3). The QuerySave event code resets the lock flag variable if necessary. It abruptly halts saving if the document has not been properly locked. The QuerySave continue variable determines if saving occurs. This variable is set to false if problems exist. Record locking is only one aspect of data integrity. Another aspect, usually found in relational systems, is unique key values.

1. Sub QuerySave(Source As Notesuidocument, Continue As Variant)
2.    If (Source.Document.LockFlag = "1") Then
3.   Source.Document.LockFlag = "0"
4.   MsgBox "This document not properly locked.  No saves allowed.", 0, "Error"
5.   Continue = False
      End If
   End Sub


1. The QuerySave event is declared.  
This is a standard component/event of all Domino forms.

2. The record locking flag variable is checked to 
determine if it has been previously set.

3. The document is unlocked.
4. An error is encountered if the document is not locked during a save.  
5. The Continue variable controls whether the document is saved. 
The default value is true, so it is set false to stop the saving of the document.

Figure 3: Through the document's QuerySave event, this code updates the lock status or prevents a locked document from being saved.

Creating Unique Identifiers

Keys, either primary or secondary, are very important to relational systems. They identify a chunk of data (row) throughout a system. Domino has somewhat of an equivalent in the form of the document ID. The Notes Document ID is a 32-bit hexadecimal number that uniquely identifies a document across all replicas of a database. This value is created when a Notes document is created. It never changes during the life of the document. The copying and/or pasting of a document are seen as a new document by the system; therefore, a new document ID is created.

The document ID is accessed by way of the @DocumentUniqueID function; it must be combined with the @Text function for proper display and storage. The following combination of the two functions is demonstrated:


This combination of the two functions is used to store the value in a form field for later use, or it can be stored in related documents to create a relationship or connection. Additionally, the document ID can be used in @DBLookup/@DBColumn formulae.

Another function provided in the Lotus development environment is the @Unique function. It generates a random, unique text value. It offers no guarantees of uniqueness across a database and its replicas. Now, turn your attention to data normalization in Domino.

Normalization Techniques

Normalization may be achieved in Domino by way of accepted normalization techniques and the Domino @DBColumn/@DBLookup formulae. These functions allow values to be retrieved from a Domino database by key value, view column number, and so forth. This allows the breaking of data along logical lines so it can be reused in a consistent manner throughout the enterprise. Figure 4 contains a quick breakdown of the @DBLookup formula.

1.   "Notes":"NoCache";
2.   "Domino Server":"Domino Database";
3.   "View name";
4.   "Key Value";
5.   "Field Name or Column Number");


1. The type of database accessed (Notes) 
and whether lookup information is cached between accesses.  
NoCache signals information is not cached.

2. The second set of parameters signals the Domino server 
and database file path/name of the data to be retrieved.

3. The third parameter is the view used by the lookup.
4. The actual document or set of documents accessed by way of the lookup.  
This corresponds to the first sorted column 
in the view specified in the previous parameter.

5. The field name of the values to be retrieved 
from the document or set of documents returned.  
Also, the column number (in the view) can be used as an alternative.

Note:  The syntax of the @DBColumn formula is similar.

Figure 4: The @DBLookup function can be used to retrieve documents through a key value.

A good example is a customer order system. One Domino database may contain customer information, another may contain order information, and still another may hold state/location information. The customer information database uses the state/location database when entering a customer's address information. Another database could be the merging point for all other databases; formulae are used to retrieve customer information. Also, ordered items are brought from another database.

Replication/Save Conflicts

The robust support for remote workers is one of the most important aspects of Domino, but it introduces problems in ensuring data integrity. This stems from the fact that one or more disconnected users may edit the same data. The next time these users replicate with the server, a save or replication conflict occurs. I will not delve into the details of these conflicts (see the article I mentioned earlier for more information), but the process of clearing these conflicts is left to the database administrator. Unfortunately, there is no magic wand provided by Lotus for addressing conflicts. I will cover two approaches to handling conflicts: programmatically and with a view.

The LotusScript language provides access to various nuances of a Domino database. The NotesViewEntryCollection class allows a programmer to work with elements of a view (such as documents, categories, and responses). The NotesViewEntry object represents each entry; this object has numerous methods and properties. The IsConflict property is a Boolean (true/false) value signaling whether the entry is a conflict or not. The utilization of this property while traversing every element in a view allows conflicts to be easily removed. The code contained in Figure 5 achieves the task at hand.

1.  Dim s As New NotesSession
2.  Dim db As NotesDatabase
3.  Dim vec As NotesViewEntryCollection
4.  Dim ve As NotesViewEntry, view As NotesView
5.  Set db = s.CurrentDatabase
6.  Set view = db.GetView("Test")
7.  Set vec = view.AllEntries
8.  Set ve = vec.GetFirstEntry()
9.  While Not (ve Is Nothing)
10.   If (ve.IsConflict) Then
11.   Call ve.Document.Remove(True)
End If
12.   Set ve = vec.GetNextEntry(ve)
13. Delete vec
    Delete view
    Delete db
    Delete s


1. Create a new instance of a Notes session.  
This provides access to the current Notes environment.

2. A NotesDatabase object is created.
3. A NotesViewEntryCollection object is created.
4. NotesViewEntry and NotesView objects are created.
5. The NotesDatabase object is instantiated to the current database 
in which the code is running.

6. The NotesView object is instantiated to the specified view name.
7. The NotesViewEntryCollection object is instantiated 
with all elements from the view.

8. The NotesViewEntry object is instantiated to 
the first entry in the collection.

9. All elements in the collection are traversed using a While loop.
10. The IsConflict property is used to determine 
if the current entry is a conflict or not.

11. The remove method of the NotesDocument object 
is used to delete the document from a database if it is a conflict.

12. The next entry from a collection is accessed; 
the current entry is used as the bookmark.

13. Object cleanup is performed through the use of the Delete command.

Figure 5: Through the IsConflict property of the NotesViewEntry object, this code automatically removes document conflicts.

The previous example allows quick cleanup of conflicts, but you may wish to review the conflicts before their deletion. Another approach to these dandies is the creation of a special administrative view displaying the conflicts. To set up such a view, first create a new view using the selection formula:
SELECT @IsAvailable("$Conflict")

  • The first column should use the formula above; check Show Twistie, Categorized, and Ascending options in the column info box.
  • The second column can be your choice (for example, name of document).
  • When you open the view, if you have no replication or save conflicts, you will see only one category called 0; this will show all the documents.
  • If you see a category appear titled 1, this will list any Replication/Save documents.

The result is a one-stop shop for conflicts in a database. It still requires manual intervention, but saves some time with its organization.

Although Domino is not a relational database, it is a very powerful development environment. It provides a myriad of tools such as LotusScript and built-in functions for mimicking relational systems. Other tools not covered in this article include Java (as an alternative to LotusScript) and enterprise integration (Lotus Enterprise Integrator [LEI] and Domino Enterprise Connection Services [DECS]). Domino provides all the elements for building applications to meet your needs.

Tony Patton works with various technologies such as Java, XML, HTML, and Domino. He is the author of Practical LotusScript and Domino Development With Java, both available from Manning Publications ( You can reach Tony via email at This email address is being protected from spambots. You need JavaScript enabled to view it..