Andy Kramek

Our DevTeach Experience Follow-Up

andykr — Sun, 21 Jun 2009 11:34:00 GMT

I have one more point to add in respect of our Vancouver DevTeach experience in June of this year. If you read my blog last week you will know that I had a complaint about the lack of conference materials in general and specifically that:

There was also no conference CD (though I did get an Email telling me that session materials would be downloadable from 6/18)

Well, I just downloaded the session materials for the sessions I wanted and I am seriously angry - for several reasons:

There is no composite download. You have to go to the web site and download each sessions's materials individually. There are not even files by track - only by session. What a waste of time!
Even the session file(s) are not consistent - some have 1 file, some have a dozen. Why couldn't the presenters have zipped their material into 1 file per session?
On some sessions, when you try to download the file you get a "Page Not Found" error, other sessions simply have a "No materials available" message
When you do get something it is, by and large, useless. The vast majority of "Session Materials" turn out to be just the PowerPoint Slides! The few that have anything more are simply unannotated demo files
I spent more than an hour painstakingly downloading over 50 files, one a time, in the hope of finally getting some value - but all I have gotten are a bunch of PowerPoint slides and the odd bit of sample code

There are NO white papers! Not one! Without a white paper a session is pretty much a waste of time.

What I mean is that at the conference you attend 15+ sessions in three days - how can anyone possibly remember all the details? You NEED the white papers so that later, when you come back to review the material, or look up the details of some vaguely remembered speaker's comment, you have some chance of finding it!

In all my years as a conference speaker I have never given a session that did not include a background paper that could be used by attendees to review the session contents and give meaning and context in retrospect. In fact I have never heard of a conference where the submission of a white paper was not part of the requirements for speakers (and I even have known cases where failure to submit a paper meant you didn't speak at all and that you were dropped from the list!)

How can a conference organization be so appallingly dismissive of their attendees?

I really feel we were ripped off from A-Z by this conference and I have already said that we would probably not attend another DevTeach - now I am sure we won't.

In fact, nothing on earth would persuade me to attend another DevTeach and unless you especially like to waste money, I strongly urge you to try another conference where, perhaps, your money and time are appreciated sufficiently by the organizers that they will make a token effort to ensure that you get reasonable value.

The one thing this has experience has taught me is that DevTeach does NOT deliver value by any reasonable measure.

Creating Data Driven Pop-Up menus in VFP

andykr — Sat, 20 Jun 2009 14:38:00 GMT

One of the little tools that Marcia and I use all the time when working in VFP is a pop-up menu generator that allows us to select a development environment easily and quickly. Yes, we know all about the Task Pane but personally I have always found that a real pain (pardon the pun) to use. All we wanted was a quick and easy way to be able to switch between my various environments.

It seemed to us that the easiest way to do that is to have a pop-up menu that could be invoked from a hot key. Then we realized that there are all sorts of situations in which it would be useful to have a pop-up menu (like right-click options on forms and controls) and so we came up with a simple menu generator to build a pop-up menu on the fly from simple tables. In order to make this generic we set up a relational structure using three tables as shown in Table 1.

These tables are all contained in their own DBC named (imaginateively) "PopMenu". So how do we use the tables?

Well, first we create the records in the names table, currently I have two menus defined, one named "Projects" and another named "EditOptions". The first is the one that I run when I want to select a project environment, and the second is a generic editing options popup. Of course there is no real limit to the number of menus you can define but for the purposes of this article let's stick to these two.

Now we need to define our menu bars for these options and this is really simple. The first thing we need is a horizontal menu divider (grouped options always look better) so that gets defined as the first bar #1 as follows:

cBarText = "\-"
cBarDesc = "Divider Line"

Nothing else is needed for this entry. Now we need a bar for each option that we want to include, but for these bars we also need to include the action (and Skip For commands if needed). For my default development environment I use the following:

cBarText     = "\-"
cBarDesc     = "Divider Line"
mBarAction = " CLOSE ALL
                       CLEAR
                       SET PATH TO (HOME() + ";D:\VFP90\RUN\;LIBS;FORMS;DATA;PROGS;UTILS;BMPS" )
                       SET DEFAULT TO D:\VFP90\RUN\"

And for the "Paste" option on the EditOptions menu we need to include a SKIP FOR condition too:

cBarText     = "Paste"
cBarDesc     = "Paste From Clipboard"
mBarAction = "SYS(1500, '_MED_PASTE', '_MEDIT')"
mSkipFor     = "EMPTY( _ClipText )"

The code to generate the tables is in the attached zip file as GenBars.prg. You can use this file as the template to set up your own version of this little utility, and Figure 1 shows how the data looks for the result:

Now all we need is the code to use this. We created this as a class that does all the work in its INIT() event so that it runs itself when called. By returning .F. on completion of the code we prevent the object from actually being instantiated so that there is no lasting impact on the system at run time. The class is based on the session base class and this means that it creates a transient data session of its own – again, to avoid any environmental impact.

However, this also means that the class cannot be defined visually and so, if you really wanted to have this object defined as a visual class you could have to use either a Toolbar, Form or FormSet – these being the three visual classes that can create a Private Datasession. (Note: Of the three, the formset is actually the one with the smallest memory footprint – so this may be the only time you might actually use a formset in VFP. Personally I don't care if the definition is visual or not and so I just use the Session base class).

The Init() method is very simple indeed, as follows:

PROCEDURE INIT( tcMenuName )
LOCAL lcScript
*** Have we got this menu definition
IF This.GetMenuDef( tcMenuName )
lcScript = This.BuildMenu( tcMenuName )
EXECSCRIPT( lcScript )
ENDIF
RETURN .F.
ENDPROC

If the passed in menu name is found by GetMenuDef(), the BuildMenu() method is called. This creates a temporary MPR file using the data from the metadata tables and the file is then executed to display the menu. O)n completion of the menu action, the method returns false preventing the object from actually instantiating.

The GetMenuDef() method simply executes a SQL query, creating a cursor that contains the relevant data to generate the required menu:

LOCAL lcMenuName
lcMenuName = UPPER( ALLTRIM( tcMenuName ))
*** Populate the cursor
SELECT PB.cbartext, PB.mbaraction, PB.mbarskip, PL.ilnkseq ;
   FROM popnames PN, popbars PB, poplink PL ;
WHERE PB.ibarpk = PL.ilnkbarfk ;
      AND PL.ilnknamfk = PN.imenupk ;
      AND UPPER( PN.cmenuname ) = lcMenuName ;
      AND NOT DELETED( 'poplink' ) ;
     INTO CURSOR curMenu ;
   ORDER BY PL.ilnkseq
*** Did we get anything?
RETURN (_TALLY > 0)

This cursor is then used by the subordinate methods (GetBars() and GetActions()) that are called by the BuildMenu() method. Here is the script generated for the EditOptions pop-up and, as you can see it is a perfectly standard MPR file that uses ExecScript in the ON SELECTION clause to execute whatever action has been defined:

DEFINE POPUP editoptions SHORTCUT RELATIVE FROM MROW(),MCOL()
DEFINE BAR 1 OF editoptions PROMPT [Copy]
DEFINE BAR 2 OF editoptions PROMPT [\-]
DEFINE BAR 3 OF editoptions PROMPT [Paste]SKIP FOR EMPTY( _ClipText )
DEFINE BAR 4 OF editoptions PROMPT [\-]
DEFINE BAR 5 OF editoptions PROMPT [Cut]
ON SELECTION BAR 1 OF editoptions EXECSCRIPT( [SYS(1500, '_MED_COPY', '_MEDIT')])
ON SELECTION BAR 3 OF editoptions EXECSCRIPT( [SYS(1500, '_MED_PASTE', '_MEDIT')])
ON SELECTION BAR 5 OF editoptions EXECSCRIPT( [SYS(1500, '_MED_CUT', '_MEDIT')])
ACTIVATE POPUP editoptions

So how do we use this class? The PopMenu.prg is in my VFP root directory and the class name is "xMenuPop". My VFP startup program, called from config.fpw, includes the following two lines:

*** Set Projects menu Hotkey and Run the Projects menu
ON KEY LABEL CTRL+F12 NEWOBJECT( 'xMenuPop', 'D:\vfp90\popmenu.prg', NULL, 'Projects' )
NEWOBJECT( 'xMenuPop', 'D:\vfp90\popmenu.prg', NULL, 'startup' )

The first assigns the projects menu to my CTRL+F12 key, and the second, which is the last line in startup program, runs it immediately. Similarly to use the EditOptions menu we simply add one line of code to the right-click of any control that we want to invoke the menu from:

NEWOBJECT( 'xMenuPop', 'D:\vfp90\popmenu.prg', NULL, 'EditOptions' )

The class definition, the DBC, tables and the sample data generation program are all included in the downloadable zip file attached to this blog, I hope you find it as useful as we do.

Our Vancouver Devteach Experience

andykr — Sat, 13 Jun 2009 11:39:00 GMT

You may have noticed the blog has been quiet for a couple of weekends. This is because Marcia and I have been out of town to attend the DevTeach conference in Vancouver. As regular readers of this blog will know, I have for many years extolled the virtues of conference attendance and encouraged people to ‘put their money where their mouths are’ and attend conferences.

On occasion it has been suggested that this was an easy position for me to take because I have been, for more than ten years, a speaker at conferences and haven’t actually had to put my hand in my own pocket to attend. The fallacy with that argument is, of course, that preparing a conference session takes many hours of effort – it’s not just a case of throwing a few slides together and dummying up a few code samples, there's a paper to be written to support it all, and many hours of rehearsal review and rework. My own estimate is that a typical 75 minute session requires 80 hours of work, and a 4 hour “pre-con” can take several weeks! So while there may be no direct cash payment, the cost in terms of lost time (and income) can be considerable.

What is the point of this? Well, as you may know Marcia and I decided last year that we were not going to speak at conferences any more and that SW Fox in October 2008 would be the last time we would submit sessions. As it happens, Marcia was not selected for that conference anyway – but either way it was our last as speakers. So this year we decided to attend DevTeach in Vancouver. We might have chosen the Montreal venue last December (it’s a lot closer for us) but we were in England visiting family and so couldn’t make it. Besides, neither of us had ever been to British Columbia and so we decided to add a couple of days up front of the conference and have a little vacation time in Vancouver too.

Why DevTeach? Well, while Marcia does have some existing clients still in VFP, all her new work is in .NET and I have been working almost exclusively in SQL Server for several years now anyway. Since admission to DevTeach (primarily .Net focused) also included admission to SQLTeach (SQL server focused) it was an obvious choice. Not cheap mind you, but obvious. It was also our first non-FoxPro conference for several years. The cash investment alone (fees, flights and hotel), for the two of us, ran close to $5,000.00 – not an inconsiderable sum in these tough economic times.

So, how did we fare? Well, I have to be honest and say that I did not really enjoy the experience. In the sessions that I attended, there were some good speakers who gave solid presentations and imparted a lot of good information. A definite plus there! However there were also some truly appalling speakers who, in my opinion should not have been allowed in front of a paying audience. I am not saying that they were not knowledgeable, or that their topics were uninteresting, but their presentation technique was so bad that it was painful.

No names, no pack drill, but I actually walked out of one session (from the front row where I had been sitting). The speaker began 15 minutes late (he kept waiting [hopefully] for ‘stragglers’), then spent the next 10 minutes on his personal life history and eventually began talking without agenda, structure or plan. After another five minutes I realized that I had no clue what he was talking about and, much worse, felt that he did not know either. What was worse he actually asked me later how I thought he had done!

OK, so not everyone is a natural public speaker in the Jim Booth, Ted Roche or (putting false modesty aside) even Andy Kramek, mode. However, there are simple rules that, when followed, will allow anyone to give a competent and respectable presentation and what I am talking about is people who failed to follow these basic rules. This, to my mind shows a cavalier disregard for their audience who have, after all, paid good money, and also invested their time to come and hear them.

So there were a couple of sessions that I thought were bad. Why am I whining, have I never seen a bad session before? Of course I have (and to be honest, I’ve probably given some in my time). That isn’t why I didn’t enjoy the experience. Taken overall, the technical content (from my SQL-oriented perspective) was at least ‘Good’ and verging on ‘Above Average’. But, as I have so often said, the technical content of the sessions is actually the least important part of any conference.

What is more important is the ability to mingle with like-minded people who share common problems and issues and exchange knowledge and views – i.e. Networking. This is the aspect in which I felt that DevTeach failed miserably. The question is, why? There could be several reasons, and I am not really competent to assess them (after all I wasn’t involved in planning the conference).

The first is that it was unquestionably a very “local” conference. Most of the attendees seemed to be either from the Vancouver area, or to have relations in the Vancouver area. Consequently at the end of sessions they went home. Result – no-one was left in the hotel and there was no real opportunity to network – people were either in sessions, or simply not there.

Second, the hotel layout made it a poor conference venue. The session rooms were split between two floors. Registration, the .Net and related Tracks, the Trade Show and breakfast/coffee between sessions were all on the third floor, while the Keynote, SQL tracks and lunch were on the second. Doesn’t make for a good mixing environment! The only bar was “L” shaped (and quite small) and even in the lobby there was really nowhere to sit and chat (a couple of sofas was about it). Taken all in all it is hard to imagine a less suitable venue for encouraging people to get together in groups.

Third, there was no real conference material. Upon registration we were offered a tote bag and a two page conference schedule and that was the sum total of everything. There was no binder describing the hotel, environs (where should we go for dinner outside the hotel?) and conference facilities (general timings, location of meals, where to look for notices etc) and, initially, not even paper and pens (though they did appear on Day 2 I believe). There was also no conference CD (though I did get an Email telling me that session materials would be downloadable from 6/18 – a week AFTER the conference ended but that the speakers slide decks were available for download immediately). Not really the kind of supporting material I expected or am used to.

Fourth communications in general were weak, in fact, on reflection, they were nonexistent! For example, someone actually complained, in conversation with Marcia on the afternoon of Day 2, that there was ‘not even lunch’ provided. They didn’t realize that lunch was being served on the floor below – but then how would they? There was no information provided at registration, no signs, and no announcements – in sessions or anywhere else - as to what facilities were provided, or where they were located. Worse, there was no indication outside the rooms telling you what sessions were going being held. A simple chart for each room with Topic, Speaker and Time would have been very helpful. I ended up in the wrong room twice and had to do a hurried exit and re-entry when I realized that the topic being presented wasn't what I thought it was going to be. Little things, but…

Fifth, the Keynote. Ah how we love those keynote presentations. In this case the keynote was a demo of Visual Studio Dot_Next. Of course this was of great interest (NOT!) to us SQL Server people. If you are going to combine two conferences, why not have two keynotes? After all the speaker was just one of the regular conference speakers – not an imported celebrity (as at Russ Swall’s “Essential…” conferences), or even an Industry (i.e. Microsoft) expert as at the German Devcon or SW Fox. Couldn't a SQL Server speaker have given a SQL Keynote?

Sixth, out of session activities. This was the last nail in the coffin for me – there were none! Oh yes, the Vancouver IT community hosted a ‘free beer’ party on the Monday night. Unfortunately it was not in the Conference Hotel, nor even close, and unless you were a local (oh, hang on, most attendees were!) it was not an easy task to figure it all out. The only information was in an Email – no announcements, no organized travel (how about sign-up sheets for organizing groups to share Taxi costs….?), no information or directions on how to get there at the conference desk (which was unmanned most of the time anyway). But then this was nothing to do with the conference, and was, obviously, aimed at the local residents.

Apart from this there was nothing. No “mixers”, no “Show and Tell” evening sessions, no “mitt bier” evening sessions, just an empty hotel bar and lobby. For the first time in more than 10 years attending conferences I was back in my room by 8:00pm every night.

Seventh, there was no passion. I attended 15 sessions over three days and I didn’t see one where I felt that the speaker was really passionate about their topic. For those of you who have ever seen Cathy Poutney, or Doug Hennig, or Jim Booth, Marcia or I (to name but a few) give a session, you will know what I mean when I talk about speakers with passion. The impression I had was that, for the DevTeach speakers, it was ‘just another day at the office’. Most of them made it clear that the sessions were not new and the feeling I got was that it was all a bit of a chore for them.

Finally, one of the things that I have always hated was there, in spades! This conference, more than any other I can remember, suffered from the “Inaccessible Speaker” syndrome. I do not recall, in three days, seeing any speaker outside of a session room – unless they were traveling in packs as when waiting to go out to their speaker dinner.

Trying to go and speak to a speaker is, for most attendees, pretty intimidating. When you have to interrupt a group of speakers who are interacting with each other it is positively frightening. At lunch, what attendee is going to go and sit down at a table of 6 speakers earnestly engaged in high level discussions of (presumably) great weight and import? The problem was compounded at DevTeach because most of the speakers didn’t wear their name tags outside of sessions (so unless you already know them you don’t even know that they are speakers) and so the only the time the speakers were really visible was in sessions.

I know that Marcia and I (and the vast majority of the VFP Speakers, encouraged strongly, and in some cases even required, by VFP Conference Organizers) always made a conscious effort to be approachable, and accessible to attendees. We deliberately tried not to sit at “speaker tables” at lunch, and to ensure that we were always in the bar/lounge after sessions (an easy task for us ). Alas that culture does not seem to have carried over to DevTeach despite its original roots in the VFP community.

My conclusion? Well, as I said, technically the conference was adequate. Most presentations were competent, some were good and only a few were really poor. I did learn stuff; some of it will be directly useful to me, some is potentially useful and some just improved my general background knowledge and understanding. I have no complaints in this respect.

However, when you factor in the ancillary costs (travel time, lost work time, flight, accommodation and meal costs) the return on investment was very poor indeed. So while for a local resident it was probably cheap training, for us it was a very expensive, unstructured, training course with only moderate value. I left knowing no-one whom I did not know before I arrived (though it was certainly nice to see a few familiar faces among the speakers) and with the total number of business cards in my case the same as on the first day.

That was most the disappointing thing. The reality is that the whole conference felt like a commercial training course (where you sit in the classroom with a bunch of people you don’t know and with whom you have no real opportunity for communication and, at the end of the day, you all go your separate ways). From that perspective the training course is actually better value – at least there is a consistent and clear learning program.

Of course, this is just my opinion and others who were there may disagree with me but, as my readers will already know, I always try to tell it like I see it and this is how I saw it. I still believe that conferences can be great value, I just don’t think that DevTeach Vancouver was one of them.

So will we go to another DevTeach? Probably not. To another conference? Probably (right now I am not sure when or where, but I expect that we will) because despite this poor experience I still believe that GOOD conferences amply repay the investment you make in them.

The Cost/Time/Content Triangle

andykr — Sat, 30 May 2009 14:36:00 GMT

The Cost/Time/Content Triangle is a simple way of representing the rather complex (not to say confusing) interaction between the three key components of any IT project - Cost, Time and Scope. It provides a mechanism for visualising the effect of changes in any parameter and provides managers with a tool for quickly assessing the impact (and hence the risk) of changes to any one component on the others.

The Cost/Time/Content Triangle is a graphical triangle whose three sides each represent a measure for an element of the project. By setting an appropriate scale for each side we can define the limits for the three key elements of any project. This is probably easier followed with an example, so let's consider a simple project that, based on the defined scope (the 'Content' element) has been estimated as comprising 4 key parts totaling 20 weeks of work as follows:

Main Data Screens 12 Weeks (55% of the Content)
System Admin Functions 3 Weeks (15% of the Content)
Standard Reports 4 Weeks (25% of the Content)
User Definable Configuration 1 Weeks ( 5% of the Content)

The percentages refer to the contribution each part makes to the whole project. Having got the Content, and Time elements we can calculate the Cost using our standard formula which, in this example is very simplistic and based on a standard Development Cost of $25 per man-hour + 25%, giving us a total cost of $25k:

20 * 40 hrs = 800hrs @ $25 = $20,000 + 25% = $25,000

(Note: this is the estimated internal cost of undertaking the project, not the price we intend to charge to the customer; that is a wholly different algorithm )

We can now construct our initial Cost/Time/Content Triangle - using these values to generate the scales. The objective is to get a balance so we want to get our initial estimate centered in the triangle. As you will recall from your Geometry classes you find the center of a triangle by drawing a line from each vertex to the mid-point of the opposing side. Where the three lines intersect is the center. Where each baseline intersects the opposite side of the triangle we set the appropriate estimated value; i.e. Cost = £25k, Time = 20 Weeks and Scope = 100% as shown at Figure 1.

Scope is, of course, the main driving force behind any project and in overall, will largely dictate both the project time scale, and the cost. After all, the more you have to do, the longer it will take, and the more it will cost! However, not all elements of a project scope will have equal weight, and invariably the actual scope consists of two parts, the 'Must Be Done list' and the 'WIBNIF (Wouldn't It Be Nice If…) list". The key to assessing scope is to determine what percentage of the project each element will account for.

Note that, thus far, our Content and Time scales are directly related (10% of the content = 10% of the time) which is what we would expect since our time estimate is based directly on the content. Cost is also directly related to content at this point because it too is derived solely from the content estimate and all we have considered to date is the "must-do" components. In fact, there were (as there always are) a couple WIBNIFs that we estimated as follows:

Ad-Hoc Query Screen 2 Weeks
Ad-Hoc Report Generator 3 Weeks

Moreover our internal assessment is that both the "User Definable Configuration" and the "Standard Reports" are really WIBNIFs too (the basic project could be done as "Phase 1" with just Screens and Admin functions) . So our project scope could drop as low as 70% of the base estimate, or go as high as 120%. These are, therefore the limits for our "Content" scale, which we define as a linear scale. Similarly our time, based on this content, could be as short as 14 weeks, and go out to as much as 25 weeks and we should add an appropriate time scale that covers this range, with some additional margin at each end (we'll see why later). Draw lines from the Content and Time vertices to each point on the relevant scale - this gives us the CTC Triangle shown in Figure 2:

To complete the triangle we need to add the Cost scale. To get this we need to calculate the cost of the project given various alternate elapsed time scales – remember that we have based the estimate on the defined scope. Changing the scope will give us a different cost for a given time scale. Table 1 shows how this works:

and using this as the basis for our time scale we get the final triangle (Figure 3):

All of our estimates relate to the original estimated scope and a time scale of 20 weeks, so now we can assess the impact of changes by simply reading off the appropriate scale. To ascertain the cost of doing the project in only 15 weeks, we do the following:

Draw a line from the Cost/Content vertex to the 15 Week point on the Time axis
Draw a line from the Time/Content vertex, through the intersection between our new line and the 100% Scope line and extend it to the cost axis to get the revised cost

The value is about £30K, which equates to £2k per week and is equivalent to 24 weeks at the standard rate - doing things quicker always costs more!

Similarly, we can estimate the reduction in cost if the project can be spread over a longer period. For example if we took 24 weeks for the project it cost only £22.5k. Although this equates to reducing our weekly rate to about $940, we still only have 20 weeks worth of work to do and (hopefully) would be able to employ our developer gainfully elsewhere to make up the difference.

Now what about those WIBNIFs? If there are no other changes in the original scope, but our client also wants the Report Generator (estimated at 3 weeks work – or an extra 15% on the original scope) we can immediately see our options:

To preserve the original cost, the project will have to take 6 weeks longer, reducing our cost to $960 per week, but gaining an additional 3 weeks of time in which to carry out the extra work
To deliver on the original time scale, the cost will have to go up to just over $31,000. Not only is this increasing the amount of work to fit in, but it is also increasing our risk of failure
Propose a compromise. Increase the project time line by 4 weeks, and the cost to $28,000. This gives the a cost for the report generator of $3000 but gains us an extra week over estimate in which to do the additional work

In precisely the same way we can rapidly asses the effect of scope changes. If the decision is made that User configuration is not required after all, but that an Ad-Hoc Query screen is, the net result is to change the scope by +5%, the estimate can now be revised as either:

Deliverable in the same time at an extra cost of £1.5k
Delivered for the same cost, 2 weeks later

The triangle is really useful when you need, as in the example above, to cope with changes in scope (of the upward variety) at short notice and need to be sure that you can not only cover the costs, but can explain to the client why it is going to cost so much more. However, as you will no doubt have realized by now, the key to the triangle is the initial setting of the scales. This is only as good as the information which you use to generate your estimates, and the relationship which you determine between cost and time.

Typically the cost relationship is not as simple as shown in this example – if we really wanted to increase the scope by 15% and deliver in the original time scale our poor developer would be working over 60 hours per week for five months (not a good scenario!). The reality is that we would need additional resources and that will change the cost relationship. Adding a developer is not simply a question of increasing the hourly billing rate, it also increases the fixed costs; each developer needs equipment, Employment, Administration and Management Overhead costs all increase too. Similarly if the workload falls below 40 hours a week it is not always possible to re-deploy the resource without losing income.

The reality is that the relationship between cost and time is typically a stepped relation, rather than linear. In fact, getting this relationship right is one of the hardest parts of any project estimate, but once you have it, the CTC becomes a powerful tool for assessing risk and managing change.

Finding the length of a string in a specific font

andykr — Mon, 18 May 2009 21:49:00 GMT

One of the things that I often found myself doing when working in VFP was trying to decide how large a textbox had to be in order to accommodate the maximum length of the data that was permitted for its underlying source given a specific font and size. As we all know, unless you stick to non-proportional fonts, the number of pixels required to display a string can vary dramatically depending on the font.

For example, the string "Andy Kramek" actually requires 88 pixels when displayed in Courier New, 10 point font. However, change the font to Arial and you need only 79, unless you make it Bold in which case you need 85. But if you change the font to Verdana then you need 85 for the plain text and 96 for Bold.

Given the multiplicity of fonts available you could be forgiven for thinking that determining exactly how much room to allow would be a fairly normal thing to want to do and therefore could reasonably expect VFP would have native function that would tell you. Unfortunately, it doesn't. Now, at this point you may be thinking that this isn't really very important. After all, VFP sizes text boxes automatically using the underlying field definition when you drag a table field from the data environment, or project manager, on to a form.

Unfortunately the native sizing algorithm is not very accurate! For example, dragging a column defined as VARCHAR(50) to a form creates a textbox 357 pixels wide when the font is defined as Arial 9pt. However, the actual size required for a character string of 50 characters (using the average character width for this font) is only about 250 pixels! Even worse, the sizing has nothing to do with the font defined for the form and, even when you change the form's font in the underlying class, VFP simply carries on sizing controls for its default (Arial 9) font. Now that is a lot of wasted space on a form and, even worse, can be confusing to users because the size of the textbox is not a very reliable guide to the amount of text that it can hold.

We also have to consider the situation in which we need to concatenate data which is stored in individual fields together (to display a ‘full’ name for example, or a ‘City, State and Zip’ line for an address). VFP cannot help us here, and so the normal solution is to decide, empirically, how big to make the textbox. There are actually two issues involved here. The first is to determine the number of characters which we want the textbox to be able to handle and the second is to work out the amount of space that number of characters will need given a specific font setting.

Concatenating Fields

The default behavior of VFP when concatenating fields is to simply add together the defined lengths and create a new field whose width matches the total. Consider a set of name fields – let’s assume ‘FirstName’ is set up as 25 characters and ‘LastName’ as 40. Now VFP will always assume the worst case scenario when concatenating these fields and will generate a result field with a width of 65 characters (just run a SQL select that combines two character columns and you will see that this is indeed the case).

However, this does not represent the reality of the situation. Using the sample data that ships with VFP, we find that the average length of first names is actually 5.85 characters, and for last names it is only 6.83. So really we could use a textbox capable of showing, say, 25 characters and be confident that we would handle the vast majority of cases. While it is impossible to be prescriptive about this sort of concatenation it is easy enough to set a target size that reflects the typical results.

So the number of characters is defined either by the underlying data source directly, or by some reasonable guess based on the required display.

But what about the font?

While we may be able to determine how many characters we want to display easily enough, as we have already seen, the size of the textbox that we need will depend upon the chosen font. In fact it is not only a question of the size of the font, but also of the style (e.g. Bold or Italic) and the font face. Even different fixed pitch fonts vary in their space requirement for individual characters.

Fortunately VFP does have functions, or more accurately a single, heavily overloaded, function to get this information. FONTMETRIC() can access twenty – yes, that’s right, 20 – different font attributes depending on the input parameter. I find it hard to remember the calling options for even one or two of these and even though IntelliSense makes it a little easier we still need to retrieve individual attributes one at a time. Since each returns the value for a single character, the whole process gets tedious, not to say messy, when you are trying to retrieve several attributes for the same string.

The SizeStr() Function

The solution is to create a simple wrapper function to determine the elements of the sizing that we are interested in. While we could grab all 20 attributes, in practice there are really only three things we are interested in. The maximum length that the specified string could possibly require, the average and the exact lengths of the specified string. With this information we can make a sensible decision based on how typical our test string is, and how much variance we need to allow for in sizing our textbox. The SizeStr() function displays the results in a little modeless form (Figure 1) and returns the exact length of the specified string in the specified font.

Figure 1: The SizeStr() Result Screen (the function actually returns the Exact Width)

The exact length of a string is actually calculated as a two step process. First we use the Visual FoxPro TxtWidth() function to determine the number of “Average Character Equivalents” in the test string when the specified font is taken into account. This calculated value allows us to treat proportional fonts as if they were actually fixed and so simplify the ensuing calculation. Thus, while a string containing 5 letters will always return 5 with a fixed pitch font, a proportional font will give different results depending on both the font itself, and any additional styles that have been defined. Table 1 shows the results from TxtWidth() for the string “This is a Test String”.

Having determined how many average character equivalents we have in our text string we can simply multiply this number by the Average Character size (FontMetric(6)) to get the exact size which is returned by the function. To get the Maximum size for a string containing the specified number of characters we then multiply by the Maximum Character Size (FontMetric(7)). Finally we multiply the Average Character size by the actual number of characters in the test string to determine the average length of the string.

But what about the height?

Unfortunately (again) VFP does not help us to decide how high a textbox should be when we change its font. FontMetric() will give us the actual height of the character in a given font, but we also need to allow for border height when sizing a textbox. In SizeStr() I do this by adding a factor of 28.125% of the character height in the specified font. This value has no theoretical basis, but, by inspection appears to be about right! The resulting value is shown on the display form.

The function is attached to this article and can be downloaded below. It accepts the following parameters:

tuInStr [Required] The input string to be tested
tcFName [Optional (defaults to "Arial")] The name of the Font
tnFSize [Optional (defaults to 9 point)] The font size (in points)
tcFStyle [Optional] Font Style codes (Bold, Italic, Underline etc)

The function pops up the modeless window shown at Figure 1, and when the window is closed returns the exact length of the input string in pixels. However, the parameters displayed in the form are all saved to an object in the function and it is simple to suppress the form and return the object directly.

Example

lnLen = SizeStr( “This is a test string”, “Garamond”, 12, “BI” )
? lnLen && Returns 131

Hopefully you will find this little function as useful as I do.

Modeling Lookup Tables

andykr — Sun, 10 May 2009 10:59:00 GMT

One topic that caught my eye on the Foxite forum recently was a discussion of how to implement Lookup Tables. Like many such questions it provoked strong opinions and definitive statements of the "Thou shalt ...." type (i.e. the type of "dogmatic logic" based on opinion, instead of reasoned argument). The question centers on whether it is better to use a single table for all lookup data (the so-called "One True Lookup Table [OTLT] approach) or to have separate tables for each different set of lookup data that is required. As with many such data The usually quoted conventional wisdom is that:

Developers like the idea of a single lookup table, but Database Administrators do not

and the reason why developers like them is that they simplify the creation of maintenance screens and allow generic code to be written to handle the population of controls in the user interface. Conversely the reasons why DBAs do not like them are that (according to an entry in "Systems Engineering and RDBMS" at http://decipherinfosys.wordpress.com/2007/02/01/otlt-one-true-lookup-table/ )

This is an OO design pushed into the database tier and poses many problems including :

Not being able to force foreign keys (so your data integrity is compromised right there),
Not being able to use the right data-types since everything has to be a string in order to accomodate all data-type values (this itself can give rise to bad data issues since a numeric(4,2) constraint cannot be enforced –> thus domain integrity is out of the door,
Because of (b), the length for the string column will be huge,
One has to then write complex SQL queries in order to retrieve the data and also take into account the type conversions that occur when you join this table with the other tables…implicit conversion is not allowed in all RDBMS and is not a good thing anyways.

However, as all too often with such didactic statements, this is based on a couple of assumptions; first, that all lookup data must be contained within a single table and, second, that all "lookup data" is the same and must all be handled the same way. Now, if those assumptions are really correct, then there is no question that using a single table for all lookup data is a bad idea (for all the reasons given). However, are they really valid?

The key to understanding this issue lies in one of the most basic principles of good database design - which is that "a database must model the business process that it supports". When viewed from this perspective it turns out that the assumptions on which the argument is based are incorrect. What we are taking about here is what I defined in my article "Designing A Database – Part 2" (see November 2008 on this web log) as Support Data. In that article I said the following:

Although support data cannot be derived from other data and therefore requires permanent storage, it differs from core data in that it is does not form part of the body of data upon which business applications operate directly. This includes all data that is used define how the system behaves (i.e. metadata and preference data), or how the data actually stored in the system is interpreted (i.e. look-up and reference data). The support data is a key element in the database in that it is primarily concerned with the translation of data into information – and this applies equally to metadata as it does to look-up or reference data.

Notice the highlighted section. The rules of data normalization require that we use Foreign Keys to reference values that would otherwise be repeated in a table and such values are usually referred to as "lookups" leading to the idea that they are all equivalent in some fashion. In reality that is not the case and there, as indicated, TWO types of data that have to be dealt with - "lookup" and "reference" data. In the previous article I did not expand on the distinction because it was not really relevant in that context. Let me do so now, because this distinction is, in my opinion, the key to the whole OTLT issue.

Lookup Data should, as the name implies, consist only of things that can be "looked up" or, to put it another way, consist of only one piece of significant information. So for a lookup, the foreign key directly substitutes for a denormalized value. Conversely, "Reference Data" may comprise many pieces of related information and a the foreign key is used to retrieve the set of information that is applicable. You may be thinking, about now, that I am making an artificial distinction, but believe me, I am not. Table 1 shows a few examples of "lookup keys" and how they map to the different categories.

Notice that for all keys that fall into the lookup category there is always just one piece of information – a description, and nothing else. While in the reference data category there is always more than one piece of information associated with the key. I can't speak for anyone else but myself, but to me it is obvious that there is no way that storing "Customer Credit Terms" in the same data structure as "Units of Measure" would make any sense.

So the assumption that all lookup data is somehow the same is false to start with. For anything that falls into the Reference Data category, an appropriately designed table should always be used and I absolutely concur with the premise that doing anything else is poor design.

But what about the Lookup Data category? The fact is that all of this data is actually 'enumeration' data and that is probably a better term to use than "lookup" for this type of data. Reduced to basics it comes down to a Key/Value pair. Even so, if we were to use a single table for all such data we do run into the issues raised by opponents of the OTLT approach. Specifically, keys become difficult to define and either we have to enforce uniqueness on the "lookup key" or we have to introduce a 'Type' column to create compound key (i.e. Type + Value). Neither option is attractive and it would indeed complicate the SQL required if we had to adopt such a solution.

But hang on! We are talking about relational databases here (we wouldn't have foreign keys if we weren't)! So why are we constrained to use a SINGLE table? The short answer is that we are not and, for enumerations (i.e. that consisting only of code and description) we can use a PAIR of tables. The structure that I like to use is shown in Table 2.

Note that all of the issues disappear if we adopt this approach. Each table has its own (surrogate) primary key, and the relation between Type (header) and Enumeration (detail) is maintained by the foreign key in the detail table. There is no need to artificially restrict the key values in the detail table and a simple SQL inner join returns the appropriate set of enumerations. If there are specific sets of data that are used often by applications, we can define views for them and create either stored procedures, or functions, to handle specific requirements. The added columns on the detail table are for the convenience of the UI developer. The "Active" flag is used to indicate whether the value should be made available in the UI, and the "Default" flag indicates which value (if any) in the set should be used as the default in the User Interface. They do not have any significance, or impact, at the database level.

So my position is that reference data requires its own tables, but enumerations can be handled using a header-detail pair of tables. This does, however, raise another question. Should enumerations even be stored in the database? After all this data can be handled directly in code if all it is doing is providing expansion of key values to their associated description. The short answer is that they most definitely should be, and there are several reasons.

If enumerations are defined only in application code, then adding a new item to the selection always means recompiling, and re-distributing, the application. Admittedly if the new item requires special handling in some way, then its addition would still require code changes, but the addition of, say, a new contact type would probably not require any code change at all and can be handled by simply adding a row to a table and letting the code that accesses the enumeration tables return the new type.
Unless the enumeration is stored in the database, there is no way to decode the enumeration key values for reports or when exporting data to another application (e.g. an Excel spreadsheet for reporting or analysis) or to a data warehouse.
When multiple applications access the same database, there is not only the issue of ensuring that enumerations are available to each but also that of ensuring that they are synchronized and consistent. The scenario where one application interprets an enumeration key of 1234 as "Home Phone Number" while another defines it as "President" does not bear thinking about!

So whether we are talking about an On Line Transaction Processing, a Management Information System, or a Data Warehouse, we always have to cater for two distinct types of "lookup data". The enumerations that are used solely to normalize values that would otherwise have to be repeated and the reference data that is used to manage various aspects of the application. They are totally different entities and should be handled in different ways.

So is there a case for the "One True Lookup Table"? Not in the way that it is usually defined. The trick is not to confuse reference data with enumerations and not try and shoe-horn reference data that has different attributes into a single common structure. However, a pair of tables can certainly be a much more efficient way to handle enumerations than having lots of separate, identical tables, each of which contains only a few static rows.

For example a CRM application I worked on recently has 40 rows in its lookup header table (i.e. we would have required 40 tables if each type were handled separately) and a total of 303 entries in the detail table (there are 50 "States" and 42 "Providers", so the average number of 'rows' per type is only about 7). Can forty, identical, seven row tables really be more efficient than the header-detail pair? Hardly!

Working with Vertical Tables

andykr — Sun, 03 May 2009 13:18:00 GMT

What is a "Vertical Table"?
The short answer is that a vertical table is one which does not store data elements in separate columns. Instead data is stored in rows, with each row containing a description of what is being stored (the “attribute”) and the actual value. In a normal table, the requirement to capture an additional attribute means adding an additional column to the table. The result is that the table gets wider (i.e. grows horizontally) as new attributes are added.

In the case of a vertical table, each row contains both the description of the attribute and its value. The result is that as additional attributes are added to the table it acquires more records (i.e. grows vertically) but never gets any wider. At its simplest, a vertical table consists of only two columns, the attribute descriptor and its value, hence the alternate name for this type of table; an ‘attribute/value pair’ table.

Whilst vertical tables can be used in many scenarios, there are two key criteria that, when satisfied, indicate that a vertical table is actually required. The first is that the data being modeled is infinitely extensible. The second is that the data related to its owning entity is always accessed as a complete set. These criteria are directly related to the advantages (and limitations!) of vertical tables and so merit detailed attention.

The term "infinitely extensible data" is simply a fancy way of saying that there are no specific rules about the data in question. One of the most commonly encountered examples of infinitely extensible data is Postal Addresses. This may sound odd, but if you think about it, there are no hard-and-fast rules for how postal addresses are defined. For example the US postal address for an apartment might look something like this:

123 Crossover Street, Apt 12A
Akorn, OH 44123

While an equivalent type of address in the UK might be:

12A Cowsfoot House
234 Pastures Walk
London
SX1 3GG

Analysis shows that each actually contain the same basic set of attributes (Apartment Number, Street Address, City and Postal Code) but the way in which they are defined is totally different. Thus the UK postal code includes the County (more accurately it defines a unique "postal district" which means that the county is implicit) while the US zip code requires the State to be defined explicitly. So what is usually done is to create a table with columns for each attribute and screens that allow data for each column to be entered. The result is usually something like this:

Which, of course, is perfectly OK - as long as the table was not defined with purely US coding in mind (i.e. 2-letter state codes and 5/9 digit zip code) but there is obviously no standard for the way address details are handled and, in practice, very few addresses really require all of these columns anyway. Now consider the same addresses as they might be stored in a vertical table:

The first thing to notice, which is a benefit of this approach, is that we can now name our "columns" much more appropriately for each address. Second, we have no wasted space in the table. Each address consists of as many rows as it actually needs. However, there are some issues that do not arise in a more conventional table.

First, even though we know which rows belong to which owner, we have no way of knowing in which order they should be presented because we do not have standard column names that can be mapped to a UI or report. So we need an additional column for each set of attributes to define how the attributes should be interpreted.

Second, our UI (or report) needs to be able to dynamically assign the appropriate number of rows and their descriptions because, while the address for owner 123 has 5 attributes, owner 125 has only 4 while owner 124 has 6 attributes. More importantly the attributes that were used differ between addresses.

So, you may be wondering, why don't we all use vertical tables like this for handling addresses? The answer is twofold. First, that while addresses considered globally are infinitely extensible in any one context they are not. After all, all US addresses are handled the same way, as are all UK addresses. So in an application context there is no real need for this kind of extensibility, and even when there is, there are other ways to handle it.

The second reason is related to a major limitation of vertical tables, that is, in turn, based in the second criterion for requiring one; i.e. that the data is always accessed as a complete set by owner. What this means, in practice, is that vertical tables are really useful when you need to save and restore sets of data, but are less useful when you need to anyalze that data.

For example, with a vertical table it is easy enough to get the address for owner 124:

SELECT attribute, value
FROM address
WHERE owner_fk = 124
ORDER BY sequence

but it is difficult to construct parameterized queries on such tables because there are no defined columns by which to filter results. The only option is to search for specific “attribute = value” combinations.

Thus to find how many addresses we have in New York state the query would have to be written as:

SELECT COUNT( DISTINCT owner_fk )
FROM address
WHERE attribute = 'State'
AND value = 'NY'

And this, of course, assumes that all New York addresses were actually entered using the "State" attribute (and not a "County" or "Province" attribute). What is worse, grouped queries that are easily handled by conventional tables are even impossible (like totaling sales figures by State for example).

So, if not for addresses, then what should we use vertical tables for?

There are a myriad of possible uses but, in general, I have found that you cannot simply implement a vertical table in isolation. The schema required, if only to implement the UI, is generally much more complex.

Here is an example for handling Educational and Professional Qualifications. The problem with qualifications is that they truly are infinitely extensible, even within a single context. There are absolutely no "rules" and no standards that are applicable.

For example the "standard" abbreviation in the US for a nurse is "RN" (Registered Nurse). However, in the UK "RN" indicates "Royal Navy" and the standard nursing qualification is "SRN" (State Registered Nurse). The problem is not just international either, what qualification indicates a medical "Doctor"? In the US around 30 are recognized (including PhD - Doctor of Philosophy) and to make matters even worse, even within this set there are conflicts – DO could be either "Doctor of Osteopathy" or "Doctor of Optometry" while DPM is either a "Doctor of Pediatric Medicine", or a "Doctor of Podiatric Medicine" (better be sure and get the right one to treat your problem)!

The situation is even more complicated when you take into account the fact that there are at least three different, and unrelated, types of qualification that people hold:

Educational: These document your academic achievements and include things like your college degree and area of specialization. These are static because once you have gained an educational qualification, you have it for life
Professional: These document your competencies and include things like medical qualification boards and memberships of professional associations. These tend to be static, but can change, especially if there are various grades of attainment or membership
Certification: These document things that you are allowed to do and include things like practice licenses or specialist skills. What differentiates them from professional qualifications is that they expire and have to be renewed periodically

Thus we could have three completely different sets of information which all relate to a individual's qualifications and they require completely different sets of data. Educational qualifications typically include an awarding body, subject, date and grade. However, a time-based certification will also need an expiry date and a professional qualification may well need a Licensing Body and Number.

Obviously this data meets the "infinitely extensible" criterion. However, unlike addresses it also meets the second criteria because we would always want to retrieve the data in sets. For example, license numbers, in isolation, are meaningless because we would always need to know the context for a license (what it is for, who granted it, when it was awarded, when it expires and so on). Similarly the fact that someone has taken a C# programming course is not much use unless we also know the level for the course (Beginner? Advanced?), when and where it was taken and whether they passed or failed.

In order to model this we will need to use a vertical table to hold the actual data that relates to a qualification to but we will need additional tables to describe how that data is to be interpreted.

In order to support this, we also need to define attributes, and this too is a little more complex, requiring three tables as described below:

The full model for handling qualifications is shown at Figure 1 below:

Notice, however, that the attribute definition portion of the model is entirely generic and could be used defining any type of attributes. Consequently, for any data entity that needs attributes all you need is to define the attributes in this model and create the necessary "detail" table to link them to the owning entity. In fact, I usually define views to handle retrieving the attribute data so that the complexity of the model is hidden, thus to retrieve the generic set of attributes for an "address" I can query the view by name:
SELECT * FROM dbo.v_trans_attrib WHERE attgrp_nme = 'Address'
Returns:

Or I can use the attribute code directly and get the set of attributes for a Certification:
SELECT * FROM dbo.v_trans_attrib WHERE attgrp_cde = 'CERT'
Returns:

By defining these views so that they include all relevant keys and codes it is a simple matter to have the UI generate the necessary data to retrieve the attribute sets, their associated lookup values and data. Since every table has a surrogate primary key, the update code is also very simple (and generic).

OK, so vertical tables can work, but why bother? There are really two reasons. First, as I have tried to show, vertical tables can provide a flexible, and more importantly a generic, mechanism for handling totally disparate data that would otherwise require multiple tables with different columns. Second, and perhaps most important, vertical tables can handle changing data requirements without requiring changes to table structure. Adding a new attribute is merely a question of adding a new row.

They are not always applicable, but when you are dealing with data that is both extensible, and heavily set oriented, vertical tables offer a powerful and flexible way of handling the data. The additional planning and work involved in setting up a vertical table based model and creating the generic supporting mechanisms is, in my opinion anyway, amply repaid by the benefits achieved in terms of reduced maintenance and flexibility that they permit.