Monday, May 11, 2009

I Dream of SQL

The dream was solid white. In front of me, hundreds maybe thousands of lines of code were scrolling upwards, too fast to read, like I was holding down the arrow key. It was SQL code with color syntax highlighting and a musical accompaniment. As each line of code scrolled past, each word would light its own color and play its own note, like a player piano, like the old days when you could place a transistor radio next to the CPU and hear the loops and jumps in your program. It sounded terrible. Out of key, out of tune, no rhythm or melody at all. Just a jumble of notes blurring into white noise.

There I was with three sheets of player piano music, written entirely in SQL code, one for each of the three major subsystems, side by side, all scrolling together. Each word in the code would light its own color and play its own note as it flew past, too fast to pay attention to the individual SQL statements, but I could see the code structure and indentation as rhythm, the overall form as tonality, and I could see the three part harmony. Nice to know those music classes are good for more than just singing around the campfire.

First I had to arrange the structure so that the three were in rhythm with each other. Once the three database structures matched, I then transposed them to the same key. At that point bulk copies could work efficiently, but the melodies still needed work. I made my arrangements through the night, sleeping soundly, until I had a beautiful song I could enjoy. It sounded great. I played the entire composition several times but to this day do not remember how it goes.

I woke up in the morning and went to work, refactoring and rewriting the SQL code until it matched the previous night's arrangement. By mid-afternoon I was running my first data synchronization tests. It worked.

The hospital had its data model.

Sunday, May 10, 2009

Dreaming in Code

I bought the book Dreaming in Code by Scott Rosenberg not long after it was published, and it's been sitting on my shelf, unread, ever since. I've read a lot of books on computer history - in fact, most all of them covering the period from the 1970s to the 1990s. After I arrived here in 1997 I've been close to many of the historic events that comprise a whole new series of books. This one, though, I should read for two reasons:

One, it's about the pitfalls and mistakes that can bring down a large software project. Some of the reviews said it can hit too close to home, but also make you feel better, as some of the top minds in the industry are not immune to the same mistakes.

Two, I have actually dreamed in code, which is the story I want to tell.

It was late in the early phase of a big project and I was responsible for building a multi-source enterprise data model for the entire hospital. The tools at my disposal were a SQL database, bulk data copies, stored procedures, and data transforms using a view/copy mechanism I developed for the physician directory years earlier - which was to be adapted one of three major portions of the enterprise architecture, the other two being physical locations and services provided.

The main problem was data flow.

I struggled for weeks with various data models, trying to fit them together, integrating the different source systems, keep the updates moving reliably from staging to production, catching errors before they do, and making the whole thing easy for one person to oversee.

Everything I tried either caused conflicting changes to overwrite each other, or entire data copies to fail. Each of the three main subsystems: physician, location, service, was an entire application data model in and of itself, and they were interlinked in increasingly complex ways. The end result, if it worked, would be amazing. Whether you're looking for a service, a doctor, or the nearest location for either, all the connections would be there.

If it worked.

(to be continued)