i’ve got a nagging headache.

i’ve been trying to redesign the revenue portion of our financial database at work. i worked on it straight for a week before cambodia, until i had this nagging problem with negative expenses. well i solved that by just treating a negative expense like a new source of funding.

what this has all come to is that for everything to work correctly, i’ve got a little algorithm that must churn away for 13 seconds (comparing every expense to every funding source) before the user interface can open. this is starting to make me think all is not right, so i’ve forced myself to step back and see if there are some more fundamental changes i need to make to the fiscal system as a whole.

the gist of the problem is and has always been that the purpose of the database is to connect expenses that hit our university accounts to activities that our project runs. on the revenue side, we’ve got to track the funds we get from USAID by type (which is pretty straightforward) and then we budget portions of what we receive to each activity.

so what we have here is:

• an expense can be split among one or more activities
• an activity can have one or more expenses
• a fund can be split among one or more activities
• an activity can have one or more funds

the point of all this is to be able to answer the inevitable question that USAID will ask: “how much have you spent of X fund?” or “how much Y funding do you have left?”

so take a given activity. it has a bunch of expenses and a bunch of funding. you can sum the expenses and find out how much the activity has spent, you can sum the funding and find out the total amount of funding the activity has received, and you can subtract the total amount funded from the total amount received to find out how much money the activity has left to spend.

but you cannot ask specific questions about how much of a given fund has been spent, especially project-wide.

so my algorithm is a loop that compares (for a given activity) the amount spent by year to the amount funded by year, and it stores the results in a new table so we can answer the questions above.

what is unique about our accounting system is that we can go back in time and reallocate expenses to different activities as long as the bottom line remains the same. which means from month to month the amount an activity spends can vary widely, going up or down, depending on how expenses are allocated.

this flexibility is the source of my problem, namely that the static table generated by my algorithm depends on the state of the expenses in the database at any given point in time. since any number of expenses can be reallocated at any time and since expenses are imported into the database every month and since there is no record of what has changed and what hasn’t, my 13 second long algorithm must run (periodically) so that Phill (my boss) and USAID get an accurate picture of ‘how much has been spent by fund’.

so here i am, home from new orleans, feeling like a little unfiltered stream of consciousness to end the day.

yesterday i woke in chapel hill and flew to new orleans for lunch. did some work and had an amazing dinner, duck appetizer in crepes, salad, and the most wonderful duck for dinner. with a beer before dinner at the bar and a half bottle of wine with, my night was complete. well, almost complete. can’t forget the obligatory stroll down bourbon street, with obligatory beads and obligatory onlookers. we can now say trip #4 to the big easy is complete.

today some work, some lunch, and then some riding in the plane. unlike most trips where i feverishly try to make database improvements up until the last possible moment, during this short trip i pretty much sat in meetings and listened, voicing an occasionally cautious note of “maybe we shouldn’t have me do anything until we figure out what it is we want me to do…” fun though watching the process of telling faculty and researchers things like: we need you to find a way to spend $750,000 quickly.

it does seem there is a pressing need for greater access to and centralization of all manner of information, with the web being the most obvious candidate for extending access to people outside the office. unfortunately microsoft access makes doing that a wee bit tricky (which is good for microsoft, forces most people to purchase SQL Server) but bad for us techy chaps who’d rather roll our own system out of whatever else seems appropriate and free.

more and more i’m questioning the viability of the traditional table data structure and thinking more in terms of ‘documents’ as the starting point. it seems to me that something is lost when we take what start out as documents and chop them up to fit in a database and suddenly have limited powers for reconstituting the original document (as a document). maybe if we left the document as a document, impose some rigorous structure (xml?) and provide some way to query a collection of documents (xquery?) as if a database, we could forgo using a database in some cases altogether.

oh, and my last thought: user interfaces. access is nice, but really it mostly sucks. the web is insufficient, but… mozilla is not. if rather than using $300/per user access as a user-interface client to access data in $0 mysql database with $0 myodbc so that $0 php can spit the data out to web, maybe we just ought to use $0 mozilla and its XUL+CSS+Javascript technology to replace the access user interface. i wonder how feasible/powerful that is? glad i bought the $40 mozilla book by oreilly (which is also available online for $0) to read for fun.

I traveled to Almaty, Kazakhstan for work in April and August of 2002. The following are a collection of photos from both trips.

Continue Reading…