Continuing Database Development:
Projected Tasks and Costs

Prepared by David Kantor, March 13, 2008

 

We are at an important juncture with our database development.  Much has been accomplished.  Much remains to be done.  The rate at which we continue to develop other aspects of the system will be a function of the resources we can bring to bear on the project.  Following is a review of the present circumstances from which we must proceed.  There are two major categories of circumstances that should be addressed: The financial resources we have to devote to the project, and the setting of priorities for further development.

 

The Financial Picture

 

To date (03/13/08) we have paid Alan Goodman a total of $15,580 for his work.  Another estimated $1,800 has yet to be billed by Alan.  Of this $15,580, $7,660 came from the IT/Web 2007 budget with the remaining $7,920 coming from the IT/Web 2008 budget.  IT/Web expenditures for 2007 came in $970 under budget.

That $970 had been allocated for Alan but the billing did not come in until 2008 which means it came out of the 2008 budget.

 

For 2008 I had $5,000 budgeted for Alan.  I originally had another $10,000 in the budget for database development which I had to cut out.  So for 2008 I am already $2,920 over budget for database development with another approximately $1,800 yet to be billed.  If I have to eat these costs it will force me to cut back on some important areas of website development.  It would be very helpful if I could somehow recover these amounts for my 2008 IT/Web budget.

 

An important question needing resolution, then, is whether or not an extra $5,000 may be added to the IT/Web budget to cover this over budget amount. 

 

Before any additional projections of costs can be made we need to prioritize some of the remaining tasks. 

 

Completion of Existing Work:

 

I suggest that we stop any further development and have Alan finish up what has currently been done, including completion of a reports section.  Then we should deal with deployment and implementation.  Several tasks will be associated with this step.

 

1. Completion of report section of existing database.

 

2. Deployment

a. Where does the database reside?

b. What is needed in the way of client software?

c. Integration of data entered into Filemaker Pro since Alan began working.  At the time Alan began, I gave him a copy of the Filemaker Pro database to seed his project. This was in November of 2007 so all records and edits made since then to the Filemaker Pro database need to be added to the new Access database.  All Filemaker Pro data is time/date stamped and can easily be extracted.  Alan will need to provide a script which will take this extracted data and place it appropriately into the new Access database.  Once this is done, the Filemaker Pro database should be archived and only the new Access database used for ongoing work.

d. Need to make sure that screens and reports can be printed locally and/or emailed.

3. Implementation

a. Documentation: Alan needs to create extensive documentation which includes documentation of the backend table structure,
relationships, etc.  The documentation also needs to provide front-end information and instructions for users.

b.  Implementation:  Cutover to using the new system and discontinuation of the old.

c. Training: Alan will need to provide some initial training at least for Paula so that someone on our staff really knows how to use the software.  John Hales has provided valuable support for our data management efforts and should also be trained so that he can continue to provide needed relief for Paula.  Bobby Dreier's management of study group information is important and she should be trained in the use of this portion of the software.

d. Support: As we begin to use the software there will be questions, issues, bugs or inefficiencies discovered, etc. Some provision needs to be made for ongoing support and any related costs.  We should ask Alan to give us a quote for an annual support contract that will be limited to answering questions, fixing bugs, etc.

e. Backups: A program for regular full and incremental backups needs to be put in place as well as a backup of the primary software.  Clearly defined procedures should be in place before any incremental edits or additions are made to the primary software so that we always know that we have the most recent version backed up.

f. Audit of Existing Data: This could be a fairly extensive task but should be done.  The data sets to be audited need clearly to be identified.  A procedure needs to be devised by which this can then be accomplished including the accurate updating of the data in a manner which does not lead to confusion about which data is correct.  This will require a well thought out administrative procedure to be designed and put in place.

 

Note: Item (3.f), the data audit,  will need to be done by Fellowship volunteers or staff.  I suggest we try to engage Larry Geis and his committee in the process.  I will take care of item (3.e), backup procedures.

 

We will need to tell Alan what we want in terms of basic reports noted in (1) above.

 

I suggest that no additional work be started until the above is in place and functioning smoothly.  I further suggest that we attempt to do nothing more in the way of implementation than the above between now and the end of IC08 given how busy Paula is likely to be during this time period.

 

I would estimate that the completion of this work would take at least 160 hours of Alan's time.  At $40.00 per hour this would be $6400.  It would be nice to have a little cushion here so I would suggest estimating 200 hours which would be $8,000.

 

Once we have changed over to the new system and are operating comfortably with it, we should then consider steps toward fuller integration of all of our data sets. This involves creating web-based forms for data entry, queries, returned data sets, and reports.  This will require administrative and workflow planning, and then technical work.  This could be divided into two phases. 

 

First Phase: 

 

1. Integration of existing secondary data sets with the primary database.

a. Fellowship internal email lists -- socadmin, council, ec

b. Online subscription form which collects email and mailing address information from website visitors

c. Skipjack financial contribution information

d. Quickbooks software

 

2. Creation of web-based forms for most basic tasks.

a. Maintaining study group information

b. Adding, updating of reader address information

c. ???

 

For items 1 and 2, this is one-way data transfer.  Data gets entered into forms on the website and stored in intermediate tables.  A person checks the information that has been entered from the website, approves it, and then transfers the information to the primary database. Much of this should be automated with scripts written which take the information and place it in appropriate tables/fields in the primary database once approved by an administrative person.

           

3. Creation of basic reports automatically published to web pages.

a. Study group information

b. Councilor/EC member/Committee member address lists

c. ???

 

Item 3 is also one-way data transfer.  A script should create a report from the database, format a web page, and publish that web page to a specified location at appropriate time intervals, for example, weekly or monthly.

 

Once this first stage is complete, we will be at another stopping point where we should use the system for a while, making sure everything is working ok before proceeding to the next stage. 

 

I would estimate 80 to 120 hours of Alan's time to complete this work, requiring between $3,200 and $4,800.

 

We should target this for completion between the end of IC08 and the end of year 2008.  If all goes well this should coincide with the transfer of the website to a CMS.  The CMS will create the potential for a web interface that more easily interfaces with the database and automates many of the associated tasks.  This will enable us to transfer our web data interface from outside services, such as Caspio Data Service and Hammond Communications, to our own internal processes and more direct interaction with our database. 

 

A very conservative approach would be to leave this first phase until 2009 so that it could be paid for out of the 2009 budget.  An aggressive approach would be to skip it altogether and go directly to the second phase described below.  This first phase only brings us to a well-rounded stopping point based on our present way of doing things by the collection and processing of data via third party online services.  It provides only stop-gap and less-than-optimal data integration.  The second phase described below brings everything in-house and under the full control of our own database and web interfaces, yielding a much more efficient and less labor intensive level of integration.

 

Prior to beginning the second phase described below we will need some decision making and planning on the administration side.  This is where we will need to know the details of how we want to provide data for Societies and enable them to participate in data entry and editing.  We will need to have a clear policy related to who has access to what information.  We can protect information in the database down to a record and field level, but we will need to have this clearly thought out.  Once it is thought out and approved we will then use that information for coding the security for the database.  Essentially we need to create an architecture for service to the reader community based on the architecture of administrative data management that we are creating for the Fellowship.  Of primary concern is the smooth operation of administrative data systems requiring a minimum of staff involvement and a maximum of data security, combined with a simplicity of operation that enables volunteers with minimal training to participate in our data management tasks.  At the same time we need to think about how we can leverage this core data management functionality by providing additional value and services to the reader community which will increase involvement and empower local groups to be more effective in dissemination efforts and in the fostering of social infrastructure.

 

Second Phase:

 

1. Coding web pages for forms and reports for an expanded range of reader services yet to be specified. 

 

2. Integration of financial software with the database

a. Quickbooks

b. Skipjack

c. Online contribution form

d. Autodebit information

e. Contribution records

 

3. Expanded security coding to control access to all records and fields in financial data.

 

4. Expanded backup procedures to include full integration with Quickbooks and Skipjack data

 

I feel that we should reach a completion of this stage before considering the integration of any conference management procedures.  Regonline is working well for us for IC08.  We can save the expense of developing this part of the software until a more basic set of features is in place.  

 

At present I'm estimating between 160 and 320 hours to complete this work.  By far the biggest (and most expensive) task likely will be the integration of the Quickbooks software with the primary database.  Here again we will need considerable administrative consideration.  We will need to clearly define what data we want to manage in Quickbooks and what data we want to manage with our own interfaces to our core database.   We will need to do this in a way which leaves us open options as both the Quickbooks and Microsoft Access products evolve over time.  We should also have some sense about the need for migration to future operating systems associated with these products.  However, the cost of software upgrades should be relatively minor. 

 

I estimate this phase will result in costs ranging from $6,400 to $12,000. 

 

We will save a lot of money for the Fellowship if we take the time to think these matters through very carefully and give Alan as complete a spec as possible with which to work.  If we don't take on this responsibility, we will need to pay him for his time to do the same work. 

 

A Summary of Projected Costs, (none of which are presently budgeted):

 

1. Estimate of Current Unbilled Invoices from Alan Goodman:                                       $1,800.00

 

2. Estimate of Costs for Completion and Implementation of Existing Work:                    $6,400.00 to $8,000.00

 

3. Recovery of $5,000.00 for budget year 2008 IT/Web budget:                                   $5,000.00

 

            Absolute minimum amount I suggest to request for budget year 2008:  $6,400.00

Optimal amount to request for budget year 2008:                                $20,000.00

 

 

Above work is projected to be complete by the time IC08 has taken place.  At that time the financial situation could be evaluated.  If the finances are good, we could proceed with item (3) below.  If finances are a problem, items (3) and (4) below could be postponed until budget year 2009.  However, we still need an agreement with Alan for the provision of technical support.

 

4. Estimate of Costs for Completion of Phase 1 Work Described Above:                      $3,200.00 to $4,800.00

 

5. Estimate of Costs for Completion of Phase 2 Work Described Above:                      $6,400.00 to $12,000.00

 

 

Amount I suggest to request for budget year 2009:  $16,800.00