Database Maintenance

[SQLSister]

In the SQL Server forum we started to discuss on one thread the need to redesign a particular production database. I thought I would bring up the topic in here and see what thoughts everyone has on maintaining poorly designed databases.

Most people who have worked with databases awhile have run across a database developed by someone else that is a business critical function but is so poorly designed it is inefficent and very hard to maintain.

Most of us put up with that for awhile because it seems too hard to restructure the thing and do it right. Often our bosses won't give us the time to fix problems at the structural level; they just want a quick fix now because the business needs the application running right now.

So when you do decide to fix a problem in the overall design of the database and when do you make another spaghetti code fix just to keep it running? And how do approach management to allow you to fix the underlying issues not the symptoms?

Do you have a good strategy for making incremental changes to the database to fix underlying problems?

Questions about posting. See faq183-874

 

[ceh4702]

I would suggest you make it harder and harder for people to get the data they want and make them wait for the information they need longer and longer and just flat out tell them it is the fault of the system. If you keep bending over backwards then they will not think anything is wrong.

If you do not like my post feel free to point out your opinion or my errors.

 

[ormsk]

Hi

I'd be inclined to talk to them in a language they understand...mainly money.

However, you'd have to come up with a feasibility report in terms of :
1. how long it is likely to take to fix
2. How many reports per annum are requested
3. The average time it takes to build a report using the existing structure
4. An average time it will take to build a report using the new structure

There are other hidden costs in terms of your sanity and the local knowledge you have acquired of the old system, that is, if you left, someone else would have to pick up the pieces and some 'quick' fixes you had learnt, will not be quick fixes for a person unfamiliar with the existing system.

 

[ceh4702]

I should probably expand on this issue a little. I probably would not recommend you just keep trying to do a fix here and a fix there and nurse a system along. The more applications and programs you write for the old system the more things will have to be rewritten for the new system.

Another way to look at it is processing time and wait time. The more inadequate your system is the less useful it is. There is also the lost opportunity cost of not having a better system. Sometimes folks get entrenched in the old system and they just dont want to change it because it still works. Many people management and users fear change.

If you had an outside party evaluate your system they might be more willing to listen the them.

If you do not like my post feel free to point out your opinion or my errors.

 

[bethae3]

I have to agree with ceh4702 - if you do it piecemeal, it's going to get ugly. Extract the data, save it, tell everyone the glitches are the fault of the system and will be fixed "eventually", and design a database that will work. Populate it with the data you extracted and send it out over a weekend in place of the original. Just make sure the entry form is VERY similar, or you will have nothing but hours of user training.

Bethae3
The phrase "working mother" is redundant.

 

[jrbarnett]

I had a situation like this at a previous employer. I was given the responsibility of converting an old Clipper system (data in .DBF files) to Access, knowing nothing about Clipper.
There were no primary/foreign keys on tables as with dbase, and I found where link tables would ordinarily be used, there was a field in the table with a list of comma separated values, some which (and some that didn't) match the appropriate lookup table.

My strategy was:
1. Develop an Access based system using a normalised version of the existing structure with proper referential integrity set up.
2. write an application to extract data from the DBF files and dump it into the equivalent tables in Access, recording where the keys didn't match up (I did this by inserting a new value into the appropriate lookup table, and setting the description field to "***Value Unknown"
3. write a new Windows based version of the application in Access.

In many ways, I consider the conversion program some of my finest work to date because of the huge number of hurdles it had to overcome (and it never fell over, which is saying something), but it was one of these I wished I was able to start with a clean slate from scratch, because there were quite a number of anomalies there that I had to deal with in the accompanying VBA code, and the log files it wrote giving numbers of records in source/destination to help me analyse the differences.
The fact that it was for internal use only, and the client never actually saw it doing its work and my analysis afterwards didn't help the cause of "this report isn't exactly the same as that report" from the client when they complained that the data was different - because of the different entries.

This took about 40 minutes to run, to convert about 10 years' worth of data in 30 files from DBF to MDB. After that, there was the necessary analysis of the "newly added" records to see if it was a quick typo or something more serious.
Although I had access to the source code, not being a Clipper developer I didn't understand how it worked or what it did exactly behind the scenes. I'm not sure whether that was a help in designing the new system or a hindrance in not understanding exactly what was going on in the program.

John