masters paper

this page is no longer being updated as of 12-APR-04

latest files (all pdf)

Progress for the week of March 22 (last week before turning in draft)

revised update_obligation to match update_fund_allocation.
made sure delete_obliation and delete_fund_allocation follow same redirect rules. so far i have half of a functional financial information system, and i HAVE to stop working on it today. sheesh.
created first stab at state transition diagram, for revenue only
started thinking about the writeup, for which i have saturday and sunday---in order to churn out a draft for marchionini. it's such a shift from the developing i've been doing lately that my mind doesn't seem to be playing along. after looking at some other mp's, i've got an idea of how this is going to go: intro, background, design, implementation, next steps. it has a nice arc to it, but i think i'm stumped by the intro (usually 1 page) so i should probably go ahead and write the background--or pull that from some of the stuff i've already written. design and implementation have not been two separate processes, rather i've iterated between the two quite frequent (as these notes will attest). next steps will be great because then i can sketch out what i have not accomplished, and what needs to come after that, etc. literature review, haha. i've had time to read a chunk of two books and the first chapter of a third. literature review could be a whole nother master's project. it's midnight now. time to get go home, get a snack, maybe get some more of the background down on paper.
redesigned update_fund_allocation form (created new validate_id function, revised get_fund_id function) so the earmark stuff is segregated from the fund_type and objective radios, which was just messy. created a function to generate radio lists with connected labels, tooltips, the works! (need to redesign update_obligation_form to match)
you can create/update a fund_allocation from two different places, fund_allocation.php and obligation.php. when you submit or cancel that form, you should be sent back to the place where you originated, in the same state you were in before.
started work on redirect problem, started to sketch out state transition diagrams (why are they so hard?)
fixed 2 update_obligation bugs: earmark_id and account_id were not being loaded when editing a fund_allocation
creating more explicit task list
making brilliant updates to the obligations list/UI so it shows the fund allocations for a given fund/year
modifying the fund_allocation form to replace select box with a summary list similar to the Access UI
creating links in between obligations and fund_allocations forms. trying to make this more transparent, joining the two under the heading "revenue". perhaps "financial planning" would be more accurate.
revised current version of erd to show two relationships between account and activity
first stab at functional specification for phase II website (not exactly mp related, may come in handy though)
included allocable obligations as separate table on obligation form
web working group met 1-4pm

Progress for the week of March 15

found the bug in generate commitments function, now maximally commits fund_allocations to obligations, added new commitment field (available_amount) to capture how much of commitment is actually available from given obligation. been pondering how much commitment will come in handy when i try to tackle transactions.
revised the entity descriptions document so that it is again in sync with the current ER diagram
reworking generate commitments function, buggy
entered fund_allocation records, to test the UI. works alright.
renamed "allotment" commitment. got commitment working, at least from the obligation side. next step is to get them working from the fund_allocation side. it is a thing to behold.
phill has been budgeting the full amount of each obligation, as opposed to the amount less allocable.
obligations should be able to be over committed (which would result in a negative number in the uncommitted column).
allocable obligations should show up as a single row for a given year.
should rows show up for the fund_allocations that target non-existant obligations? yes!
posting the 8th somewhat major revision of my ERD. includes the trip concept (similar to personnel efforts), though that may not be implemented for some time. moved the transaction relationship back between expense_allocation and obligation, as it was in version 5.
trying to come up with some way to diagram manual and automatic system processes
the transaction relationship between expense and obligation is agnostic of the expense_allocation (hence activity) that spawned it. this is bad. i thought expense allocation could be to transaction what effort_allocation is to expense_allocation, but this analogy has a flaw. transaction is not related to the all important activity. you can tell how much of a given obligation you have left, but i don't think you can tell the things we'd need to know by activity.
trips? (are like efforts)
revising ER diagram based on transaction misgivings noted above (not posted yet)
frustrating slogging today. revised obligation/update_obligation based on earmark revisions. this was hard. i want to get allotment working, just to prove to myself i can do it.
perl expense import script working. (semi-major effort)
checked progress of PID inclusion in DAS table (probably won't happen in time)
checked progress of installing PHP to latest version (probably won't happen in time)
imported phase II expenses. they end at 2/23/04 for no apparent reason.
created expense.php for expense viewing only
started work on allocate_expense.php and allocate_expense_multiple.php for allocating expenses to activities
thinking more about the seemingly constantly changing business rules as demonstrated by pepfar/pai. databases are wonderful in static environments, but few environments are truly static. how do you create a normalized database in the face of changing data description demands? i'd like to build some extensibility into my "fund" entity, something like an optional "earmark" attribute that captures the fluctuating bureau-wide, management, pai 1.5, pai 2, pai 2.5 qualities. as long as they're all mutually exclusive, this should allow the addition of future "test" attributes. the problem is this complicates the business rules. only SO4 objective funds can optionally be earmarked as pai 1.5, pai 2, or pai 2.5; only Core funds can be optionally earmarked as management or bureau-wide (which has implications for the calculation of allocable).
what is a "fund"? it's really an abstraction, intended as a composite attribute serving both fund_allocation and obligation. but is year 1 allocable any different from year 2 allocable? are those "different" funds? i'm starting to think not. i'm starting to think "project year" should really be an attribute of fund_allocation and obligation, but not fund. fund should consist of a manditory fund type (core, field support, maard, allocable), an objective (SO1-SO5) null for allocable, a region (for field support and maard only), and an optional earmark. considering these two "issues" i've updated the entity relationship diagram (v7).
worked on perl script to grab expenses from DAS and insert them into FIS. this is harder than you'd think, plus it's perl. i'd do it in php, but cpc's version of php has no oracle db functions installed. hard to have a financial system without expenses.
spoke with fill about new president's emergency plan for aids relief (PEPFAR) aka president's aids initiative (PAI) which comes in three flavors (track 1.5, track 2, track 2.5). this is special SO4 money, and it's not clear how we'll be require to report it.
the original workflow diagram wasn't exactly a workflow diagram. it was more of a sequence model cum layered architecture model. but it was useful in helping me think about the relationship between expenses and funding for my ER diagram. so i've created a new workflow diagram (version 4) with specifies users (or user groups) and their interactions with the financial information system.
comment to consider from dad: "In the document can you indicate which directed relations are aggregations, that is the relations that provide cascaded delete. Some people call this a whole-part relationship where one entity is the whole and the other is the part. So that when the whole is deleted, the part is also deleted. Also a part entity can be a part to one and only one whole entity."

Progress for the week of March 1

finished fund_allocation.php, update_fund_allocation.php, delete_fund_allocation.php. next step--automatic generation of allotment records. future development: ability to manually order fund_allocations using UI.
finished revision version 3 of entity description document so that it is in sync with ERD v6 and now includes data dictionary tables.
finished update_obligation.php form (which creates and updates obligations)
finished obligation.php (which lists out all obligations in database) future development: highlight most recently created/updated row; color code rows based on fund; filter list (probably beyond MP scope, given impending deadline!)
created delete_obligation.php which... deletes obligations
both of the forms above implement the allocable solution i described on monday. the obligation form is responsible for creating two obligation records, the primary obligation record and it's child allocable obligation record (if applicable) based on the yearly agreed upon allocable_percent stored in the project_year table. when the obligations are listed out, the two records are "summed" together and the allocable record is thus "hidden". in the future there may be a variation on this interface that shows the allocable rows on their own (or separately), so one can determine amount budgeted and amount expended of each.
wrote specification on publication code format (not really MP-related)
wrote position description for my assistant to be (not really MP-related)
updated training website (not at all MP-related)
created update_effort form, which subsumed create_effort (basic personnel allocation component complete)
begun work on perl script to move expenses from DAS to CPC. incorrectly installed...? didn't get very far.
possible allocable solution: add "allocable" as option to fund_type. make objective and region optional for fund (optional for funds of type "allocable" only) as much as i don't want to. project year is still required. add "allocable_obligation_id" column to obligation table, to allow creation of allocable records, that point to their allocable child record. create/update fund_allocation interface must allow selection of allocable fund for a given year. this scenario should not break posited "allotment" relationship, and should allow transparent generation of "transaction" relationship between expense and obligation.
revised erd v6 in line with my current thinking on allocable
working on revision of entity description document so it aligns with the current erd
apparently which has access to AFS (where everything is stored) can do perl/dbi/oracle. so i can start testing over there (but haven't yet). eventually i'd like a better solution.

Progress for the week of February 23

entity relationship diagram version 6 practically re-did everything. now funds can be allocated to activities that have yet to be obligated to measure (in other words, funds that are projected). now expenses are directly related to obligations via the transaction relationship. "expense_allocation" is just another informative abstraction, like effort_allocation. i think there should be a relationship between fund_allocations and obligation, but i'm not sure, and i'm not sure what to call it. right now it's "allotment". totally stumped about how to track allocable. i have a few ideas, but all of them add complexity to this model in a way that suggests the model needs to change to accomodate allocable, which is essentially a meta-obligation, (5% of every non-bureau-wide or basic management obligation with the ability to be budgeted).
built the effort form, create_effort, delete_effort (which includes creation of effort allocations). still need to do update_effort. once that's done, phill can start playing, fill in everyone's time.
reconceiving "fund" as a generic describer of fund_allocations or obligations. trying to figure out what to call the relationship between fund_allocation and obligation. allotment?
pulling hair out about what to do about allocable. allocable is something that needs to be automatically siphoned off of obligations and needs to be something that can be budgeted to specific activities. but because allocable can be siphoned off any obligation that is not core-mangament or core-bureau-wide, it should also probably burn those funds evenly--probably not though, it could just burn them in order. but how to budget alloable then? only US-1 (general management) and US-9 (other ODC) will ever be allow to be recovered by allocable. should allocable then be automatically budgeted to these activities? should it be treated as a special fund, or should the database just obligated the appropriate percent of each appropriate fund to US-1 and US-9? should activity and fund_allocation both have is_allocable switches, the latter signifying that the database has automatically budgeted the appropriate amount of each fund and denying the end-user from modifying them.
spoke with phill about the bureau-wide/basic management constraint that they must be burned evenly and budgeted without regard to the initiating SO. re-realized that only Core funds will ever provide basic management or bureau-wide funding. the burn evenly/budget ambiguously constraint was also erroneous---or atleast an unrequired "feature". this means budget items can now mandatorily require the objective and region. thinking about changing obligation_intent to obligation_type with core-directed, core-bureau-wide, core-management, field support, and maard as options.
designing a number of form and list mockups as a way to think constructively about the interface and the underlying architecture. thinking seriously about the fund_allocation side of revenue.
met with Bob Dermody and Teddy Britt, discussed fields in transaction table and possibility of including a new field (PID) for personnel expenses.
worked on create_obligation form--having serious reservations about my obligation-fund architecture. realizing that phill may want to budget funds that we don't have obligations for (yet) is throwing a majority of the design into question.
continuing to think about the database architecture and system design assumptions. expense_allocation is just an abstraction like effort_allocation...why not treat it as such--as a way to inform the database as to how expenses and obligations should be related in the form of transactions.
problem: you create an obligation, which causes the database to possibly create new fund records and definitely new obligation partition records. then you allocate some of those funds to activities. then later, you want to go back and substantially modify the qualities about an obligation (like the country, year, objective, intent) which may eliminate a fund that's already been allocated. this is a very dangerous integrity problem---but while trying to modify the obligation form i made last night to edit an entire obligation record, i realized that a user should not be allowed to edit an entire obligation record. in other words, once an obligation is created, certain qualities about it must never change. in fact, the only thing that you'd be able to modify (without significant effect) is the amount and the note. the only way to change an erroneous obligation would be to delete it first--which the database should only let happen if it won't eliminate any allocated funds--and then recreate it.
added project year table, modified current ERD, debating whether there should be a separate "modification entity"...
created basic obligation_list page. no filtering abilities
created a form, launched by javascript, for updating the obligation amount and notes
to do (next): include mod number in data viewing/entry, implement create_obligation form, generate required obligation_partition and fund records, implement validated deletion function.
created data dictionary of tables with descriptions, types, and constraints
sorted the entities by name in the entity descriptions and constraints document worthy of a v2?
finished writing first draft of Entity Descriptions and Constraints
revised ER digram in accordance with my comments from last night. version 5
thinking about writing a treastise on ER diagram line endings, may need to save my energy for other tasks. basically comes down to the fact that all line endings are either "only one" (mandatory) or are "zero, one, or more" (optional). "zero or one" is a special case and can be denoted by a dotted line. "one or more" is rare and raises questions about the current state versus the desired state of the database. in other words an instance of entity 1 may not initially relate to any instances of entity 2, but the desired state of the database system is for the instance of entity 1 to eventually be related to one or more instances of entity 2. clear as mud? (this was all triggered by p22 of Harrington's "Relational Database Design: Clearly Explained" and jane reading it for inls 162 last night.)
i feel like i am on crack today.
started reading the accounting services section of the UNC Business Manual. fun. chock fun of goodness. more practical information about stuff i've worked with for the last four years but haven't known half a thing about.
started deciphering Bob Dermody's DAS data dictionary. trying to determine what transaction rows and columns measure needs. Created a "DAS data export" document.
built obligation form prototype

Progress for the week of February 16

crap. so on thursday i renamed the allocation entities: allocated_expense, allocated_fund, and allocated_effort, but i just realized that was wrong because an allocated_expense is not a type of expense the same way a personnel_expense is a type of expense. an allocation is "a record that captures the relationship between a thing and an activity". thus they should be named by the noun "allocation" and prefixed with the entity being allocated. so i'm going to rename them back, and following that logic, paritioned_obligation will now be obligation_partition (two characters shorter, but i still don't like). I feel that this rationale is also consistent with the naming of lookup tables: region_type, obligation_intent--they are types and intents of regions and obligations, not the other way around.
there is this constant tension between designing an ER diagram that is one step away from implementation, or several steps away. as database naming conventions are especially important to me, i like all my eventual tables to show in my diagrams. otherwise effort_allocation, transaction, and obligation_partition would just be diamonds with attributes floating off of them. but this tension comes more into play when deciding whether or not to model subclass and superclass relationships, which are useful in determining which attributes apply to which subclasses, but when implemented directly (utilizing one-to-one relationships between the superclass table and each of its subclasses) the database complexity grows unnecessarily. the place where i might employ this in my design is with expenses---some of which are personnel_expenses (and are related to the person table) and all the rest are non-personnel_expenses (and have a descriptive payee text field). should i model these subclasses in my ER diagram even though i plan on implementing them as a single expense table? should i implement each subclass as a table? should i not even bother diagramming the superclass-subclass relationships and lose some constraint semantics? for now i'm going with the latter, so in the next ER diagram, i'm killing the "personnel_expense" entity which had no attributes other than the person_id foreign key. i like implementable ER diagrams!
i began writing an entity description document (suggestion from dad). i'm about half way through and am including fairly rigorous write up of the constraints betweens between tables. in the next ER diagram, I may include some fancier "arrows", that classify mandatoriness of relationship (thanks to jane and her 162 readings).
in an attempt to integrate the effort/effort_allocation abstraction layer into the core system, i've added a personnel_expense entity that's related to the person and effort entities. a more integrated design would have consolidated multiple personnel expense records under a single, or several expense_allocations. but then an individual expense_allocation would no longer have an atomic expense date (which would be important for date-range filtered reports). i removed some fields that i accidentally left in the fund_allocation and fund entities. i'm debating whether i should name expense_allocation, fund_allocation, and effort_allocation like that, or like allocated_expense, allocated_fund, and allocated_effort. the former would sort the allocation tables with the tables their allocating, whereas the latter sounds more active, looks prefix orientied (like "personnel_expense"). i'm going to try the latter and see if that grows on me. what about obligation_item? (allocated_obligation? allocablized_obligation?). i'm going to rename that partitioned_obligation which is technically accurate but also very long. hmm... here is erd version 4.
spoke with teddy britt of ais about the oracle tables they set up for KFBS and CPC.
revised data entry workflow diagram, synchronized it with the new system task list
met with Ed, Phill, decided on trial run of WebGUI for foreign travel tracking
created a diagram that combines components of layered architecture within a workflow framework.
i attempted to motivate an entity-relationship diagram based only on the workflow diagram and discovered that the "fund_expense" layer served no function, and that the "aggregated_expense" layer provided only a minor caching benefit--that would probably only increase programming complexity. so i eliminated these layers, giving me version 2.
the entity relationship diagram i created from scratch (based on the workflow diagram) had a many-to-many relationship between the old "expense_item" and "fund_item" entities. this new relationship (which i've named "transaction") will essentially connect expenses to one or more funds and funds to one or more expenses. this entity relationship diagram also helped demonstrate that the only place i need to record an "amount_expended" is in the "obligation_item" relationship--due to the complexity added by the constraint that expenses charged against bureau-wide or basic management funds must expend their composite obligations evenly. (note to self: maybe there is another way to do this...) lastly the 'workflow-diagram-inspired'-entity relationship diagram confirmed that the majority of my existing ERD is totally on the right track. so i give you, erd version 3.
made the project proposal flow better
finished uniquifying of publication filenames (not really MP-related)
spoke with Teddy Britt, Bob Dermody of DAS about getting info on their Oracle tables

Progress for the week of February 9

Fri, Sat, Sun
reorganized web publications (not really MP-related)
built interim measure evaluation phase2 homepage/blog
put trip reports into web database (not really MP-related)
purchased, started reading Web Application Development with PHP 4.0
got oracle client from software acquistion, installed it, using Oracle ODBC driver + Access to view, query tables (old habits die hard)
went through both oracle tables (views) created for the CPC, doing "group by"'s on every column to get an idea of the sort of data each holds
emailed some AIS folks about getting more detail info, documentation, may get together with them next week
trying to install oracle client
trying to figure out kerberos for php
revised proposal (now at version 3) based on comments from marchionini

Progress for the week of February 2

wrote document outlining current fiscal system tasks, and who performs them
wrote document outlining proposed fiscal system task/functions, trying to define/limit the scope of the web-enable portion of the system
significantly revised the underlying entity relationship model of the database based on new information from phill concerning the handling of Bureau-Wide funding and Basic Management funding.
significantly revised project proposal, emailed to marchionini for comment
checked out the following books:
read seven chapters of "Building web applications with UML"
wednesay: met with Ed at the CPC to discuss the following three topics

Progress for the week of January 26

Progress for the week of January 19

Progress for the week of January 12

Progress for the week of January 5