Re: normalisation vs denormalisation

I once the good fortune of working in a dual development environment. We had development group supporting our 3gl product (non-relational / non-normalized) while an advanced development group went on to develop in a 4gl (still non-relational but normalized).

Here are the unique results we encountered:

Our environment was such that requirements were always changing and had fixed implementation dates. The application was software for the Import/Export industry and therefore subject to numerous govenment regulations.

We (advanced development group) redesigned the whole product. As part of this redesign we actually went through and did Entity-Relationship Models (ERM), Data Flow Diagrams (DFD) and Entity Life History (ELH). We normalized the database down to 4th or 5th normal form. This did not last long. As most of you can atest to, for good reasons we backed off to 3rd normal form. In some cases, we actually had tables that contained summary data as well (ok slap my hands). It was pratical for business reasons (performance, timing etc..) to do this.

Now the real interesting note. As mentioned before, we had govenmental requirements constantly changing and with fixed implementation dates. What we noticed was that the system that was built from (god forbid) an "academic approach" ie.. Normalization and data modelling, was more robust and flexible. The advanced development group was to design and implement faster the changes required by the government. The other non-normalized group always had difficult time with database changes and coding changes because of the information it was built on.

Remember both systems WERE NOT RELATIONAL but yet even with indexed files we noticed drastic improvements in speed to market and maintenance costs in the normalized approach. Yes, initially we were taking longer and had less return on investment. Thank god for long-range thinking because we quickly recouped that extra cost of development time and had a significant gain on return of investment.

There is a lesson for both sides in this story. Yes, you should normalize and follow the textbook approach. It may initially take longer but the long-term gains are there to be realized. And yes, also you can't just stick to that approach there are times when you have to denormalized (just don't make it a habit).

I hope that helps both sides of this debate.

Bob
bobcat_at_usa1.com Received on Wed Aug 14 1996 - 00:00:00 CEST