Understand the Role of the Underlying Database in Your Application Architecture
Before diving into the details of the features the database of your choice can offer, it’s important to look at the big picture to understand the role it plays in the entire architecture of your application. Once you have a good understanding of this, you’ll be able to make better use of your database.
When developing a database-backed application, it’s important to understand the underlying database is an integral part of the entire solution being developed. Starting with the application planning stage, one of the most important things to realize is that the database is not just a container for storing and retrieving data. Aside from these two common database operations, many RDBMSs today let you also transform, process, and generate new data based on the existing data sets, thus allowing for the implementation of data access procedures and business logic inside the database.
This can be best understood by example. Suppose you need to build an application that will process purchase orders stored in two tables: orders and details, producing reports and printed forms based on the data retrieved from these tables. One of the tasks your application will have to deal with is to prepare the printed form of a purchase order, calculating the total sum of that order based on the quantities of the included items, their prices, taxes and shipping&handling figures. The way you can accomplish this task will depend on the architectural decisions you’ll make when planning the application. Consider the following two alternative approaches.
The first approach assumes that the application will query the database for the required data, calculating the results in its business logic tier implemented with a high-level programming language such as Java or Python. Of course, the business logic tier of your application may be implemented based on a framework and may utilize an object-relational mapper (ORM) for database connectivity, but the basic idea remains the same: the underlying database serves only as a storage for the application data – most data processing tasks are performed outside the database tier.
The second approach is database-centric, meaning that the key data processing takes place inside the underlying database itself. Turning back to our example on preparing the printed form of an order, this approach assumes that the task of calculating the total sum of an order will be performed by the database server. It’s interesting to note that this can be accomplished in several different ways. For example, you might implement all the processing inside a stored procedure or simply issue a complex SQL query utilizing the SUM SQL aggregate function and a subquery.