LIBRARY ACCOUNTING USING PARADOX:
A Database Management Software Package
Finanzas y Administración
de Bibliotecarias Usando la Base
de Datos PARADOX
Mary Ann Van Cura
St. Paul, MN 55104, USA
Abstract: The goals in implementing the electronic ledger and spreadsheets were to reduce or eliminate the time spent on manual computations of expenditures by fund, by month and by year; to provide a means of manipulating and rearranging the accounting data in the manual ledger; and to increase the amount of management information avail-able to administrators.
We were using an automated acquisition system that did not have good fund account-ing capabilities, and we had a paper ledger that did not permit manipulation of the accounting data it contained. We decided to create a database of accounting records that could be manipulated in a variety of ways to facilitate accounting, library planning, col-lection development, and collection maintenance.
We chose to use PARADOX, a state-of-the-art, relational database management soft-ware package. This paper will consist of an overview of the purpose of creating the electronic ledger and spreadsheets, hardware and software used, database design, and reports generated. The review of database design will include type of data collected, record structure, and table structure. Sample reports will be viewed and discussed.
Resumen: Esta ponencia ilustra el uso de PARADOX, un programa de bases de datos, para el manejo de funciones de contabilidad dentro de la administración financiera de una biblioteca.
El objetivo de estea implementación es reducir el tiempo utilizado en la producción de registros y cómputos del estado financiero de la biblioteca en los distintos departamentos que manejan fondos, tales como: adquisiciones, publicaciones seriadas. Se persigue manejar más adecuadamente y más rapidamente el acceso a tan vital información, nece-saria para los administradores: de costos, planificacion, desarrollo de colecciones y mantenimiento.
Este trabajo ilustra mediante ejemplos la utilidad de tal programa en
la contabilidad de bibliotecas.
The goals in implementing the electronic ledger and
spreadsheets were to reduce or eliminate the time spent on manual computations
of expenditures by fund, by month and by year; to provide a means of manipulating
and rearranging the accounting data in the manual ledger; and to increase
the amount of management information available to administrators.
We were using an automated acquisition system that did not have good fund accounting capabilities, and we had a paper ledger that did not permit manipulation of the accounting data it contained.
The ideal for us would have been to use an integrated automated system or an automated acquisition system with fund accounting capabilities. However, the stand-alone acquisition sys-tems in our price range lacked adequate fund accounting capabilities. And although we were and are a member of a consortium of nine institutions that shared an online catalog, it appeared that it would be a minimum of 3-5 years before the consortium purchased an acquisition module.
We were certain there would be added staff time required to design and maintain the data-base, but it looked as if it would not be sufficient to distract us from our other responsibilities and there was no other way to get improved data manipulation. We decided the staff time to maintain the database would not be significantly greater than the time required to maintain the manual ledger.
We wanted the benefits an electronic ledger could provide,since data could be manipulated to generate reports useful for accounting, library planning, collection development, and collection maintenance.
Fortunately for us, exactly at the point where we
decided that PARADOX was the software we wished to use, the Hamline University
Computer Center also decided to purchase it and sup-port its use on campus!
As a result, we were able to buy Paradox at an educational and site/insti-tution
rate of $200, at a time when the retail price was $695; and we would have
ongoing training and support available outside our own small staff, should
we need it.
3. BACKGROUND: DATABASE MANAGEMENT SYSTEM SOFTWARE
Database management system (DBMS) software is available in two types:
• Flat-file programs and
• relational programs.
Relational database management programs have the ability to share and compare information from multiple database files, and they often have a programming language as well. With flat-file programs, on the other hand, one must reenter the data in every file that will use the data. In terms of applications, "...flat-file systems are intended for fairly simple applica-tions such as managing mailing lists, client lists, telephone directories, form letters and personnel files. The relational packages, meanwhile, are best suited for creating accounting systems, inven-tory-control systems and other types of big, complex data bases" (Dunn, p.45).
In relational databases, data is stored in tables.
Each cell can hold only one value. Each cell in a column must have the
same type of data-- e.g. text, integers, dates. One or more columns must
be designated as the "primary key". The key values of a record are used
to distinguish it from other records.
4. SOFTWARE SELECTION
Because we had decided that our primary use for the DBMS would be accounting, we wanted to buy a relational DBMS. After reviewing the available software, we chose a top-rated, state-of-the-art, relational database management software package called PARADOX. Infoworld has described PARADOX as "one of the highest-scoring products we've ever reviewed"; "a no-risk investment from novice to expert"; "best combination of speed, ease of use, and query power"; and "Paradox documentation is among the best we've seen" (Petreley, Jan. 1990, p.54, 67).
Query by Example is the method PARADOX uses to ask questions of the data that has been typed into the tables. As a side note for those who are more familiar with query languages, PARADOX is nonprocedural (based on relational calculus) and is an example-driven program (Query by Example).
PARADOX 3.0 requires an IBM PC, XT, AT, PS/2, or compatible; 512K of RAM; PC-
or MS-DOS 2.0 or later. A hard disk is recommended (supports both expanded and extended memory). It is also available on the OS/2 operating system. And a LAN pack for 5 additional users is available (Petreley, p.67).
We ran it successfully in-house on an IBM-compatible microcomputer, Zeos, with a 286 chip and a 33MB hard disk. (10 to 14 MB of the hard disk were actually used for Paradox. To have two years of data in the system, we anticipate needing 20 to 30MB, depending on how much bibliographic data we decide to enter.)
The response time to search a 1700-record table of invoice entries, retrieving only those en-tries that pertained to the last month of the fiscal year, took 5-10 minutes. Printing a ledger report two pages long took about 5-8 minutes. Printing a spreadsheet report for the month took 2-5 minutes. Printing a 19-page list of 975 ID numbers and corresponding titles took 30 minutes. (Printing was done on an Epson, letter-quality, dot matrix printer.)
We will be moving the PARADOX database to a Tower system with a 386 chip and an 80 MB hard disk. This will increase the response time and provide computer space for both the PARADOX accounting functions and a new stand-alone acquisition system.
6. DESIGN: ESSENTIAL END-PRODUCTS/REPORTS
The essential end products for us were: 1) monthly financial statements, indicating account balances, free balance, and cash balance, and 2) a ledger or voucher type printout that could be compared with the university's monthly printout of expenditures. In order to produce these re-ports, we decided to produce the following permanent and temporary tables.
7. DESIGN: BASIC COMPONENTS -- TABLES
Table 1 is the Table Structure used for the Library Accounting data:
• Materials Expense Table -- one record per title or part purchased. Data is entered as invoices are processed for payment or as funds are committed by the library for future payment to others. [Note: Initially we input one record per invoice, to save data entry time. Over time we will be entering one record per title or part since this will permit us to track prices per title over the course of time and project costs more effectively.] (Hamline's Name for Table: MATEXP)
• Operating Expense Table -- one record per item purchased or per service rendered. Data is entered as invoices are processed for payment or as funds are committed by the library for future payment to others. (Name: OPREXP)
• Income Table -- one record per payment or donation received. Data is entered as income is processed for deposit or as funds are committed by others for future payment to the library. (Name: INCOME)
• Bibliographic Information Table -- one record per bibliographic entity or cataloging title. Data is entered the first time an item is entered into the materials expense account, MATEXP. (Name: BIBDATA)
(Each month the new temporary table replaces the one generated during the previous month.)
• Encumbrances for Expenses -- At the end of every accounting period (usually at the end of each month), a table containing both anticipated expenses and expenses paid after the end of the accounting period is created from the permanent expense tables. (Names: MATENC and ENCUMB are created from MATEXP and OPREXP, respectively.)
• Anticipated Income -- At the end of every accounting period (usually at the end of each month), a table containing both anticipated income and income received after the end of the accounting period is created from the permanent income table. (Name: INCENC is created from INCOME.)
• Expenses for Current Accounting Period -- At the end of every accounting period, a table containing solely the expenses paid during the accounting period is created from the permanent expense tables. (Names: MATMO and MONTH are created from MATEXP and OPREXP, respectively.)
• Income for Current Accounting Period -- At the
end of every accounting period, a table con-taining solely the income received
during the accounting period is created from the permanent income table.
(Name: INCMO is created from INCOME.)
8. DESIGN: BASIC COMPONENTS -- REPORTS PREPARATION
A general outline of steps required in the preparation of the two essential, periodic reports is given below. Table 2 gives a summary of the Table Purposes for the application.
Monthly Financial Statement
• Use the permanent expense and income tables to create tables of outstanding encumbrances as of the day after the end of the current accounting period. (For example, if the accounting period report will cover July, search the materials expense table for items dated for payment from August forward.)
• Use the permanent expense and income tables to create tables of expenditures and income for the current accounting period. (For example, if the accounting period report will cover July, search the expense table for items paid for during July.)
• Create and printout the financial statement reports
for the current accounting period. [Report Fields/Columns: FUND#, FUND
NAME, EXPENDED, Y-T-D EXPENDITURES, Y-T-D CASH BALANCE, OUTSTANDING ENCUMBRANCES,
FREE BALANCE, CURRENT ALLOCATION, ORIGINAL ALLOCATION
During report design for expense and income reports, codes are placed in the report that tell Paradox to retrieve data from 4 different tables. In other words, 4 tables are "linked" to generate these reports. The reports will pull information from a combination of the following tables:
- One permanent expense or income table -- e.g. MATEXP
- One permanent budget table -- e.g. BEGBAL
- Two temporary expense or income tables containing data solely from the period under consideration (i.e. month, quarter, year) -- e.g. MATENC and MATMO
Monthly Ledger/Payment Record
• After the financial statements have been prepared for the current accounting period, manipulate each of the temporary expense and income tables to produce tables arranged like a ledger/payment record.
• Create and printout the ledger reports for the current accounting period. [Fields/Columns: FUND#, FUND NAME, DATE PAID, VENDOR, ID#, INVOICE DATE, INVOICE#, AMOUNT]
9. OTHER APPLICATIONS
The following are additional applications for PARADOX that we have identified.
Reports that could be generated using the above tables:
• Cancellations list arranged by location and call number and/or by fund account
• Total of number of active serial titles (total, by fund)
• List of ongoing serial titles
• List of ongoing serials titles by fund
• Serials cost history (year-by-year comparison; percent increase; by fund, by title, overall)
Applications requiring design of new tables:
• Equipment inventory
• Supplies inventory
• Generating circulation overdues and fines lists
• Serials processing workload--manipulate the daily
serials piece count to tally the number of pieces handled per month by
In the fall of 1989 we began to design a PARADOX
database that would produce an elec-tronic ledger and spreadsheets. By
June of 1990 we had entered into the database the bookkeeping data for
a full fiscal year, and we had generated monthly ledgers (by account) and
monthly and annual spreadsheets.
Dunn, Si, "Building Business with Data Bases", Computer User, (Nov. 1988): 1, 45-6.
Petreley, Nicholas, Zoreh Banapour and Linda Slovick, "Product Comparison: Analyzing Relational Databases," Infoworld, (January 8, 1990): 51-68.
"Relational Database Program," Software Digest Ratings Report, 4 (7): 1-12 (July 1987).
Warden, William H. and Bette M. Warden, "An Introduction to Database Management Systems," Library High Tech, Issue 7: 33-40.
Wong, Harry, "Relational Database Development: A
Primer for the Business Person," C, (Aug. 1989): 10-11, 18.