Mental Model Meltdown

You need the right question in order to find the right answer

When I was a working programmer, I always had a healthy attitude toward management and my end users. I considered them to be frightfully stupid or in league with the forces of Darkness and Evil - probably both.

I now appreciate that they are not evil - very often. The truth is that it takes a lot of mental effort to give someone a clear programming specification from which to work.

Wrong Questions Breed Wrong Answers

A classic example for programmers is the apparently simple request for "the top three sales representatives based on their total sales this month." It sounds very natural and easy to code, but then you start playing with the loose ends and unanswered questions. Even more problems arise if you're supposed to assign bonuses. Consider the following:

1. What if you have exactly three sales representatives in the company? Do you give them all a bonus? Do you declare that there was no contest that month?

2. What if you have fewer than three sales representatives in the company? Do you give them all a bonus? Do you declare that there was no contest that month? This case is slightly different from the first one because you can never get a subset of the required size from the original set.

3. What do you do about ties? Do you need three clear winners, without any ties, for the contest? If you have a unique first place winner but a tie for second, do you report only the first place winner?

Let's say that first place gets $100, second place gets $75, and third place gets $50 as a bonus. Now Tom, Dick, and Harriet all tied for first place with identical sales amounts. If you split the $100 three ways, they each get $33.33; an amount less than for third place.

4) What if you have missing data for some sales representatives? How do you handle NULLs in this contest? Do you drop them from consideration or hold up the contest until they get their reports into the database? Do you use other figures for them so they are in the contest?

People who specify this little contest have the unspoken premise in the back of their mind that sales representatives will sell a unique amount and report all their information in time for the contest. But no such one-to-one relationship is required to exist in the data.

Don't Be a Hammerhead

Another specification error is to assume an inappropriate method for a solution. To a man with only a hammer, so the proverb says, every problem looks like a nail.

I am used to seeing people write SQL with cursors in the same style that they would write procedural code. They think that a table replaces a sequential file and FETCH NEXT replaces READ. We went through this kind of coding when relational databases first appeared and Cobol programmers were trying to hook them into existing applications.

You would think that the advent of object-oriented (OO) programming would have prevented this problem. Instead, it only mutated the situation.

One extreme example of this problem occurs when programmers want to pass a table name as a parameter to a stored procedure but can't figure out how to do it in their SQL product. After posting the request to a newsgroup, a programmer may receive information on dynamic SQL and how to use procedural control structures to pick one of several different queries with the body of the stored procedure.

But why would you want to pass a table name as a parameter to a stored procedure in the first place? A table represents a set of one kind of entity or it represents a relationship among entities. A general-purpose procedure that takes a table as a parameter would have to do something meaningful to apples, oranges, polar coordinates, parts explosions, and anything else that someone adds to the database model in the future. Wow, that is some magical procedure! The specification is too general to be useful, so it must be narrower than you are first told.

You next find out that the stored procedure must work on any table with a name of the form shopping_cart_<number>, where number is a string of digits. The code will create a new table for every shopping cart that the Web-site application uses. When the shopper is finished with the cart, the application drops the table. The trouble is the programmer thinks that SQL is an OO language and has no idea how much it takes to CREATE and drop tables on the fly. The programmer wants a class-level function.

A better model would be to have a table for all shopping carts in which the cart identifier is a column rather than part of the table name. Updating and deleting rows in a table is much cheaper than modifying the whole table.

In other words, you can save yourself a lot of effort by fixing an incomplete or wrong mental model before you start.

Rate This Article Rating: 1 (best) 2 3 4 5 (worst) Comments: Optional e-mail address: