Welcome to EMC Consulting Blogs Sign in | Join | Help

SSIS Junkie

The power of schema abstraction

Gert Drapers has posted a really good article today which you can read here. In it he suggests a best practice of always putting a view layer in between your database tables and the consumers of your data. He explains some scenarios where this technique can help.

It is clear from the scenarios that the use of views becomes really powerful to protect consuming applications from changes to your table schema. The use of views is a technique that I always try and preach on any project that I am on and this post from Gert is an excellent explanation of why.

Quite often I put posts up here to serve as personal bookmarks for when I might need stuff later and this is one of those times. Hopefully you'll click-through and be educated as well.

-Jamie

Published 12 July 2007 18:02 by jamie.thomson

Comments

 

jwelch said:

Good read. This is one I have always struggled with - the extra maintenance involved with the views is one of the downsides of using views, as well as the loss of functionality in reporting tools that rely on being able to query the database for foreign key information. Do you use the view layer for datamarts and warehouses as well as transactional databases?

July 12, 2007 18:56
 

jamie.thomson said:

John,

Indeed I do. I work exclusively on warehouses/marts so transactional systems aren't my 'bag'. In fact I sometimes think of views as my 'logical' model above the physical model.

You raise a good point though. I find maintenance is made easy by using the appropriate tool Redgate's SQL Compare is a good one however I prefer to use Datadude because of its ingrained support for TFS source control.

-Jamie

July 12, 2007 19:07
New Comments to this post are disabled

This Blog

Syndication

Powered by Community Server (Personal Edition), by Telligent Systems