Need your advice on Database programing

[Lunatic3]

I developed an apllication for an institute. It was a database application and fortunately it's working good without any probelm till now (although it needs revision in my opininon).
However today i heard a news. That institute has decided to change some functionalities of the program and they referred to me for that. To fulfill their request (that I dont have to) I need to add fileds to some of the database tables and write change some parts of the code.

Now i am thinking to myself that I should have predicted these kind of changes before. When we were discussing the design of the application and reviewing their needs it was clear that there will be no change in it at least for 5-6 years but now they have suddeny changed their mind.

You know what I am talking about? When you design your database you decide that for example this particular table should have 10 fields and you write your code based on that. So when it comes to adding a filed then sometimes you have to write the code from the scratch.

How one should avoid this?

[VBCrazyCoder]

I don't think you can avoid changes in code 100%, unless you are just binding to a dataset, or only displaying data, etc. The best practice is to write you code and documentation well so that it will be minimal effort to add additional functionality. You should also design your database so that it would be easy to add new fields, etc. (towards 3rd normal form).

[Lunatic3]

Thanks for your answer. towards 3rd normal form
what you mean by that?
You should also design your database so that it would be easy to add new fields, etc
I need some good book or tutorials on that. Do you know one?

[VBCrazyCoder]

3rd Normal Form - or some just use the term "Normalized". A basic definition is that your data has been broken out into a logical, non-repetitive format that can easily be reassembled into the whole.

It is a tricky subject because it is often the case that you have to de-normalize to balance optimum performance for your app.

My reference (for SQL Server 2000) is Professional SQL Server 2000 Programming by Wrox Press, but I am sure there are many other good books.

[Pirate]

You need to read this http://www.informit.com/isapi/product_id~{F1EC63A8-03DF-4242-9283-2E8EFAF74708}/element_id~{61B3B9F4-3C94-4D5F-A432-456084FCA668}/st~{CA1FEB69-C272-4194-9449-92E4E051EC35}/session_id~{7FB84E9C-F5DF-40B0-A43D-8CBEFDF22335}/content/articlex.asp . The more dynamic your code is , the less change you make . ;)

[VBCrazyCoder]

I think there is a trade off though - the more dynamic your code is, the more complex it is for maintenance.

[hellswraith]

My reference (for SQL Server 2000) is Professional SQL Server 2000 Programming by Wrox Press, but I am sure there are many other good books.
Great book, worth the money!

What helps me is first normalizing the db as much as possible, then look at performance and de-normalize it just enough to get a good balance. When using stored proceedures to access the data, I specify every field I need returned (I don't use a SELECT * FROM statement, that will return all the rows, and if you add more to the table, it will return those also). This helps because when you call that proceedure, you will get the same results back even if more columns are added to the table. This type of optimizations may seem tedious as you are doing it, but a lot more easy to continue to add to your database.