Minus Oneth Normal Form

We used to joke about some of the database structures at my previous place of employment - for example, comma-separated lists of name=value pairs - as being in minus-oneth normal form. We were forced to do stuff like that because we were working with huge databases and impossible time constraints, and simply could not afford to take the databases offline to make the changes we really needed, so we had to stick data wherever it would fit.

(There's a good name for this sort of activity: deficit programming. I hate deficit programming.)

Why do I bring this up?

I've just been denormalising our database. We are running, right now, on a hacked-up copy of an ancient version of Movable Type. In preparation for moving off Movable Type, I am trying to get it into a form at least somewhat similar to the Minx structures.

Part of the problem is that MT is highly normalised. There is, for example, no record of how many comments are attached to a particular post. MT executes an SQL query to count them as needed. Minx embeds it within its queries. But being forced to embed a carefully grouped count function within a query that is assembled ad-hoc depending on parameters set by the blog owner and the individual template tags is something of a pain.

So I de-normalised the database. Now we have a comment count field.

There are only two pieces of code that add comments to the system, and I've patched those to update the new field. A database trigger would be a much better solution, but this is MySQL 4.1, and it doesn't have triggers.

Unfortunately there are also several things that delete comments, so I need to hunt those down as well. Or just set up a routine to recalculate the comment counts every so often. Until we move to Minx. Whenever that might be...

Update: And, uh, just breaking the entire blogging system in the process. Stupid MySQL.

Posted by: Pixy Misa at 10:56 PM | Comments (12) | Add Comment | Trackbacks (Suck)
Post contains 337 words, total size 2 kb.