Make Mine MAPPER #16 -------------------- by Rob Haeuser Rapid Evolution: Quashing the "Clone Syndrome" ---------------------------------------------------------------- One minor drawback to working with MAPPER is that because you can get so much done so quickly, people begin to expect, even demand, almost inhumanly short development times. This happens to me occasionally, most recently with a system that we'll call Medical Transportation (Med-Trans). Actually, I guess the whole thing is at least partly my own fault. I was wearing my coordinator hat one day when wind of another classic case of the "clone syndrome" blew by. The Texas Department of Human Services (TDHS) has the state divided into ten regions: one of them had developed an application used in the transportation of certain TDHS clients to and from medical appointments. Other regions heard of it, obtaining copies that they then modified to their own needs. By the time the fifth region had cloned the application, I was suggesting that maybe it should be re-written into a "state-wide" system. Somehow this idea caught on. The part about me personally doing the code is a bit fuzzy, but that's how it turned out. I must have slipped on my run- designer hat (and a banana peel) somewhere along the way. Ok then, when in doubt, gather about. Let's have a meeting! Marathon meeting, that is. Bring ten people in from across the state, smash them into an 8 by 10 foot meeting closet, and then proceed to expose them for two days to your sneezing, coughing, flu-like symptoms. They loved it! Well, ok, I'm exaggerating, it was hay-fever, not the flu, and they kept getting pained looks on their faces like they were gonna catch something, but at least we got a lot accomplished. Actually, we had three meetings, each about a day and a half long, spread out over a few weeks in August. The first meeting was dedicated to identifying common data elements among the five existing systems, and adding any that would be required by a state- wide system. In addition, we discussed, in a general sense, just what the blasted system's supposed to do. The second meeting was initially spent reviewing all the data items and system concepts. Then we looked at reporting needs. The third meeting started with a review, followed by discussion on forms, screen layouts, field edits, and more on reports. After the third meeting, I had enough info to start designing the database and begin coding. So Med-Trans goes something like this (take a deep breath): a qualified client can set multiple appointments during a certain floating period of time with various facilities/clinics/doctors for treatment of a variety of approved medical needs, transported via possible multiple means of travel, including airlines, buses, cars, mass-transit, taxis, and, yes, even limousines. Clients can be picked up at a location other than their residence address. Their mailing address can be different from their residence or pick-up address. Their different appointments can be at different places, and they might get picked up at a different place for each appointment. Their forms can be sent to places different from any of the addresses already mentioned. If the forms don't get where they belong they may need to be re- printed. And then there are the transportation tickets... Tickets can be issued, which are to be tracked. It might be one ticket, or it might be a whole batch of tickets. And there might be multiple batches. They might be tokens, which aren't tickets, and therefore not tracked. Of course, there are forms to generate and track, except for the exceptions. Did I mention all the history data? Speaking of data, there sure is a lot of it. Dozens and dozens of little fields; addresses out the kazoo; 15 tables and counting. At last count, there were approximately 150 separate data items in the database. My estimate for the time required to develop this system was about four months (a very happy guess - I would have liked six). Since it was late August, the delivery date was projected to sometime in December. This seemed to cause some stress. The month of November had taken on magical qualities, so I agreed to a deadline of November 30th. Three months to do what hadn't been done in four previous attempts over the last five years. Three months is a ridiculously short amount of time to do a system like this! The project account code I was to use already had more than 1,300 hours logged against it. I don't know if that covers all time ever spent on the project, or just the time invested in the last effort (probably the latter). To be fair, they were within a few weeks/months of implementation, I'm told. Ok, three lousy months (sure could have used four). Boy, talk about wishful thinking! Apparently a member of the TDHS board heard of the effort, and wondered if we could have the system by November 1st. My guess is that it had something to do with $3.4 million in threatened federal sanctions. Seems reasonable. Poof! Thirty days, thirty-three and one-third percent of my entire development time, had just vaporized! Now I only had two months! Damn! This was getting serious. Am I whining yet? Time to get down to the nitty-gritty, eh? The whole pile boils down to four major areas in the database: the Client Master, Appointments, Forms, and Tickets. Each of the four major areas also contains history data, separate from the "live" data. The client population in all the existing cloned systems combined is somewhere around 25,000 people. Extrapolating out to all ten regions, that gives me a really rough guess of between 35,000 and 50,000 current clients. Due to rumors of expanding the client population by using the system to transport children under Aid to Families with Dependent Children (AFDC), the client count could easily double or triple, to between 100,000 and 150,000 people. So I designed it to comfortably support 250,000. It's all in how you spread the data. The client master and ticket tracking areas are spread across 1,000 rids apiece. The appointment master and form tracking areas are spread across 2,000 rids each. The appointment area also has transaction records, stored using a "julian" rid technique, where the rid number equals the julian date of the appointment. For the client master, tickets, and appointment transactions, history is contained in the upper 1,000 rids, in what I call the "thousands compliment". Add 1,000 to the live rid to get the history. With the appointment master and forms tracking, history is in the same rid number, but in an alternate form type, the "form type compliment". If live data is in form type B, look in type C for history. Within the blocks of rids are sub-blocks, one for each region. For the two areas with 1,000 rids, there are 100 per region; the two areas with 2,000 rids have 200 per region. Within each regional block, the client number is used to determine a specific rid. Just to confuse the issue, our regions are numbered 02 through 11, not 01 through 10. This is where the fun comes in. You see, I hate look-up tables, especially if all I want to know is where I really need to be. Did that make sense? For example, the thought of having a rid with the region in one field and a starting and maybe an ending rid in other fields, that had to be constantly read just to figure out where to go, gives me I/O bottleneck nightmares. There is a way to determine the block given only the region number. First, let me say this: I am not using the @HSH (hash) or @LDV,N (the original hash) functions to determine the block and/or rid. Believe it or not, my research indicated a better spread of the data by using two digits from the client number and running them through a simple calculation (see Figure 1). ---------------------------------------------------------------- FIGURE 1 -------- @. Get the region and client number via screen input, etc. and put them in V1 and V2, respectively. V3 represents the rid. @1:CHG V3 V1 -1 *100 -V2(?-2) . (the exact column is a secret) @. The above calculation gives the rid in a 100-rid spread. @2:CHG V3 V1 -1 *100 -V2(?-2) IF V2(?-1) < 5 DEC,100 V3 ;. @. The above calculation gives the rid in a 200-rid spread. @FDR,,,V3,6,,LBL '' 2-9 ],V2 RLN . . . etc., etc. @. Find and read the data, put it on the screen. . etc., etc. ---------------------------------------------------------------- The appointment transactions have two regions per form type across five form types, with the even regions getting rids 1 to 366 for julian dates, and the odd regions getting rids 501 to 866, the "five-hundreds" compliment (see Figure 2). ---------------------------------------------------------------- FIGURE 2 -------- @. Get the date in YYMMDD format (V1) and convert to julian (V2) @DC D3=D1(V1) V2I4 LDV V4(1-1)=0 . I know I skipped the edit... @. Loading column 1 with 0 strips off the year... @IF V3 = 3,5,7,9,11 INC,500 V2 ;. Offset for odd regions (V3) @. Use V4 for the form type, V5 is a work variable @LDV V4=A CHG V5I1 V3 *.49 CHG V4 V4 +V5 . Get the form type ---------------------------------------------------------------- The only tricky part is turning pairs of numbers into a single letter. If the region, V3, is 2, then 2 times .49 equals .98, which is rounded up to 1. If the region is 3, the 3 times .49 equals 1.47, which is rounded down to 1. So, regions 2 and 3 become a 1, 4 and 5 become a 2, 6 and 7 become a 3, 8 and 9 become a 4, and 10 and 11 become a 5. This number is then used to change V4, the form type, which had been loaded to an "A". The CHG function will allow changing an alpha character by a number, so A plus 1 is B, A plus 2 is C, and so on. This allows me to spread ten regions across five form types. So I had all the data items identified, and techniques to spread it so that out of 10,000 rids in the active database, almost none would exceed 1,000 lines of data, even at a full load of 250,000 clients. Purge routines will periodically roll old active data to history, and old history data to tape. Now I could start working up the screens. With every application I try to do something a little different, so with this one I thought I'd confuse everybody by making the screens "intuitive". When you initially call up a screen, you are automatically in add mode, whereby you key in data and transmit (with the TRANSMIT key!). To inquire, simply type in the data to inquire on, and press F1 (that's FUNCTION KEY 1!). Once data has been displayed, if you transmit with the TRANSMIT key, you are now UPDATING. Seems intuitive to me, don't you think? Well, I thought it would confuse everybody, but apparently they seem to like it (at least I hope so). But hah! Just wait until they see what kinds of lousy reports are in store for them (I hate doing reports). One final note: maybe I exaggerated just a teeny tiny bit when I said four months for the project (two and a half is more like it), but, hey, nobody's perfect! Thanks, MAPPER!