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

Digg This!     StumbleUpon     Del.icio.us

RELATED CONTENT DBMS and data warehousing Definition of primary, super, foreign and candidate key in the DBMS What is the difference between a logical and physical warehouse design? What are some emerging data warehouse and DBMS trends? How to select an MPP database: DB2 vs. Teradata What comes first — the data mart or the data warehouse? What are the top database management systems (DBMS)? What is the role of DBMS in RDBMS? Is an Inmon-modeled BI system, like Madison, the future of data warehousing? What are the benefits and disadvantages of a RDBMS? Are there benefits to using both Teradata and a DB2 database? Enterprise data architecture best practices Advantages and disadvantages of XML shredding How to shred XML with the DB2 XMLTABLE function Shredding XML docs into relational tables with annotated XML schemas Teradata takes a logical approach to data warehousing appliances Examples of single and bulk XML shredding of XML documents What is the difference between a logical and physical warehouse design? What are some emerging data warehouse and DBMS trends? Teradata VP talks data warehouse appliances, reveals cloud and SSD plans Selecting ODBC functions for optimized SQL statements Guidelines for managing data updates to optimize ODBC performance Database management systems (DBMS) architecture and design Definition of primary, super, foreign and candidate key in the DBMS What is the difference between a logical and physical warehouse design? What are some emerging data warehouse and DBMS trends? Data Warehouse Platforms Product Directory Designing for performance: Strategic database application deployments An introduction to database transaction management Database access security: network authentication or data encryption? Executing SQL statements using prepared statements and statement pooling Static SQL vs. dynamic SQL for database application performance How to select an MPP database: DB2 vs. Teradata

RELATED GLOSSARY TERMS Terms from Whatis.com − the technology online dictionary data classification  (SearchDataManagement.com) OLAP  (SearchDataManagement.com)

RELATED RESOURCES 2020software.com, trial software downloads for accounting software, ERP software, CRM software and business software systems Search Bitpipe.com for the latest white papers and business webcasts Whatis.com, the online computer dictionary

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.