I have been a martial artists for over 20 years: Karate, Kung-Fu, Jiu-Jutsu and currently Filipino & Indonesian martial arts. Most traditional martial arts employ a series of movement forms or patterns that in the Japanese martial arts are called Kata. The purpose of a Kata is to build spontaneous instinctive reaction and motion. It is a method of refining movements to an efficient perfectly ergonomic motion.

Such repetitive focused study is not too dissimilar to the practice methods in other arts. A musician will spend years practicing scales and études (studies), and a fine artist will spend significant amounts of time practicing technique and form in sketchbooks. Such focused study is how an artist trains to master the techniques of their art to the point where they become spontaneous and apparently effortless.

Software development is based on principles of computer science. However, designing a solution to a problem and coding that solution into well formed computer code is a craft: as much art as science. Sara Chipps provides a good description of the qualities of developers who embody the art of programming in her article Natural Programmers (Code Monkeys) vs. Career Programmers (Geeks in Suits). What stands out in her description of "Code Monkeys" is a total absorption and immersion in the joy and adventure of computer code. Code Monkeys live and breath code in the same way any other serious artist is thoroughly committed to their art.

Dave Thomas from Pragmatic Programmers has started to develop an concept of Code Kata borrowing the concept from Japanese martial arts. His argument is that most programmers learn their craft in the heat of battle while engaged in project work. The problem with this is that programmers tend to optimize on the solutions with which they have already researched and implemented and continue to follow those same solutions over and over again even though there may be much more efficient or elegant solutions.

A code kata is a focused study on a discrete problem with the emphasis on exploring different ways of solving the same problem. One of my favorite code exercises that could qualify as a code kata is the 196-algorithm. This has no earthly use whatsoever, but it is a good way to practice list handling, sort, recursion and many other different software algorithms. It has become my "Hello World" app when exploring new computer languages.

Of Dave Thomas' Kata I really like "Kata Five: Bloom Filters" particularly since it is working with bit sets, hashes and I have come across a sufficient number "item is member of set" problems for this to yield some interesting results.

Other than the CodeKata blog I would recommend Steve Yegge's article Practicing Programming as a source for Code Kata, or practice drills as Stevey calls them. Perhaps you can come up with a few of your own?

Many people struggle with complex data models that often require multiple tables to be joined together to satisfy a single query. Joining up to three tables is usually not a problem for most DBAs and developers. However, queries that may involve 6 or more tables (including joins on the same table) can often confuse even the most stalwart developer. Troubleshooting such a query can be a major test of faith and fortitude as you scan lines of JOIN expressions and WHERE clauses. I am a visual person, I usually find it easier to solve a problem if I can visualize the various elements and components of the problem. For that reason I developed a query modeling technique several years ago that allows me to diagram even the most puzzling and arcane query problems. I recently modified that modeling 'language' and have converted the main concepts into the UML. My original modeling language was based on ERD diagrams, but it translates very well to UML. I find this approach to query modeling extremely helpful and hopefully it will prove useful to others also.

The basic format is quite simple and is a stereotyped version of a standard UML Class Diagram:

The major elements of the model for a query element (a database table) is show in the diagram on the right. It consists of a box with the name of the table (stereotyped with the label FROM) and the box is divided into two compartments. The first compartment is stereotyped as the SELECT compartment and the second is stereotyped as the WHERE compartment. In the SELECT compartment the fields that will be retrieved from the database by the SQL SELECT statement along with their field aliases as expressed in the general format: "field : alias".

The WHERE compartment lists the constraints that must be placed on the table to limit the return results. These are typically expressed as generic SQL WHERE clauses (see the example below). The dotted box attached to the FROM area represents the alias used to identify the table. In UML this element typically represents the template or generic type of a class, but here it has been stereotyped and reused for an entirely different purpose. As you will see later clearly marking the table alias in this way makes referencing the same table with different aliases as if they were logically two different tables a very intuitive concept. Visualizing tables by their aliases in this way also makes reverse engineering a complex query much more transparent.

A query model is composed of a table definition for each table that will be referenced to satisfy the query, joined by a line that indicates the join criteria (i.e. what key / foreign key relationships need to be expressed in the query). It is important to know that query modeling should be conducted in an agile modeling approach. The model needs only to be sufficiently complete to solve the problem at hand. It is not a design artifact, only a tool for increasing understanding. Typically I develop a model on a whiteboard and treat it as a highly disposable diagram. Once the problem has been solved the model serves no purpose. Creating a model in this way usually takes a matter of minutes and saves a significant amount of time that would otherwise be spent gazing at a convoluted query in SQL form.

This is a full example model of a query to retrieve a user name and the name of their associated organization under some specific criteria of selection. We are trying to extract only people who are associated with organizations identified as consultancies. Further we are only interested in users with an id less than 2000 and excluding the admin user who has the id number of 1.

There are five logical tables that must be referenced to satisfy this query and two of those logical tables are actually the same physical table (named "records"). The two "records" tables are modeled as separate tables with the same FROM designation but with different table aliases (i.e. "user" and "org"). From this we can see that user records and organization records are both stored in the same physical table. These records are related to each other by a generic table called "primaryrel". Further the type of organization is stored in a generic link table called "pickrel" that cross references to a table that stores the types of organizations "orgtypes".

Once the tables that are required to satify the query have been identified you can add the fields that will need to be returned from each table in the FROM compartment of the respective table elements. Note that in our example most of the "utility" tables do not contribute any fields to the query result and are therefore left blank.

Next you add the constraints on the tables in the WHERE compartment of each table. The constraints can be expressed in pseudo-SQL format. In our example query we wanted to retrieve only users who are not the admin (with id 1) and have an id number less than 2000. This requirement is expressed in the WHERE compartment of the "user" table. Similarly other requirements for limiting the results on each tables are placed into the WHERE compartments.

Finally, the tables can be related with JOIN lines and the join criteria added as labels to those lines. Once that has been done the model is basically complete. Sure, you can add the ORDER BY and GROUP BY criteria as a note as done in this diagram, but in most cases this information is obvious and not necessary to add to the model when used as a problem-solving tool.

If you wish to visualize the expected result set for different types of join (INNER, OUTER, etc.) you may find Jeff Atwood & Ligaya Turmelle's use of Venn Diagrams to visualize joins of some interest.

Building a model of the query in this way is very quick to do - it is also easy to convert an existing query into model format to start analyzing it for troubleshooting or performance analysis. Looking at the diagram above for example you can probably spot two table joins that could potentially be removed, making for a more efficient query (Here's a clue: what if you could hard code the id for a "Consultancy"?). Converting an adjusted model into SQL is also very easy to do. Using the query model above:

First take all the fields and aliases in the FROM compartment of each table element and add them to your SELECT clause and choose the base table for the FROM clause:

SELECT user.id AS uid, user.fname AS firstname, user.lname AS lastname, org.name AS oname, org.address AS oaddr
FROM records user

Then you can start adding the joins from the lines:

INNER JOIN primaryrel AS rel ON user.id = rel.id1
INNER JOIN records AS org ON org.id = rel.id2
INNER JOIN pickrel AS pick ON pick.id = org.type
INNER JOIN orgtypes AS otype ON otype.id = pick.id

Finally add the WHERE clauses from the WHERE compartments:

WHERE user.id <> 1 AND user.id < 2000 AND user.datatype=1
AND rel.relid = 30
AND org.datatype = 2
AND pick.table = "orgtypes"
AND otype.name = "Consultancy"

Once you have constructed those portions of the query you can add any group by and order by clauses and you have your query!

One important note - I have not provided an example of a query that uses "HAVING" statements in the GROUP BY. However, you simply follow the same scheme adding an additional compartment. Hopefully, this modeling approach may prove as useful to you as it has to me over the years.

"You want to know how two chemicals interact. Do you ask them? No, they're going to lie through their lying little chemical teeth. Throw them in a beaker and apply heat."
Dr. Gregory House, MD (Hugh Laurie)

I'm fairly confident that Fox or CBS will never make a TV drama about software development. It's for the same reason that there are few biopics about writers or philosophers: it is simply not very interesting watching someone think or tap away at a keyboard. Sure, if my job involved saving the world by skateboarding in transport terminals evading the FBI while hacking into the mainframe of a corrupt corporation, movies will certainly be made about it. Sadly though, my profession verges perilously close to the same degree of glamor as accountancy; and yes there are times I daydream about being a lumberjack.

Though I will never see an episode of 'Buffy the Code Debugger' on November 2004 a show aired on Fox that did catch the very spirit of what I do as a software architect. 'House MD' is centered around Dr. Gregory House, played by Hugh Laurie: a rather complex and antisocial Certified Clinical Diagnostician. House is a flawed antihero, addicted to painkillers, maverick to point of unethical behavior, egotistical and contemptuous of authority: an archetypal 'outsider'. Despite these flaws he is fundamentally a likable character; like Harry Callahan of the 'Dirty Harry' movies, he is acceptable to us within the dramatic context. His professional ethics are questionable, but motivated from the higher imperatives of saving lives. Human values over bureaucratic rules.

Although House is a fundamentally enjoyable character it is with his diagnostic approach that I most identify. That approach is not much different from what I do when troubleshooting a software application or system. My problem domain is different, malfunctioning software rather than a sick patient, but the diagnostic process is in almost all points the same: the application of the scientific method. Here is a breakdown of Dr. House's diagnostic methodology:

1. Gathers a list of symptoms: just the facts. House doesn't like to talk to patients because 'they lie'.


2. Brainstorm and shortlist the possible causes.


3. Research environmental factors: his team is sent to investigate the patient's living environment.


4. Research the shortlist of candidate complaints


5. Formulate a hypothesis


6. Test the hypothesis, even to the point of deliberately putting the patient in a coma!


7. Evaluate the test results


8. Repeat steps 5 thru 7 until a diagnosis is reached


9. Treat the patient based on diagnosis


10. Monitor the patient for improvement in response to treatment


11. If patient does not respond repeat steps 5 thru 10

In comparison is the methodology I use for non-trivial cases of troubleshooting:

1. Gather facts about the observed behaviors: replicating the behavior


2. Identify likely software components that might be involved


3. Consider environmental factors - dependent data sources, user conditions (browser configuration etc.)


4. Do any necessary research - support forums, software documentation (IRC is great for real-time answers)


5. Formulate a hypothesis


6. Test the hypothesis with prototype, code or configuration changes etc.


7. Evaluate the results of test scenarios


8. Repeat steps 5 thru 7 until the cause of the problem is clear


9. Apply a remedy (code patch, data repair etc.)


10. Monitor the application in the live user environment to verify a permanent resolution


11. If problems persist or new problems are exposed repeat steps 5 thru 10.

In trivial cases the diagnosis is usually reached at step two.

This approach to software diagnosis is systematic and although it might sound laborious it usually yields a more rapid problem resolution than simply jumping into the code looking for the problem. In software engineering you should always first solve the problem then apply the solution to the code. Inexperienced developers often try to solve problems in the code and that is usually the very worse place to do it.

So next time I'm at a party and an attractive woman asks me what I do I can say, "Do you like House MD? I do that with software". Let's face it, I am only in this job for the glamor.