The Wrong Way

I got a call for help from one of my teammates today. He was supposed to deliver a data correction script. Unfortunately, the thing was not compiling. He wrote up this huge script. Then he tried to compile it. Of course it did not work.

I found a slew of problems. There were syntax errors. Wrong keywords were used. Semicolons missing. The poor guy is not strong in PL/SQL. So he could not decipher the error messages. I helped him for a while, getting past tons of bugs quickly.

This all could have been avoided. Don't just blindly code and hope to eventually compile the thing. Compile it after you write your first routine. That way the code delta is small and you can hone in on the problems.

I write PL/SQL pretty well. However I still start checking for errors as soon as I start coding. I even stick in a NULL in place of the code and make sure the function and procedure declarations are correct before I implement the first line of business logic. Take it from a pro buddy. Test first.

The New Normal

I saw a job that required familiarity with the first and second normal forms. Now I never properly studied database theory. But I got a lot of on the job training. A whole lot. Still I figured I should at least know some terminology. So I googled around.

The goals of normalization are to reduce duplication and to put data where it belongs logically. Every rule in normalization is a normal form. Edgar Codd (E.F. Codd) came up with the first three normal forms by himself. These are the only forms most apps will ever need.

The first normal form aims to get rid of duplicates. Do not arrange tables for any specific performance. Make sure attributes have only atomic values. Mae sure attribute values are unique.

The second normal form requires the first. In addition, attributes must be dependent on the whole key to the table. Separate tables should be created for values that go with multiple records. Create foreign keys to reference the primary keys in the tables.

The third normal form requires the second. In addition, attributes must contain a detail about the entire key (especially in the scenario of composite keys). Put another way, there should be no fields that do not depend on the key. This is the form that people usually mean when they say normalized.

There is obviously more to database normalization than these high level descriptions. But this is a start. I think the real value comes from having to design databases over time.