Hi there,
Can anyone tell me what is the best approach for writing an application to talk to database when database schema will change in future? At present we have an older version which will get upgraded to a new one and some data columns might change. I want to design a data layer that should be flexible enough to work with changed schema as well. I am thinking stored procedures might be one option? Any other suggestions? Generating dynamic SQL statements? Any other ideas? I am kindda stuck. Thanks for your feedback.

Paul.

You mean you're not using stored procs already?!? Really, that's so mySQL :)
Definately go for stored procs! Also check out views, as they make quite good 'defined interfaces' to procs and other applications. Read up on n-tier architectures. Avoid dynamic SQL if you can.

Hope this helps you get started.

There are two secrets to success in this world:
1. Never tell everything you know

ok great thanks! Any suggestions for good books on this. Database is Sql server 2000.

Wrox's professional SQL Server 2000 and M$'s Inside SQL Server 2000.

Chris is right on the layering of code. At the least encapsulate code into functions and procs. Eg for inserting some data: create a function to insert the data, that function calls a proc to insert the data or retreiving data: create function to retrieve data, that function calls proc to retrieve the data. Obviously create other routines to apply your business logic. One real advantage of using function to return your data is that if the underlying tables change, the chages are limited to that function.

Try to use procs for working with data.

regards
David Cameron

Hi David,
Thanks for the response. I will check out Inside SQL Server 2000. So maybe i can create dataviews and stored procedures in a seperate database which sort of provide interface for actual data. SO when the database schema (data table ) changes, I will have to change this newly created database which has views and stored procs. I dont think there will be a way to write something generic enough (which i am trying to achieve) which will work with new database(once its in place) and old database schemas WITHOUT someone manually changing some stored procs and views.

Paul.

I'd avoid creating a data access database (ie a database just to hold procs and views). You'll take a slight speed hit and it is likely to be confusing as it is more normal to put the views and procs in the database they are accessing. If you are concerned about people accessing things that they shouldn't, block all permissions on tables, so they are forced operate on tables via the procs and views only.

There is no question that if one thing changes (table), others will have to change (procs, accessor code, business logic etc), however if it is well designed code you limit the changes.

Also I'd try to avoid making something too general. This does no work well with databases. Picking a couple of ways you might generalise:

* Proc that returns everything for a table you specify.
Problem is that this goes into dynamic SQL and the proc will be very poorly performing. See:
http://www.algonet.se/~sommar/dynamic_sql.html

* General table to hold anything, ie structure is ID, AttributeName AttributeValue.
The performance on this one is hideous. Also it flys in the face of good normalisation practices.

Anyway, just try to keep your database normal and to avoid swiss army knife procs (ie procs that do everything).

regards
David Cameron

Ok Thanks! I will check this out. Thanks for taking the time out to write back.