Database Design: How Table Normalization Can Improve Performance

Three Designs

We'll try to measure the relative costs of a database which degrades through a comparison between three designs--denormalized, partially normalized, and fully normalized.

• The denormalized design is the original MESS design.

• The partially normalized design partitions columns into "core" and "extension" using a rough estimation of the relative frequency of update. Attributes which are rarely or never updated are part of the core, and will be quite compact. Attributes which are updated--in particular, those with an initial value of NULL--go in the extension. The extension is expected to fragment, but the fragmentation will be isolated and (hopefully) under some control.

• The fully normalized design partitions columns into the dimension and a fact table that records each individual event in a separate row. The idea is that events accrete. Instead of updating a NULL value, a row is inserted to reflect a change in the prospect.

  Listing One is the denormalized MESS design. The evs are "Events" which are filled in by updates, leading to fragmentation. The business processing makes ev1 mandatory, and the other events may, or may not happen.

  CREATE TABLE MESS(
  key VARCHAR(20),
  ev1text VARCHAR(100),
  ev1date TIMESTAMP,
  ev2text VARCHAR(100),
  ev2date TIMESTAMP,
  ev3text VARCHAR(100),
  ev3date TIMESTAMP );
  
  

  Listing One: Denormalized MESS design.

  Listing Two shows a normalized design which controls fragmentation by isolating ev2 and ev3 event data into a separate table. The M2 table can be sparse and can fragment.

  CREATE TABLE M1C(
  key VARCHAR(20),
  ev1text VARCHAR(100),
  ev1date TIMESTAMP );
  CREATE TABLE M1X(
  key VARCHAR(20),
  ev2text VARCHAR(100),
  ev2date TIMESTAMP,
  ev3text VARCHAR(100),
  ev3date TIMESTAMP );
  CREATE UNIQUE INDEX M1C_X1 ON M1C( key );
  CREATE UNIQUE INDEX M1X_X1 ON M1X( key );
  
  

  Listing Two: Normalized design.

  The most interesting design is in Listing Three. This uses inserts instead of updates to fold in the additional data.

  CREATE TABLE M2C(
  key VARCHAR(20) );
  CREATE TABLE M2E(
  key VARCHAR(20),
  evt NUMBER,
  evtext VARCHAR(100),
  evdate TIMESTAMP );
  CREATE UNIQUE INDEX M2C_X1 ON M2C( key );
  CREATE INDEX M2E_X1 ON M2E( key );
  

  Listing Three: Using inserts instead of updates.

  I used three update scenarios for each design. The first job did only two updates--one to update each sub-entity's attributes. I compared this with scenarios doing 5 updates and 10 updates. The higher number of updates would show the effects of any storage reclamation strategy the RDDBMS used.