Defining the Architecture of a Database Application

In this section, the structure of a database application will be explained gradually, from the least sophisticated application to the most complex.

Using a Monolithic Application with Data Embedded in the Application Code

A database application can be as simple as a computer program managing its own internal data. This was the way that most FORTRAN applications were written, with a block of data at the beginning of the code and the logic to deal with this data coded immediately below. This type of application can be called a single- tier database application, as shown in Figure 1-1.

Figure 1-1: Monolithic application containing data and logic inside the application itself.

Using a Monolithic Application with Data Stored in an External File

The problem with a single-tier structure is that, each time the size or format of the data changes, you must modify the code and recompile the application. The solution to this problem would be to store the data in a different file and then access this file in a generic way from the application. In this way, you can keep the application untouched and edit only the data file. Still, the logic for data access is inside the application itself, which could then access data sequentially or randomly . This was the technique used by some early mono- user database applications. For example, some xBase applications were designed in this way, as shown in Figure 1-2.

Figure 1-2: Monolithic application accessing external data to improve flexibility.

It is easy to try one of these applications. Click Start All Programs Microsoft SQL Server 2005 SQL Server Management Studio and you will see one of them in action. SQL Server Management Studio is a tool that comes with the SQL Server installation and manages external databases, but it is a monolithic application.

Now that the data is stored in an external file, you can store this file in a shared location where other people can access it, as shown in Figure 1-3. Each application might need to use this data in different ways, but all applications could access the shared information, which is very convenient .

Figure 1-3: Monolithic application accessing shared external data.

A problem arises when some applications need to change some of the data. In this situation, specific transactional rules must be observed to avoid potential conflicts. All applications need to agree on the specific protocol to follow to avoid these problems, but this can be difficult to regulate.

Multi-user xBase applications, such as applications developed with Clipper, were designed in this way. What we call Access applications are not drastically different from the model shown in Figure 1-3 because the Access database that is stored in a central location has no logic on its own. The logic is provided by the Jet database engine that is running as part of the Access interface on each client machine.

Using a Database Application with Data Managed by a Database Server

The solution to problems associated with sharing data might be to create a service that owns the data and manages how this data is accessed so that it can avoid conflicts between requests sent from different applications. To make this a workable solution, you need to ensure that all applications access data through this service, which publishes well defined interfaces to the functionality it provides. Early SQL Server applications designed to connect to SQL Server through the DBLib interface were a typical example of this technique. Refer to Figure 1-4 as an example of this process.

Figure 1-4: Management of centrally stored data through a database management service.

Using an Application with a Generic Data Access Layer

Applications were designed for a specific database system. Consequently, this produced portability problems when the same application had to be redesigned to connect to a different database system. The industry thus agreed on specific interfaces that could provide generic access to database systems from different vendors , thereby giving more flexibility to programmers and making it easier to write database applications (see Figure 1-5). The best known of these interfaces are ODBC ( open database connectivity) and OLE DB (object-linking and embedding database), but the managed data providers of ADO.NET could be considered part of the same principle.

Figure 1-5: Main components of a database application connecting to SQL Server 2005 through ODBC.

As you can see in Figure 1-5, each database system requires a specific ODBC driver, but applications can be designed to connect to ODBC in a generic way. However, ODBC provides a call-level interface that is very efficient for C++ applications, but difficult to use from other languages. This is typically why applications use ODBC through an extra programming layer, such as RDO (remote data object) or ADO (active data object), as shown in Figure 1-6.

Figure 1-6: Main components of a database application connecting to SQL Server 2005 through OLE DB.

Each database system requires a specific OLE DB provider, but applications can be designed to connect to OLE DB in a generic way. As was mentioned earlier about ODBC, OLE DB exposes a Component Object Model (COM) interface that is designed to be consumed natively from C++ applications, but is difficult to use from other languages. This is why the most common programming layer to use with OLE DB is ADO, which exposes OLE DB functionality as collections of objects with properties and methods in a way that is easier to be consumed from client applications.

ADO.NET changes this situation quite dramatically by providing natively managed data providers to some database systems, such as SQL Server 2005, thus avoiding the need for an external data driver or data provider. This managed native data provider talks directly to the database server without any extra programmable layer, as shown in Figure 1-7.

Figure 1-7: Main components of a database application connecting to SQL Server 2005 through the ADO.NET managed data provider.

However, some applications still require extremely fast processing and therefore prefer to avoid the potential overhead that .NET might produce. In the past, these applications might have been designed with C++ and ODBC, but now they can use the SQL Server Native Client, which connects natively to SQL Server 2005 while providing ODBC- and OLE DB-like interfaces to the application. This relationship is shown in Figure 1-8.

Figure 1-8: Main components of a database application connecting to SQL Server 2005 through the SQL Server Native Client.

Using an Application with Separated Presentation, Business, and Data Access Layers

The next step in an applications architectural design is the three-tier application design. This design was used in several standard architectures, such as Windows DNA during the late 20th century, and it is still one of the most popular designs. When attempting to keep the data access problem isolated within a component, this type of application isolates the graphical user interface (GUI) presentation as well. The principle behind this type of design is that the business problem is neither a data problem nor a presentation problem. However, this type of division of components is not always well understood or used.

For example, part of the power of relational databases, such as SQL Server 2005, is their ability to impose some consistency into the database definition with the use of constraints, relationships between tables, default values, and so on. A purist adhering to the three-tier architecture will try to manage all of the business rules in the business layer, foregoing one of the biggest capabilities that the database system possessesthe facility to ensure the integrity of your information.

Using a Complex Application with Multiple Options for Each Layer

Nowadays, many applications rely on Internet connectivity. A recent development in the 21st century is the appearance of a new type of mixed application that can store information in a company database, but that sometimes needs to run disconnected from the main system.

For these applications, a new design appears. Each of the layers can be different, depending on the environmental condition. If the computer is connected to the Enterprise network, the application works against the central database. If not, it stores the information locally and will update the central repository as soon as it can connect again to the central system. To help you develop these applications, SQL Server 2005 contains an Express edition that uses exactly the same database format as the other versions, but which can run locally.