Data Management

Decision Support: Choosing the best database management system for your client

Choose the best database management system

As an IT consultant, I realize the relationship between efficient database management and the success of my client’s business. An organization with poor database management can count on being just as successful as a business whose phone system works half of the time.

At least a dozen software vendors design two main types of database (DB) management systems: relational and nonrelational. But which is right for your clients?

Two of the bigger players in the DB market, Microsoft and IBM, each offer several different database management solutions. We'll look at the features of the companies' flagship desktop DB products, Microsoft Access 2000 and Lotus Notes/Domino R5. While these two products may have very different uses, they are both at the core, database management and development tools.

Keep in mind that a consultant's decision to implement one solution over another should be based largely on the client’s current infrastructure and software investment, the user audience, and the ultimate goal of the development project.

First of two parts
This article discusses the uses and features of both Microsoft Access and Lotus Notes. In the second installment, we’ll cover the two products' respective GUIs, their use in Web development, and their security architecture.

Microsoft Access 2000
Microsoft Access 2000 is a relational database management system with an easy-to-use development interface ideal for building simple desktop database applications and for performing calculations.

Its major selling points are its relational nature, which simplifies the process of validating data integrity and uniqueness, and its ability to integrate smoothly with other applications in the Microsoft family. Access is an excellent desktop database platform for analyzing data and generating visually pleasing reports.

Access constructs and design principles
A full-scale Access application involves at least the following three basic Access object types: forms, tables, and reports. Forms are used to collect data, tables are used to store the data in a database, and reports are used to print detailed or summarized information pulled from tables.

Most Access tables also use Query objects to filter, sort, and combine data and Module objects to store Visual Basic (VBA) code. Note that the Access 2000 query engine has been enhanced by the introduction of the Microsoft Data Engine (MSDE), which is based heavily on the Microsoft SQL engine.

To present data, users create reports that summarize the information in Recordsets, which they can view, print, or publish on the Internet. Access’ capability to provide meaningful reports is its ultimate asset, giving it the clear edge over Lotus Notes in this area.

Access can also provide visual representations of database data due to the application’s internal relationship with Microsoft Graph.

The application's forms can contain VBA code in a special type of Module object called a Class Module. Forms are used to display and enter data, to control the opening and closing of other forms, and to control navigation within an Access application. The Switchboard Manager dialog, which aids users in building the Switchboard for a database, is the primary design object used to maintain a consistent look-and-feel for users as they move around a database.

Beneath the GUI, Access data resides in a series of strictly structured tables. Relationships between tables are the foundation of Access and all relational database management systems (RDBMS). Linking tables preserves the integrity of data relationships, a concept often referred to as “referential integrity.” A base table incorporates one or more columns of an object’s properties and contains the primary key that identifies that object as a unique piece of data.

A primary key consists of a set of values that uniquely identify a row of a base table. A foreign key is a column containing values that correspond to those contained in a primary key in a related table; foreign keys can consist of one column or a group of columns. The entire Access application must conform to a rigid structure based on the primary key-foreign key relationship to take full advantage of the database system’s architecture. Unrelated tables in a RDBMS are extraneous elements.

Access application development
Many large corporations use Access to handle hefty amounts of data efficiently and query information easily. Access’ superior query function minimizes a duplication of information by requiring repetition of only those data items by which multiple tables are linked.

While Access is not an industrial strength database solution such as MS SQL, it remains an attractive database solution for many businesses, because its ease of use enables users with limited experience to build basic desktop applications.

Wizards, for example, guide users through the database generation process and reinforce the need to maintain uniqueness and referential integrity between the records.

Access uses a rows-and-columns structure to store and represent data. Records created from forms represent rows, while the individual fields on forms represent columns.

Access also employs macros, which automate repetitive database operations. Access 2000 supports macros primarily to allow backwards compatibility with databases created in earlier versions of Access.

Access 2000 can also take advantage of ActiveX controls for added functionality.

Lotus Notes
Lotus Notes is a groupware system containing an e-mail component, a database development environment, and a fully integrated Web-publishing tool. It is, in essence, a NRDBMS (Non-Relational Database Management System). Its major benefits are its ease-of-use, document-based architecture, sophisticated security structures, and integration with Notes e-mail and the Domino Web engine.

Lotus Notes is ideal for building Web-based document-management, contact-management, and workflow applications, all of which can take advantage of Lotus Notes' e-mail capabilities and the Domino Web engine.

Notes constructs and design principles
The base unit of all Lotus Notes applications is the document, unlike most other database applications, in which the base unit is the record.

The smallest unit within a document is a field, which can store single or multiple values of the same data type. Further, every document in a Notes database is based on a form. The form determines the visual representation of the document, as well as the default values of its fields. Notes views are similar to Access queries in that they capture a subset of a database’s documents based on specific developer-defined criteria: the view selection formula.

Notes Navigators are the primary constructs used to facilitate movement from one task to another through a consistent interface. The introduction of Notes Outlines in Domino R5 makes the task of designing navigators for Web-based databases very straightforward. Navigators are still available but are less applicable to Domino Web sites than to Lotus Notes' client-side applications.

Notes application development
Lotus Notes has, at its core, a "flat" database structure, which means that data organization is not dependent upon primary key values linking different pieces of data to one another. There are no base tables, and data uniqueness is handled differently.

However, the lack of a strict relational database structure does not prevent the developer from mimicking aforementioned Access functionality in a Notes system. One piece of data in a nonrelational database can, in fact, be related to another, in order to establish a data hierarchy.

In Notes, the onus is on the developer to provide related sets of data to the user, in Views.

@DBLookup, @DBColumn, and @PickList are three Lotus macro language functions, or “@Functions,” which developers can use to mimic some relational database features on Notes forms. All of these formulas are used to display sets of related data in the user interface. In addition, uniqueness is established at the document level by the systemic generation of the “DocumentUniqueID.”

Views display a subset of records based on a query designated in the “View Selection formula.” Columns within Notes' Views function very similarly to columns in Access tables. Columns can merely represent field values for a specific form, or they can be calculated using multiple field values or with the Lotus macro language. View action buttons and Form buttons often use Lotus macro language to perform simple operations. In this regard, they are most similar to Access macros.

More sophisticated development can be performed in Lotus Notes with LotusScript. As LotusScript and VBA are supersets of BASIC, they have very similar object sets. Both are simple object-oriented languages, unlike C++/Java, but they can provide for significant functionality within their application development environments.

LotusScript can automate routines such as purging data, creating documents, and performing data extractions. Similar to Access modules, Lotus Notes agents, primarily written in LotusScript, are used to perform complex operations on large sets of data.

One significant difference between Access 2000 and Lotus Notes development is the location of the code store. In Access, much of the code is stored in Modules, while in Lotus Notes, much of the code exists within specific forms and views.

Which do you prefer?
Have you had more success with Access or Lotus Notes? Which does your business prefer? Post your comments or send us an e-mail.


Editor's Picks