Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
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: 301 | Views in the last 30 days: 1
 
Related Articles
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

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...

ARTICLE

ADO.NET - A Data Access Layer

Developing applications for SQL Server usually results in a variety of access methods that the progr...

ARTICLE

Layers of Security

A good security scheme will contain many layers. Today Steve Jones talks about one of those: good ha...

Tags
database weekly    
development    
editorial    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones