Rewriting the system... keep the old schema?

Question

Suppose we have a system (in production) written in an obsolete technology and difficult to adapt to changing business needs. The decision has been made to rewrite it in a newer technology. Should we start fresh with a new database schema that will accurately reflect the data models of the new system but accept the risks and costs of developing a database conversion (necessarily 2-way due to a phased implementation plan)? Or should we keep the same schema, even though it will complicate development since it does not reflect the new model, but gain the advantage of eliminating the conversion task?

Answer 1

Your database should meet the needs of the program, not the other way around. If your business needs have changed, no doubt the data has too, and if it were me I'd be taking this opportunity to update the program as a chance to update the database to your changing needs as well. The time you save not converting the database, you'll waste creating hacks to make it work in the new program.

Answer 2

As a developer and application maintainer, getting to start over with a new schema and an application rewrite is a dream that is not realized often. Given only the information in your question I would tend to go for the new schema and the conversion work.

BUT...

There is a ton of missing information needed to make the correct decision. Like: How will the decision effect the budget and/or schedule? What are the problems with the current schema? etc.

SO...

As a project sponsor and business analyst, I want the cost justified by having a good return on investment. Remember, any time and money spent on the new schema is time and money that could be used for features or other projects. Some questions to ask your self when looking at it from this point of view are: Will the new schema reduce maintenance costs? If so, how much? Will the new schema give us an advantage by giving us the ability to add the next set of features faster? Is there some inherit limitation of the old schema that is keeping us from meeting goals? Will the new schema provide performance gains that will lead to more satisfied customers? etc.

I'm afraid the whole picture is needed, and even then once you choose a road you will never know how it would of turned out had you made a different choice.

Answer 3

I believe that you should re-design the schema. There is no reason to take old problems to the new implementation. the data converting is a one time mission, it will take some time, but on the bottom line - you'll get a better result for the long run.

Answer 4

It's a case-by-case question. How much will both choices cost you in the short term? In the long term?

That said, it's probably better to take this opportunity to redesign your schema. It's certainly better to do it now, when you're rewriting lots of code anyway, than a few years down the track.

Just remember about this issue.

Answer 5

You'd have to weigh the time added by additional complications of development against the amount of time it would take to convert the database.

Personally, I can't stand working with a schema that doesn't accurately match the business requirements and can't be changed. It always introduces lots of hacks and bad code smells.

I would imagine the additional complications in development would outweigh the usually straightforward ETL type of task of database conversion.

Answer 6

I believe the meat of your decision will fall into the How, rather than whether or not to change the schema. Be disciplined and thorough in your choice of solutions.

You should assess the services the old system provides, with the goal of acknowledging all the business requirements it meets that must also be met by the new system. How much of the new model cannot coexist with the old (i.e. is it possible to decouple the parts of a complex transition?) How tolerant is your system to risk?

The more pieces you change at once, the less likely you are to get what you expected from the whole endeavor in the first place.

After you get through all the mechanics of your migration, be sure that you have ways to verify and quantify the characteristics of your new system. Do you still meet all the business requirements? Are the old system's inadequacies actually resolved? Does the new system perform better? Does it reduce your maintenance overhead?

So on and so forth.

Answer 7

I would only consider redoing the schema if you have a a database professional to help you with the design. Application programmers in general do a poor job of designing perfomant databases that have all the checks and balances a business critical system needs.

Changing the schema and successfully moving existing data is a much harder job than you may think. This will be a large effort taking months of full-time work and it's risky. The larger and more complex the existing database is the harder the redesign.

One thing I would consider is moving the old data to a data warehouse and then designing the new system for data going forward. It would then send data to the data warehouse periodically too for people to be able to query historical and current records. That way your new system can have constraints that maybe the old data didn't have and you won't have to try to figure out what values to put in the required field for old data which did not have a value.

If you are considering this, you may also want to read up on refactoring databases. Here is an excellent book onthe subject: http://www.amazon.com/Refactoring-Databases-Evolutionary-Database-Design/dp/0321293533/ref=sr%5F1%5F1?ie=UTF8&s=books&qid=1257433737&sr=8-1

Also do not consider doing this without readding in depth about performance tuning the database you plan to use as your backend. THere is no point in redesiging if you don't develop something that will perform well and scale up. Forget that trash about premature optimization - databases need to be designed from the start with performance as well as dat aintegrity and security in mind. There are a lot of well-known techniques to create better performance that should be considered in any redesign.