Image Map of Navigational Panel to Home / Contents / Search Denormalisation and Girls

by Peter Wone - GUI Computing

Image of line

Undoubtedly many are surprised to see an article like this from a bloke who prefers Fourth Normal Form to Third "on principle", but actually there are occasions when denormalisation is advisable.

Iíve been revamping GUIís clients database in sporadic bursts since January. The whole shebang was in 4NF ó not so much by design as because thatís how I think. Everything was sweet, the interface was totally generic and quite simple, and then I ported the data. All ten thousand plus clients.

Oh my, but stressed performance was abysmal. With only one user. Even at the top level of a query, calculated fields are a disaster when you have ten thousand rows for which to calculate. So donít use calculated fields? I hear but canít obey. Clients are subclassed into two tables (People and Companies) and the interface must provide homogeneous display and searching facilities. An expression is therefore necessary to merge three fields (CompanyName, Firstname, Surname) into one (Name).

It gets worse. You canít index a calculated field, and Rushmore technology canít come into play. Hmm, thinks Peter, baulking and hesitant, a d-d-denormalised table... Then he goes and does it, rather like his discovery of girls: with no further hesitation or thought for consequence.

The whole business is difficult. Special handling is required for every update. Error handling has to be fast and robust. Oh well. If you choose carefully itís worth it (also much like girls).

Now a Name field is kept in the superclass table Clients. It bears none of the performance penalties of calculated fields, and because itís indexed, Rushmore technology makes retrieval wonderfully swift. Retrieval delays on the main form have dropped from 30 seconds to hardly discernable. Because all the data is still stored in (and can only be edited in) tables which are in 4NF, flexibility and data integrity have not been compromised. I can even retain the moral high ground by asserting that as "...information stored in the field [ĎNameí] cannot be obtained by means of a join..."1 but only from calculation, the field Name does not represent redundant data according to the terms of any projection-join normalisation methodology. Truly.

1. Adapted from a comment made by C.J.Date in DATABASE Programming & Design. I canít find the exact reference, but the veracity of this idea can easily be derived from Dateís comments on Projection-Join Normal Form in An Introduction to Database Systems, Volume I.

Written by: Peter Wone
June 94

Image of arrow linked to previous page Image of arrow linked to next page
Image of line