System iNetwork

December 2006

« Previous Month Main

December 10, 2006

Database Modernization

On a post to this blog the question was raised: "It appears that any modernization effort needs to start with a fully normalized database. Is this correct?" Another person noted that some shops have experienced substantial performance improvements by switching from native IO to SQL, and yet another comment pointed out that many system i shops have very unnormalized databases. So I pose these questions: How important is database modernization to application modernization, must it be a first step, and what role should normalization play in modernization?

In general, I think database modernization is important but need not be an initial step, and might even be better deferred to later phases, after much modularization work has occurred. And although a high degree of normalization is usually a good thing, it isn't always the best thing in all places.

Let's remember where we came from and why our databases are the way they are. The System i community has a 30-40 year history which goes back to the Sys/3 or System360/20. For many years we did not have a relational database system, but a file system. Our preferred languages -- RPG and COBOL -- used record level access to write, update and read records from these files. In order to minimize the number of files we had to work with in an application, we often stored many attributes in a small number of files. When we moved our System/36 files systems to the DB2 relational database, we usually didn't have time to redesign our database to achieve a high degree of normalization. Since we also moved our programs and continued to use native IO, it was not immediately clear that we were headed for trouble.

But our world of self-contained applications is breaking open, and we need to integrate more effortlessly with other systems, make our presence felt on the web, and permit newer technologies to access our databases. There is an increased desire to use SQL, and those who prefer SQL expect to see normalized databases. How often do you have to explain to people with little sense of history why so many legacy databases have a low degree of normalization?!

But I do not mean to suggest that we normalize only for SQL, or that problems with legacy database designs are solely due to lack of normalization. The situation is more complex than that, but a lot can be attributed to history and the age of our system designs. Many file design practices which were acceptable 20-25 years ago, near the end of the pre-RDBMS era, are no longer acceptable. They interfere not only with efficient SQL but with agility -- the ability to adapt applications to new circumstances. Those mature systems which deserve a future need to eventually be refurbished on the database layer as well as on the business logic and user interface layers.

But how do we find the time and resources to redesign vast, complicated systems? In many cases, a massive rewrite project is out of the question, and managed evolution over time is the way to gradually refurbish valuable systems so they have a longer future and can continue to serve the needs of the business.

With an evolutionary approach, I think it will usually make more sense to first focus on segregating monolithic application designs into layered designs. Separate the user interface programming from the business logic programming, and the business logic programming from the database IO programming. With a layered application it is then a more manageable task to raise the degree of normalization of the database.

With normalization, aim for the highest but be willing to pull back wherever necessary to obtain better performance. I understand that database architects often work this way: they aim for the highest degrees of normalization, but then selectively denormalize back down a bit wherever it makes sense to do so for performance or other design reasons.

Posted by on December 10, 2006 at 8:13 PM | Comments (23)