Database geekery

[bcholmes]

I'm looking for database wisdom.

Database modellers often refer to "third normal form". Does this imply a first and second normal form. How are these levels defined?

Comments

[hellsop.livejournal.com]

concur, regarding a little bit of denormalization.

An example of this kind of denormalization comes out really well with regard to province/state codes.

To be technically correct for the form, an address would not be allowed to put a province abbreviation directly into the database. Instead, there would be a code for the province, that combined with the country, would be keys into another table that contained the province name and its abbreviation. Similarly, the country wouldn't be stored as a country name or abbreviation in an address record, it would instead be a foreign key to a country table that would have the abbreviation, country name, etc. in it. outputing an address record to a label or display would require fetching the address record, then fetching the country and province records to get the abbreviations for the display.

Rarely does that level of strictness become handy for a lot of elements, and addresses will store the abbreviations directly, so that only one table fetch is required instead of three.

Several years ago, a coworker had the joy of aiding a group that was firmly convinced that rigorous normal form was desirable. They build the databs thusly and started importing data from the system that this one was to replace.

The first thousand records got loaded in about two seconds. The second thousand took about 10 seconds. The thrid thousand took about 45 seconds, and the fourth thousand about three minutes. They gave up trying to load data after approximately 10,000 records and it was possible to read every record completely during the time that it took to load it. They got 0.1% done.