Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

Decoupling the Database

By MladenPrajdic,

When poor performance blights an application, the developer rarely gets free-rein to "mess" with the underlying database structures, in pursuit of a solution; there are just too many dependencies and too much risk. This is intensely frustrating when the developer realizes that the database structure is fundamentally flawed because, without touching the database structures, the task is bound to be a fruitless search for a non-existent fast=true switch, involving snipping around the edges of the problem, tweaking parameters and adding indexes.

The problem is at its worst when the database is close-coupled to the application's other components. You can't then change the database schema without changing the database system's input/output behavior. To make it possible to refractor the database without compromising the integrity of the build, there must be a layer of abstraction between the database and the applications that access it.

'Close-coupling' means allowing direct base-table access from components outside the database. If your database is closely-coupled to your application then you have to change the inputs from the application, in order to produce the same output when you make any schema change. By disallowing direct access to tables, and allowing access only through SQL Server database routines (stored procedures, user defined function and views) in an abstraction layer, we decouple the underlying schema. We can change the schema as much as we want as long as we maintain the abstraction layer to ensure that the output column names and data types stay the same in our abstraction layer.

Even if you are using an Object-Relational Mapping tool (ORM), such as Entity framework, NHibernate, and so on, you still need a well-designed database abstraction layer, and most of the ORMs now work very well views and stored procedures. Here is the problem, though: in my experience, most projects still use ORMs to map objects directly to tables. It's as if the ORM mapping layer is regarded, in itself, as the abstraction layer. It's not; if you map directly to tables, you've still coupled your code directly to the database. As a result, any schema changes often result in application recompilation. If you store the mapping in XML files, you can, in theory, change the file and then just restart the application. Unfortunately, this is not the case most of the time.

ORMs offer a means to map your application domain to the relational database, but you still need the native SQL Server abstraction layer to decouple the database from all the applications that need to access it. Developers need to stop thinking of a database as a simple data store, and more as an equal partner in the application. After all there are usually many different kinds of applications all accessing a single database and we all know that databases last many years longer than most applications.

Mladen Prajdic (guest editor)

Mladen is co-author on the new book, SQL Server Team-based Development, from Red Gate Books, where he discusses database refactoring, as well as testing, in more detail.

 
Total article views: 319 | Views in the last 30 days: 3
 
Related Articles
ARTICLE

Abstraction

Developing software requires abstractions. Should we do more in database development?

ARTICLE

Views for Abstraction

Views are one of the more basic constructs in SQL Server, but often it seems that developers are not...

FORUM

ORM and Data Abstraction Layer

Hi all, I work for a company using MS SQL databases exclusively, but we integrate with varying tech...

FORUM

Abstraction

Comments posted to this topic are about the item [B]Abstraction[/B] My creations these days are excl...

FORUM

Trying to incorporate a Database Abstract Layer...

I have a goal of creating a Database Abstract Layer (stored procedures and maybe views) as long term...

Tags
database weekly    
development    
editorial    
 
Contribute