Best Practices for Redesigning a Database

Question

I'm aware of some general best practices when designing a database for an application, but what about redesigning?

I'm on a team tasked with re-designing an internal business application, though despite me saying "internal," I'm unfortunately many, many layers of people away from contact with the actual users of the system.

The current program is in Oracle Forms, scattered across a bunch of non-normalized tables, sometimes with multiple near-duplicate tables holding slight variants on each others' data. The constraints are often in the form of poorly-enforced stored procedures. Even the types don't seem to be stored right. I've encountered all sorts of bad data that Oracle seems to ignore but gave fits (and rightly so) to SQL Server's Import/Export Wizard. (For example, two digit integers do not constitute a complete datetime!)

The original program probably goes back twenty years, and all of the original developers have retired so long ago that even the older people here have no idea who they were. As a result, there also aren't really any clean requirements to go off of--we're just supposed to duplicate the existing application's functionality and keep its existing data.

The end result of the rewrite is going to be a web-based version running on ASP.NET with MS SQL Server for the back end.

My other two developer teammates are much, much older than me, both with business/MIS backgrounds whereas mine is CS. The senior member's experience has been almost exclusively Oracle forms and the other member has mostly done business applications work in Visual Basic. Although my database background has been limited to designing new databases for projects in MySQL or SQLite, mostly for my undergrad classes, I seem to be the only one with experience actually designing databases at all.

I've already written a little program in C# that reads in all the existing data to a neutral format, ready to be re-cast and placed into a new database. I plan to write the load-in code after the destination database is designed, so that data can be properly split across the new normalized tables, added in the correct order to follow new constraints, etc. The same program could then be run again later to copy the production data to the real newly deployed finished redesign. This leaves the actual redesign of the database as the main thing to figure out.

So the heart of my question: what are some best practices for doing a redesign from the database level up of an existing application?

Answer 1

I think you already know how to normalize a database.

What you need are strategies for minimizing the risk when moving all of the software to the new database.

What I'm suggesting is more work as a trade-off for less risk.

Normalize the database, and create a process to populate the normalized database with data from the original database. The original database will be the database for inserts, updates, and deletes. The normalized database will be the query database only during the conversion.

Your populate process will have to run as often as the need for query data. If day old data is acceptable, you can run a nightly populate process. If you need more current data, you have to run a continuous populate process.

Build the query portion of your new ASP.NET system, pointing to the new normalized database.

The query results from your new system should compare with the query results from the original system.

You could stop at this point. That's a business decision, not a technical decision.

At your leisure, you create new insert / update / delete functionality in your new ASP.NET system. As you create the new functionality, you turn off the parts of the original system that correspond. At some point, nothing of the original system remains.

The advantages of converting in this manner are reducing risk by building the query portion first. Generally the query functions are simple compared to the business logic embedded in insert / update / delete functionality.

You convert the insert / update / delete functionality one process at a time. If there's a problem with misunderstanding the business logic, it can be fixed while your users are using the original system.

It should go without saying that your populate process better be absolutely consistent.

Answer 2

Try to convince them to contract the development of the new system to an outside company, there are a lot of good development companies that have the resources to properly develop applications faster and better than your limited team. A good development company will also be able to force your superiors into doing things they may not do if you requested to do them, the PM of the company getting paid a lot of money to develop an app has a lot more pull to get user involvement than the IT guy many levels below the management authority to arrange such things.

It costs a lot of money up front, but it will pay off big time to have the proper resources for development and implementation. If you do manage to get an RFP put out I would wager that the bids you get indicate that what you are trying to do is far more complicated than your managers realize.

Answer 3

Design the normalized database you need with the data types you need. Then the hardest part is migrating the data. First you need to have a plan for how you are going to map from the old to new and what you are going to do with data that doesn't meet the new structure. For instance you may have data is that is now unidentifiable if you didn't have proper integrity constraints. You may want to simply not move that data or you may need to move it but attach it to a new parent record called "Unknown". If a date isn;t really a real date, can you put a null in the field when migrating? You will need answers to those sorts of questions. I woudl suggest that you have some of the developers work on changin the gui to use the new database struture and others to work strictly on the migration. The migration is huge task, it will taks a lot of skill and alot of time. Don't leave it as an afterthought.

Since you are using SQL Server, you can do the actual migration through SSIS.

Create a good solid set of test cases so that you can compare that results with the old system are the same with the new system.

Because you have so many years of data you may want to migrate in two parts. First migrate most of the data and then when it is time to go live, only migrate changed data. Of course you would need to put controls in place on the database to be able to find changed data which you may not yet have. You may also comsider at this time if you want to archive some data.

Answer 4

I am confronted with re-designing of database schema almost daily due to support and further development of several legacy applications that were born as MS Access files (.mdb) and then grew up to large databases with several hundreds of tables now located on MS SQL Server but still having the "infant deceases" of the original design. Here are some practices I found useful for me:

Try to minimize the publicly available surface of your database schema.

This means that you should try to design some public API that you make available to external applications. I normally try to implement the static data as views (even if they are just based on a single table) and dynamic data as parameterized views or as stored procedures. For data queries where only a single value is enough one can also use scalar functions.

Only these (views, stored procedures and scalar functions) are visible to external applications (via ORM or directly) and used for all CRUD operations. This schema is then frozen completely, whereas internally you might change the underlying tables, improve your procedures etc. -- this won't break the compatibility to your application.

Try to optimize for real-world criteria, not for those from books.

Normalization is a big topic in every book on database design. But in real life there are cases when normalization won't bring you much or even slow-down your database, for example if you have some data that is repeated, but the percentage of repetitions is very low etc. I am not against normalization, what I am trying to say here is that you have to tackle it with some skepticism and prudence.

Record profiling session and analyze them.

Database re-design based solely on the database schema is not complete. Look at your database in its dynamics, try to find the bottlenecks during load tests and address those. In case of MS SQL Server there is a special Tuning Advisor that can generate some recommendations upon the recorded activity trace.

Answer 5

Here is my answer:

1. First understand the current database system as much as you can. You need to know all the uses of these system as well as the users. You need to know all the sources to the system as well as the systems it might be serving as their source system.

2. You need to identify all the different uses of the system, whether it is for operational or reporting or both. Identify the applications and the upstream system that might be using the database. By doing so, you will know the elements of the current database that are obsolete and no longer needed.

3. Also analyze and understand the current ETL process that load data to the database and extract data from the database.

4. Understand all the data elements of the database and if you can build a box matrix which can help you identify duplicate elements.

5. Having obtained all the information, you can approach the redesign as if you are designing the database for the first time with the information you have gathered as part of your requirement gathering.

6. Good luck!