Personal Information Management The Old Fashioned Way
I have crohn's disease. It's not terminal or anything like that, but that's the most succinct way of conveying that I see a lot of doctors, and take a lot of medication. For a few years, I've hemmed and hawed over how to keep all of my records someplace organized. I've spent hours looking at database apps for my iPhone and iPad, I've considered using some version of MS Access (or a similar tool) to build my own, and I've looked at various hosted "cloud" solutions. All of those options usually ended up somewhere on the spectrum between "inconvenient" and "shit". There's also a degree of risk-management to consider: What kind of privacy policy, data governance, and transportability could I expect from a hosted solution? That mostly knocked anything cloud-based out of the running. So to date, my record keeping never really evolved beyond a few simple flat files for logging current medications or notes.
I don't consider myself a Luddite by any degree, but it's no secret I have a certain fondness for retro tech, and age is merely a number when it comes to finding the best tool for a job (see also: the HP 48G calculator I use as an alarm clock, with programs to quickly set an alarm for the following morning). In a fit of boredom during a recent hospital stay, I started looking around different archives of publications, discussions, and software to see what "well worn" tools might be out there to fill the niche. My main goal was to find an extremely rapid-application-development tool to build a relational database, and have it be something easily portable (both in physical terms of carrying the computer it's running on, and in the sense of being able to move data in and out). I don't want to spend hours laboring over writing UI and data binding code by hand, and I want to be able to use the data anywhere.
Enter DataPerfect
To cut to the chase, the tool I found is DataPerfect. DataPerfect, as subtly implied by its name, was originally a product from the makers of WordPerfect. It was initially released in the late 1980s, and sold to Novell in the early '90s. In 1995, Novell released it as freeware, and allowed Lew Bastian, the original author, to continue updating the program as he saw fit. (Palmtop Paper, Vol. 8 No 1) Despite its debut being nearly 30 years ago, the latest release, 2.6Y, was made available in 2008. (So it's Y2K compliant!) If you'd like a copy for yourself, the program and its manuals are just a few Google searches away.
If you were hoping to watch a charmingly campy marketing video, then you're in luck: Are You Cut Out For DataPerfect?
Without going into any sort of lengthy sales pitch, here are some of the key features of DataPerfect:
- Genuine relational database, with one-to-one, one-to-many, and many-to-many join capabilities
- Highly optimized indexing and lookups
- Text-based UI builder that requires no coding, and no mouse
- Runs on MS-DOS
- Only requires about 300 KB of RAM, but can handle multi-gigabyte databases if needed
- Fully networkable, with support for thousands of concurrent users (But you're insane if you try to use it for this today! There are obviously vastly better multi-user database systems available now.)
- Basic user security system (But it's a file-based database; don't kid yourself and think client-based security will protect you.)
With DataPerfect's modest requirements, this means it's an ideal candidate to run within DOSBox, which in turn works on damn near any computer in service today. (And anything too old to run DOSBox is probably capable of running MS-DOS programs directly anyway.) So that takes care of the portability goals; it'll run on my nice new desktop computer, and also on my humble HP 200LX Palmtop from 1994, and I can easily move data in and out, in a variety of different formats.
So, how does the program work? In DataPerfect, a "panel" is essentially a one-to-one combination of an on-screen form and its underlying table. Unlike in, say, Access, where one can design any number of forms, and bind them all to the same table if desired, you only get one per here. Also, "requires no coding" is a slight understatement; in fact, there is no sort of imperative coding for use in building your UI. Those points made me a little nervous at first, but they turn out to not be all that limiting, as long as you're satisfied with a UI designer that can get you 90% of the way there, if not always 100%. Perfect is the enemy of good, right?
The DataPerfect UI Builder
To its advantage, DataPerfect has a relatively robust formula language that can be used for calculated fields, or setting field default values. And despite the lack of any UI coding provisions, panels can be highly customized with data and navigation links to other panels, subforms ("windows") that show a summary list of related records, fields that automatically total up data from child records, custom lookup lists for finding records, hidden fields with formulas that can do all sorts of clever tricks, and partial indexes that allow browsing subsets of a panel's data. And that's to say nothing of the report builder that not only allows for reading and outputting data, but can also modify data and thus be used as a sort of supplementary scripting and processing language.
Building the Database
Here's the laundry list of the kind of records I want to be able to track:
- Medications (current and past, plus logging as-needed doses)
- Doctors and facilities
- Visits/consultations with notes
- Test results
- Procedures
- Hospital stays
- Symptoms
- Food
I started by working on the medication portion of the database, as this data doesn't change all that frequently, meaning it would be a good place to get my feet wet. It was also a good trial-by-fire to make sure I could import CSV data exported from my current medication list. DataPerfect passed this trial with flying colors.
With that in place, I started branching out into entering doctors' contact info, logging visits and notes, and recording procedures. Then I added a place to record test results. For now, I've only set up a panel for entering CBC results (a blood test), but it will be pretty trivial to add panels for other types of tests.
After feeling pretty comfortable with DataPerfect's design paradigms, I started on the "diary": a sort of daily journal of food, symptoms, and "as-needed" medication doses. The diary panel presents a three-column view of each of those things, and being the most data-intensive part of the application, I tackled this last to make sure I got it right; it involves some many-to-many joins. The final product is a nice little dashboard where I can quickly log any of those three things throughout the day:
Analyzing the Data
As excellent as DataPerfect is for building a data-entry application, its report writer isn't nearly as powerful as writing an SQL query in a modern database. My eventual plan is to use the export capabilities of DP to do some basic ETL and get the data in SQL Server when I feel the need to do more sophisticated analysis, like looking for correlations between, say, food and symptoms. The jury is still out on how I want to architect this. DataPerfect has an assortment of command line options and such that can be used to script its actions (I kid you not, there are people using DataPerfect as a web-development back-end). Thus, I may be able to roll the whole process into an SQL Server Integration Services package that quickly slurps out the data. To be continued!
So what's the moral of the story? I say, the ends justify the means, at least as far as personal data management is concerned. I've spent hours looking for modern solutions to this problem, and in the end, a database program that's almost as old as I am ended up being the best solution. Though I have to admit, the means did end up being pretty cool in this case. I'm always impressed when old tech stands up to, or even bests much more modern alternatives.