Dot Net Tricks

Despite that stupid presentation about "normalization is for sissies" that went around the net for a while, I'm very pro-normalization. Normalization and the standard Third Normal Form (3nf) simplify data management and application development greatly.  This is especially true when writing object-oriented code or using an ORM, but I normalized long before I knew what an object was.  For SOME reason, there's developers still out there that choose not to normalize.  I ran into this problem quite a bit when I worked for a certain online matchmaking company.  They had a lot of denormalized tables and it made the application more complicated, although that was the tip of the iceberg in the IT and general mismanagement at that company.  I won't say what company it was except that it was a truly interesting experience.  In a bad way.

Again, denormalization has reared its ugly head today.  This time with a company and product I WILL mention, Telerik's Sitefinity.  Overall, Sitefinity has some good features but upgrading from one version to another is always a pain.  Today we ran into an issue that that we blew hours on and could have been easily avoided with a little 3NF magic.  Below is the forum post to Sitefinity.  Its a bit of a rant, but I want to illustrate to all you other developers the kind of extra problems you run into when not normalizing.  Note that I DO say there are times for denormalization, but its one of those cases where you have to know the rule in order to know when to break it.

Posted at:
http://www.sitefinity.com/support/forums/support-forum-thread/b1043S-bthhde.aspx

We did a 3.x to 3.5 upgrade this week on one of our existing websites, and ran into some pain.  On of our other developers spent 4 hours working on it, which in fairness is partly because we have our own customizations in the site.  However, he got stuck on one particular error.  The error could have easily been avoided if you would just NORMALIZE YOUR DATABASE.  Its really not that hard and would help us developers greatly.  I'm not sure if its your developers that don't normalize certain things or if its Nolics itself, but not normalizing means more room for these types of errors and more data that has to be touched when somethings changed.  Below are the steps I as the lead developer had to go thru to debug and correct this problem:

So the other developer was getting this error related to the Sitefinity upgrade that said “Key not found for value UpcommingEvent.”

"Upcomming" was misspelled so i figured the error was related to the Sitefinity developers correcting the spelling.

Looking through the stack trace, I saw it was trying to parse this value from into an enum and not finding that string in the enum thus throwing the error.

Since all the code had been completely replaced by the new version (3.5) I assumed the problem had to be in the database.

There are like 100+ something tables in Sitefinity, so going thru them one by one was out of the question.

I opened up sql profiler, ran the page again and collected all the generated queries to the database from sitefinity.

Nothing in the queries mentioned “UpcommingEvent” so instead I ran all the queries that profiler found and looked at the results.

I converted the query results from grid view to text view in sql management studio.

I then did a find for UpcommingEvents, and found the text!

I found the query that got that result to find the table and column holding that event, something like "sf_cmscontrolproperty" and "value".

I did a LIKE search on that table and column to find all the records that had “UpcommingEvents”.  19 records had this text in it!

Not knowing what the right value was to put in there, I did a search in vs.net in object browser for anything that had “Upcoming” (spelled correctly) in the hopes I would find an enum with this value and that this would be the correct value.

Sure enough there WAS an enum and it had 3 values, something like PastEvents, CurrentEvents, UpcomingEvents (spelled correctly.)

I copied the exact enum value into sql query analyzer and updated the 19 records from “UpcommingEvents” to “UpcomingEvents”

I ran the site again, and it was fixed.

This took over an hour.

If the database had been normalized, with just the value of the enum (the integer) in the database, this could have been avoided.  You could have changed the names of the enums without hurting the data at all, as long as you didn't change the underlying values.

Anytime you see repeating data (in this case "UpcommingEvents" your breaking normalization and creating work for yourselves and/or your customers.  3NF has been around for a long time, I'm not sure why so some people choose not to use it when it prevents a slew of problems.  Its okay to denormalize certain data thats non-authoratative and can be recreated from normalized data (such as for reporting) but this wasn't the case.

Thanks,
Craig