Sam Danielson

I have been designing a database schema for my pilot logbook program and I figured it wouldn’t hurt to peek at the one I have been using for over three years now, Logbook Pro, which stores it’s data in an MS Access file with the extension lbk. The file utility revealed that this was indeed an Access file but Kexi and Openoffice both crashed while trying to open it. Thus began the search, ending in MDB Tools.

Oracle has an excellent article, New Life for Old Microsoft Data, describing the use of MDB Tools. You have to love the marketing spin on the title.

To my delight these tools were packaged in aptitude as mdbtools mdbtools-gmdb. The latter of which is the GTK enabled one. Nice, now to peek at the Logbook Pro schema.

The tables seemed designed to be heavily maintained by application code or triggers because of the denormalized log format, which is to be expected I guess. It’s basically a paper logbook in electronic form. The main ‘tblLog’ table, for example, has several references to the aircraft flown both directly via ‘AircraftID’, ‘AircraftIdent’, and ‘Aircraft’, and indirectly via flight-time columns like ‘AirplaneMELand’, ‘Glider’, ‘Helo’, etc. The table also has flight-time columns for ‘PIC’ and ‘SIC’, which are dependent on the job I was doing. I would personally prefer a more normalized conceptual schema to allow the creation of a diverse set of views.

The real reason I am writing new software, though, is that I was a moron and I lost my software registration keys for Logbook Pro and ADPL. I also see no reason to keep Windows around since I prefer to master my data without the recurring fees. Heck, I wouldn’t have even been able to open these files without MS Access and I don’t think Logbook Pro has a provision for SQL queries to it’s database.

When an airline application asks, “How many night landings have you completed in single-engine piston airplanes while acting as pilot in command but not as a flight instructor?”, I might just feel like a tall glass of SQL. Every pilot I know eyerolls in response to these questions because it usually means adding by hand through a few thousand lines of data. Usually one comes up with a close guess, then cross-checks and corrects the answers for inconsistencies. The worst part is, every airline application is different. Hours are wasted.

How much easier would this be?

SELECT sum(night_landings) FROM log_view WHERE single_engine > 0 AND airplane > 0 AND piston > 0 AND pic > 0 AND dual_given = 0;

For educational and entertainment purposes I’m posting the DDL for tblLog in Logbook Pro. That’s fair use, right?

CREATE TABLE tblLog
 (
    s_Generation            Long Integer, 
    s_Lineage            OLE (255), 
    LogID            Long Integer, 
    LogDate            DateTime (Short), 
    s_GUID            Replication ID, 
    EntryTime            DateTime (Short), 
    AircraftID            Long Integer, 
    Aircraft            Text (50), 
    AircraftIdent            Text (255), 
    RouteFrom            Text (255), 
    RouteTo            Text (50), 
    Duration            Single, 
    AirplaneSELand            Single, 
    AirplaneSESea            Single, 
    AirplaneMELand            Single, 
    Helo            Single, 
    Glider            Single, 
    Custom1            Single, 
    Custom2            Single, 
    Custom3            Single, 
    Custom4            Single, 
    Custom5            Single, 
    Custom6            Single, 
    Custom7            Single, 
    Custom8            Single, 
    Custom9            Single, 
    Custom10            Single, 
    LandDay            Long Integer, 
    LandNight            Long Integer, 
    CondNight            Single, 
    CondInstrument            Single, 
    CondSimInstrument            Single, 
    AppNumber            Long Integer, 
    AppType            Text (50), 
    Simulator            Single, 
    CrossCountry            Single, 
    Solo            Single, 
    PIC            Single, 
    SIC            Single, 
    Dual            Single, 
    IP            Single, 
    SEFE            Single, 
    Option 1            Boolean, 
    Option 2            Boolean, 
    Option 3            Boolean, 
    Option 4            Boolean, 
    Option 5            Boolean, 
    Option 6            Boolean, 
    Option 7            Boolean, 
    Option 8            Boolean, 
    Option 9            Boolean, 
    Option 10            Boolean, 
    Cost            Currency, 
    optILS            Boolean, 
    ILS            Long Integer, 
    optPAR            Boolean, 
    PAR            Long Integer, 
    optTACAN            Boolean, 
    TACAN            Long Integer, 
    optASR            Boolean, 
    ASR            Long Integer, 
    optLOC            Boolean, 
    LOC            Long Integer, 
    optVOR            Boolean, 
    VOR            Long Integer, 
    optNDB            Boolean, 
    NDB            Long Integer, 
    optGPS            Boolean, 
    GPS            Long Integer, 
    optMLS            Boolean, 
    MLS            Long Integer, 
    optBC            Boolean, 
    BC            Long Integer, 
    optRNAV            Boolean, 
    RNAV            Long Integer, 
    optSDF            Boolean, 
    SDF            Long Integer, 
    optLDA            Boolean, 
    LDA            Long Integer, 
    TOTime            DateTime (Short), 
    LANDTime            DateTime (Short), 
    Expense1            Currency, 
    Expense2            Currency, 
    Expense3            Currency, 
    Expense4            Currency, 
    Expense5            Currency, 
    Expense6            Currency, 
    Expense7            Currency, 
    Expense8            Currency, 
    Expense9            Currency, 
    Expense10            Currency, 
    TTLExpense            Currency, 
    TTLLands            Long Integer, 
    PerDiem            Currency, 
    TimeOUT            DateTime (Short), 
    TimeIN            DateTime (Short), 
    counter1            Long Integer, 
    counter2            Long Integer, 
    counter3            Long Integer, 
    counter4            Long Integer, 
    counter5            Long Integer, 
    counter6            Long Integer, 
    counter7            Long Integer, 
    counter8            Long Integer, 
    counter9            Long Integer, 
    counter10            Long Integer, 
    chkHolding            Boolean, 
    holding            Long Integer, 
    txtText1            Text (255), 
    txtText2            Text (255), 
    txtText3            Text (255), 
    txtText4            Text (255), 
    txtText5            Text (255), 
    txtText6            Text (255), 
    txtText7            Text (255), 
    txtText8            Text (255), 
    txtText9            Text (255), 
    txtText10            Text (255), 
    Remarks            Text (255), 
    Legs            Long Integer
);

So long big table and you’re forty-two friends, you served me well.