How to get data/database independence with a three-tier architecture

Discover how to attain database and data independence in this tip. Find out what database independence is and learn the benefits of a three-tier data warehouse vs. a two-tier data warehouse.

What are the advantages of having three-level database architecture? How are they related to data independence and database independence?
I'll address the second question first and start by describing two-level (also called two-tier) architecture and work up to three-tier.

Two-tier architecture is also called client-server architecture: The server in question is a database server which runs a database engine (like DB2 or SQL Server) and its job is to hold and manipulate the data. The application that runs against the data is managed entirely by the client and provides all the user interface elements such as pop-down boxes, buttons and the like. The application requests data by sending SQL queries to the server and the database obliges by returning a table of data. The application on the client then manages the way in which this data is displayed (as a table or report or whatever).

Two-tier architecture gives us data independence — the data is handled entirely separately from the application. The concept of data independence is very important in database design. It is hugely beneficial to be able to write completely different applications that run against the same data and do it easily because the data is divorced from the application.

Database independence is, in theory, also provided by two-tier architecture. The SQL language is used for communication between the application and the data and all the major database engines speak SQL, so in theory you can change engine without any changes being necessary in the application. If only the theory held true in practice. The problem is that different engines use different SQL dialects; which means the SQL code is not always transferrable without tweaking. Using only standard SQL as set out in the ANSI standards (SQL-92, SQL:99, 2003, 2006, 2008) will, however, improve the portability of an application between engines.

Moving to a three-tier architecture per se does nothing to provide data or database independence because these can be provided by adopting a two-tier approach. On the other hand, a third tier can be inserted between two existing tiers without disrupting the desirable data and database independences. So what does the new tier do? There is a wide range of options. For instance, you could put in a load-balancing tier. The application sends a query to the load balancer. Behind the balancer are five identical copies of the database and the load balancer directs the query to the least busy database. A database application on the Web can be considered three-tier — a browser talks to a Web server and the Web server talks to the database engine. The architecture can become as complex as you like, perhaps browser to Web-server to load balancer to database engine.

A great deal of architectural choice is available, and to arrive at a good design requires the choice of the right architecture -- and therefore the number of tiers -- for the job in hand.

Dig Deeper on Enterprise data architecture best practices