Common Database Options

Just as there are a variety of cars and trucks for different uses, there are a variety of database models and tools to satisfy different business sizes and activities. The ideal database for one task may be unsuited for another task, and in fact may even be damaging to the business. To choose the correct database tool, you must review the data to be managed and the volume of access to that data you require. Generally speaking, the larger the number of people viewing the data (at one time), the more complex the application architecture.

Recommendations for database tools are influenced by the following factors:

• Number of users (people accessing the data at one time)
• The amount of data, such as the number of records, transactions per day, and the amount of data in each record.
• The style and speed of computer hardware available.
• Method of access, such as office network, internet, or hand held or wireless computer.
• What staff are available to manage the database, including their skills and available hours.
• The required budget for acquiring the database.

Single User Applications - one-tier

Users: one; Data: hundreds of thousands of records; Computers: common pc's; Access: on disk; Staff: none; Purchase Budget: less than $1,000.

Single user database applications are frequently store bought, retail programs. Examples include Microsoft Outlook, Quicken, QuickBooks, Act!, HyperCard, ClarisWorks/AppleWorks, and GoldMine. These programs consist of one or more data files physically stored on the hard drive of a desktop computer, coupled with a program (or set of programs) access that data. These programs are usually written in the "C" programming language by teams of programmers working for years, but the high cost is supported by the large volume of copies sold over many years.

In the past, custom "C" programs would have been developed occasionally to manage similar databases, but current inexpensive, retail software programs make this method obsolete.

More complex applications such as Microsoft Outlook, QuickBooks, or GoldMine allow users to connect to the data file on their computer, and share that data with other network users in a limited fashion. These share one or more files, but they do not have a central server or processor controlling the sharing process, hence they are defined as one-tier.

Workgroup Systems

Users: 2 to 100; Data: up to 1-2 million records; Computers: common pc's and dedicated server; Access: network; Staff: 5-15 hours per month skilled database management; Purchase Budget: $10,000 to $70,000 (typical).

Workgroup databases are used by groups or departments within larger corporations, or by small businesses. Sometimes they begin as single user databases that are expanded over time to include multiple users. Microsoft Access and FileMaker Pro are examples of workgroup databases that are frequently used both in shared workgroup and single user modes. Workgroup databases on the market today may either be one-tier or partial two-tier* (see footnote).

Workgroup databases have proprietary data formats and features that are specific to the brand and version of the software, they support up to 100 users or more, but more typically are used for groups of 10 to 60 users, and they support complex data structures and graphical screens. These products are often described as file based databases because all users share a single copy of a centrally located database file. This type of implementation is ultimately what limits the maximum number of users and the record count, due to limitations on record locking and network bandwidth, although these limits increase from year to year as the result of improvements in computer and network speed.

Workgroup one-tier systems can be created in a very short period of time, relative to other alternatives, typically 2 to 6 months. Most of these products offer the developer integrated development environments (IDE’s) for screen design, plus functions for validation and formatting of data. In the last three years, workgroup one-tier systems have added many features for web integration, allowing for data entry or reporting to be performed from web sites.

Legacy One-tier Systems

Users: hundreds or thousands; Data: many millions of records; Computers: terminal and mainframe, such as VAX, Digital, AS/400; Access: special terminal network; Staff: one or more dedicated, salaried programmers; Purchase Budget: no longer sold.

The mainframe-based database used to be the standard business computer. These systems have been in use for nearly 40 years, and although modified over the years, are still in use at many locations. Users access the computer from a plain screen of white, green, or yellow text, using various memorized typed commands, and there is no mouse or graphics. Modern integration has allowed users to view the mainframe command screen from within a window on their desktop Windows or Macintosh screen, but the interaction is the same.

Data is stored on the central server in indexed data files, and users "log in" to a central server to work with the data. This data structure is very reliable, secure, and can service hundreds of simultaneous users (and many thousand casual users). However, the user interface is difficult to learn, requiring tens or hundreds of hours of training. More significantly, mainframe data is often stored in proprietary formats, which makes it difficult or impossible to analyze using standard tools.

In recent years, many legacy applications have been modified to use database "engines" such as Oracle or Sybase for data storage. These hybrids can be classified as two-tier applications (see below). Developing applications in a mainframe environment requires highly specialized skills and is very time-consuming.

 Two-tier Client/Server Architectures

Users: 2-150; Data: multimillion record counts, depends on specific product; Computers: desktop computers and central server(s); Access: network; Staff: one or more dedicated, salaried programmers; Purchase Budget: $50,000 to $500,000.

In the early 1980’s, the latest rage in information technology was two-tier application development. This type of architecture usually consists of a Windows based client program, and a server database such as Oracle or Informix (or more recently Microsoft SQL Server). The graphical user interface (GUI) communicates with the database server across the network via Structured Query Language (SQL), and may be developed quickly with IDE based tools such as Delphi. Microsoft Access applications can also be configured to access an external database server, and a common sales path for Microsoft is to upgrade from a single user or workgroup Microsoft Access database to an MS SQL Server database using Access as the connection application.

Two-tier applications offer an increased level of scalability in that processing is shared between the client and the server. The database server typically does little more than manage the data, while all business logic and the user interface resides on the client machine.

The limitations of a two-tier application become evident after 100 to 150 users log in. Because business logic is processed on the client machine, large data sets are downloaded across the network and calculated or summarized by the client application. This type of architecture is very taxing on network infrastructure, and to some degree on the database server. Still, two-tier applications are well suited for small to midsize user groups, and are still developed widely today.

Web Based Two-tier Client/Server Architectures

Users, Data, Computers, Staff, and Purchase Budget: similar to two-tier client/server architectures, with case by case differences. Instead of desktop computers, can substitute hand held computers, cellular phones, or wireless devices.

At present, a large number of two-tier Web-based applications are being deployed for business use. Web-based systems utilize a Web browser such as Netscape Navigator or Internet Explorer to access applications that are hosted on remote Web servers. The Web server then accesses databases or other server-based resources on behalf of the Web user.

The benefits of browser-based applications are numerous. One major benefit is that users access these applications using a standard Web browser instead of proprietary client software. This eliminates the need for regular distribution of proprietary client software to endusers, and allows users to access the application from any location with Web access, given the proper security. It also eliminates the need to purchase or license software for individual employees or users.

On the negative side, there are fundamental limitations to the ways in which data and graphics can be displayed on the screen or printed from Internet Explorer or Netscape Navigator. As the reporting or presentation needs become more complex, ever larger amounts of programming effort are required to work around the limitations of the internet browser. The server resources for a web based application must be much more robust and powerful, because no processing is occurring on the desktop computer (the client), all of the processing is occurring at the server. In a two-tier database with 100 users, there are 100 desktop computers assisting in the data processing. In a web based database application, the processing that was previously performed on those 100 desktop computers must now be performed at the server.

Regardless of these and other concerns, the savings in time and effort through the elimination of distribution of client software to endusers are so great that they become the deciding factor. Maintaining and updating software (unless you are using a central source system such as FileMaker Pro) can be extremely expensive, time consuming, and repetitive. For example, it is not uncommon for multiple, time consuming updates to occur in client software in an attempt to fix a single problem. The demands from the user frequently follow the dialog: "Fix this problem.", "The update didn't fix it.", "No, that's not what I wanted.", etc., with each interaction requiring a time consuming client software update. One middle course to these problems is to install a web based application for the majority of users, and supply client software or reporting tools for a small number of administration or executive users to perform advanced tasks.

Most Web-based applications access a separate database engine via the webserver. While some call this type of arrangement three-tier, the Web browser’s only function is displaying the user interface and performing some validation of user input. Therefore, client/server purists do not consider the Web browser to be it’s own tier. In many ways, web-based applications are a theoretical return to the origins of the mainframe, e.g. the "dumb" terminal simply displaying information stored and processed at a single central computer.

N-tier Client/Server Computing

Users: unlimited; Data: unlimited; Computers: desktop computers or web access device and multiple central servers; Access: network; Staff: at least two or more dedicated, salaried programmers; Purchase Budget: $100,000 and up.

The n-tier client/server model expands on the traditional client/server model by introducing a middle tier. This middle tier, often call the business tier, houses the business rules of the application. This middle tier moves some of the processing logic usually contained in the database server and client application into a third (or middle) tier. The shift of business rules into this middle tier enables the application logic to be separated into still smaller components. These components can then be manager by a Transaction Server such as BEA Tuxedo or Microsoft Transaction Server, further spreading processing to more physical machines. The scalability of this type of application is virtually limitless.

N-tier client/server systems can be implemented in any number of ways with varying levels of complexity. The client tier can be comprised of a simple Web-browser, or a proprietary application written by a programmer. Middleware can be written in C, Java, or another network-aware language. There are also numerous database products available for the last tier of the model.

Web-based/N-tier applications:

Many new database applications developed today utilize a Web-based/n-tier architecture. This model combines the scalability benefits of client/server computing with the rich user interface capabilities of Web-based systems.

Multi-tier Web-based applications are most often developed using Java Servlets or Microsoft Active Server. Microsoft Corporation has one of the most widely accessed Web sites in the world, and as one might guess, it is based entirely on Active Server Pages and Microsoft SQL Server.

This computing model is complex to build and maintain, but the resulting system is capable of handling the highest transaction volumes known today.

Conclusion

Each of these database technologies are successful when used in the appropriate scenario. A recommendation of one or more choices requires an assessment of the available funding or budget, the number of users, the transaction volume or data size, the long term maintenance plan, and whether any existing (legacy) systems can be re-used or recycled.

Applied Arts welcomes your inquiry. Please contact us at (859) 225 1572 to discuss your next data application decision.

©2001 Applied Arts Ltd. and Endeavor Systems - All Rights Reserved