Statistics Design Document
Purpose
The Statistics application was designed to make collecting and reporting library statistics of all kinds easier and more effective.
Application Architecture
This is a two-tiered application consisting of the presentation and data layers. The application consists of two parts, library-facing portions of web pages which display and collect the statistics, and administrative web pages used by a UL administrator to manage the format and structure of the statistics collected. No pages are available to the public.
The presentation layer uses HTML and PHP code to present web pages to the user.
The data layer uses PHP and SQL code to store and retrieve resource information in the database.
No business logic or rules have been implemented, so that middle-tier does not exist in this application.
Application Design
User specifications are available in Word format for the instruction statistics. The background and justification for this project may be found in this whitepaper. This is a more complex design than actually needed for the initial implementation for instruction statistics, but the requirement to make it extensible and flexible to accomodate other types of yet unspecified statistics complicated the design.
Administrative Pages
The administrative pages allow one to define the nature of the statistics to be gathered. Currently only instruction statistics parameters have been defined to the application. Each new type of statistics also requires that a programmer create the data collection web page to gather that information.
Authentication and authorization for these pages are accomplished through the htaccess mechanism provided by the web server. Add, update, and delete functionality is provided. Editing a record is done by selecting the record to edit from a pop-up list created when one clicks on the "Get Record to Update" button on the page. Selecting a record to update automatically fills in the update form and places Update and Delete buttons at the bottom of the page. The Clear button presents an input form for a new record with an Add button at the bottom.
Library Input Page(s)
The library input pages allow librarians to enter statistics to be gathered. Currently only instruction and reference statistics parameters have been defined to the application. Each new type of statistics also requires that a programmer create the data collection web page to gather that information.
Authentication and authorization for these pages are accomplished through the htaccess mechanism provided by the web server. Add, update, and delete functionality is provided. Editing a record is done by selecting the record to edit from a pop-up list created when one clicks on the "Get Record to Update" button on the page. Selecting a record to update automatically fills in the update form and places Update and Delete buttons at the bottom of the page. The Clear button presents an input form for a new record with an Add button at the bottom.
Programs
/includes
connect.php |
Does the connection to mySQL database. |
constants.php |
Defines application constants used in all other programs. Change it once in this file, and it changes everywhere. |
lib.php |
Contains function library. |
jscripts.php |
Contains javascript code to be included on every page. |
submissionButtons.php |
Contains the submit buttons at the bottom of every page. |
/admin
admin-menu.php |
Defines the HTML administrative menu that shows up at the top of every admin program. |
departmentForm.php |
Contains the HTML for the department editing form. |
departmentManager.php |
Contains the code to do add/update/delete processing for the department form. |
fieldHelpForm.php |
Contains the HTML for the field help editing form. |
fieldHelpManager.php |
Contains the code to do add/update/delete processing for the field help form. |
getRecord.php |
Generic pop-up window program to get a record to edit. |
| getRecordbyType.php | Generic pop-up window program to get a record to edit, but passes the grouptype in to limit results to only the group's defined values for whatever record we are looking at. |
groupDefinitionForm.php |
Contains the HTML for the group definition editing form. |
groupDefinitionManager.php |
Contains the code to do add/update/delete processing for the group definition form. |
index.php |
Main menu, includes some basic help information. |
locationTypeForm.php |
Contains the HTML for the location type editing form. |
locationTypeManager.php |
Contains the code to do add/update/delete processing for the location type form. |
methodForm.php |
Contains the HTML for the method editing form. |
methodManager.php |
Contains the code to do add/update/delete processing for the method form. |
organizationForm.php |
Contains the HTML for the organization editing form. |
organizationManager.php |
Contains the code to do add/update/delete processing for the organization form. |
orgTypeForm.php |
Contains the HTML for the organization type editing form. |
orgTypeManager.php |
Contains the code to do add/update/delete processing for the organization type form. |
personForm.php |
Contains the HTML for the person editing form. |
personManager.php |
Contains the code to do add/update/delete processing for the person form. |
roomTypeForm.php |
Contains the HTML for the room type editing form. |
roomTypeManager.php |
Contains the code to do add/update/delete processing for the room type form. |
| subjectForm.php | Contains the HTML for the subject editing form. |
| subjectManager.php | Contains the code to do add/update/delete processing for the subject form. |
/event
getEventRecord.php |
This is a pop-up window for selecting the event record to modify. |
eventForm.php |
This defines the outreach/event form for data entry. |
eventManager.php |
Does the add, update, and delete of outreach statistics records for the event form. |
instruction
getInstructionRecord.php |
This is a pop-up window for selecting the instruction record to modify. |
instructionForm-subset.php |
This is a section of the instruction form. |
instructionForm.php |
This defines the instruction form for data entry. |
instructionManager.php |
Does the add, update, and delete of instruction statistics records for the instruction form. |
/reference
getReferenceRecord.php |
This is a pop-up window for selecting the reference record to modify. |
referenceForm.php |
This defines the reference form for adding new records. |
referenceManager.php |
Does the add of reference statistics records for the reference form. |
| definitions.php | Contains reference-specific definitions of terms. No programming lives in this file. |
| index.php | Initial location-selection page. |
| referenceUpdateForm.php | This defines the reference form for add, update, and delete of records. |
| referenceUpdateManager.php | Does the add, update, and delete of reference statistics records for the referenceUpdateManager form. |
/
lookupCourse.php |
This is a pop-up window for looking up UNM course numbers and titles. |
lookupInstructor.php |
This is a pop-up window for looking up instructor names. |
lookupOrganization |
This is a pop-up window for looking up organization names. |
showHelp.php |
This is a pop-up window that displays customized field help. |
/stylesheets
admin.css |
Very basic style sheet for the administrative applications. |
input.css |
Controls the look of the statistcs input forms. |
Database structure
The database resides in mySQL and is named webNews.
Tables
affiliation |
Not used. Intended to be a join table between person and organization. |
department |
Contains UNM academic department codes and titles as used in the course catalog. |
| event | Contains outreach/event details. |
| eventPerson | Contains the people associated with each outreach/event. |
fieldHelp |
Contains the customized field help information for each record and field on a screen. |
groupDefinition |
Contains high level grouping descriptions for each type of statistics kept. |
groupType |
Contains the highest level identification of a type of statistics. This allows each of the other tables to be used to describe ways of breaking down information specific to the type of statistics being gathered. For example, locationType used in instruction statistics might reference different types of classroom learning situations, whereas in reference statistics it might be broken down by physical reference desk locations. The locationType table has a field called groupType in it, so that the appropriate locationType records can be stored for each type of statistic independently. Initially there was only one record in this table to define instruction statistics. |
instruction |
Contains the master instruction statistics record. |
instructionMethod |
Join table between instruction table and method table. User subsequently removed the requirement to store instruction methods, so this table is currently unused. The relevant code is only commented out however, in case minds are changed. |
instructionPerson |
Join table between instruction table and person table. Stores all persons associated with the instruction event. |
locationType |
Contains location breakdowns relevant to the type of statistic described. |
method |
Contains methodology breakdowns relevant to the type of statistic described. |
organization |
Contains specific organization breakdowns relevant to the type of statistic described. |
organizationType |
Contains generic organization description breakdowns relevant to the type of statistic described. |
person |
Contains all contact information for any person described in the tables. |
| reference | Contains the reference statistics master record. |
| role | Contains the defined roles for the people involved in events. |
roomType |
Contains room type breakdowns relevant to the type of statistic described. |
| subject | Contains definitions of subject areas being tracked, ie American Studies, Art History, etc |
Table Definitions
Key field
Foreign key
affiliation
Field Name |
Description |
Definition |
Options |
|
Unique record identifier, auto incremented number, no meaningful value |
int(11) |
NOT NULL auto_increment |
|
Record identifier of person |
int(11) |
NOT NULL default 0 |
|
Record identifier of organization |
int(11) |
NOT NULL default 0 |
updateUser |
Username of person who last updated the record |
varchar |
default NULL |
updateDate |
Timestamp of last update |
timestamp |
default sysdate |
department
Field Name |
Description |
Definition |
Options |
|
Unique record identifier, auto incremented number, no meaningful value |
int(11) |
NOT NULL auto_increment |
description |
Department name |
varchar(255) |
NULL |
code |
Department code |
varchar(255) |
NULL |
|
Type of instruction statistic with which this record is meant to be used. |
int(11) |
NOT NULL default 0 |
updateDate |
Date and time record was updated. |
timestamp |
default sysdate |
updateUser |
Htaccess userid who updated the record most recently. |
varchar(20) |
default NULL |
event
Field Name |
Description |
Definition |
Options |
|
Unique record identifier, auto incremented number, no meaningful value |
int(11) |
NOT NULL auto_increment |
|
Id of type of instruction, defined in groupDefinition record. |
int(11) |
NOT NULL |
|
Id of type of statistic with which this record is meant to be used. |
int(11) |
NOT NULL |
|
Id of associated (methodType) activity |
int(11) |
default NULL |
|
link to UNM department code, if UNM course. |
int(11) |
default NULL |
title |
Event title |
varchar(255) |
default NULL |
individualName |
Name of individual if type of statistic is of individual nature. |
varchar(255) |
default NULL |
startDate |
Date instruction event started |
datetime |
default NULL |
endDate |
Date instruction event ended |
datetime |
default NULL |
startTime |
Time instruction event started |
time |
default NULL |
endTime |
Time instruction event ended |
time |
default NULL |
prepTime |
Not used - moved to eventPerson |
varchar(255) |
default NULL |
numberParticipants |
Number of participants in event |
smallint(6) |
default 0 |
notes |
Notes |
varchar(255) |
default NULL |
|
Id of location type used for event |
int(11) |
default NULL |
| locationDesc | Freeform description of the location | varchar(255) | default NULL |
updateUser |
Htaccess userid who updated the record most recently. |
varchar(20) |
default NULL |
updateDate |
Date and time record was updated. |
timestamp |
default sysdate |
eventPerson
Field Name |
Description |
Definition |
Options |
|
Unique record identifier, auto incremented number, no meaningful value. |
int(11) |
NOT NULL auto_increment |
|
Id of associated event. |
int(11) |
NOT NULL |
|
Id of person associated with the event. |
int(11) |
NOT NULL |
|
Id of role the person played in the event. |
int(11) |
default NULL |
hours |
Hours the person participated in the event |
decimal |
default 0 |
travelHours |
Hours the person spent travelling to and from the event |
decimal |
default 0 |
prepHours |
Hours the person spent preparing for the event |
decimal |
default 0 |
updateUser |
Htaccess userid who updated the record most recently. |
varchar(20) |
default NULL |
updateDate |
Date and time record was updated. |
timestamp |
default sysdate |
fieldHelp
Field Name |
Description |
Definition |
Options |
|
Unique record identifier, auto incremented number, no meaningful value |
int(11) |
NOT NULL auto_increment |
recordName |
System record name containing the field being described |
varchar(255) |
NOT NULL |
fieldName |
System field name of the field being described |
varchar(255) |
NOT NULL |
description |
Short description of field, displayed as rollover text on the webpage |
varchar(255) |
NOT NULL |
longDescription |
Unlimited length text for more detailed field help, which is displayed in a pop-up window upon clicking on the field label on the webpage |
text |
|
updateDate |
Date and time record was updated. |
timestamp |
default sysdate |
updateUser |
Htaccess userid who updated the record most recently. |
varchar(20) |
default NULL |
groupDefinition
Field Name |
Description |
Definition |
Options |
|
Unique record identifier, auto incremented number, no meaningful value |
int(11) |
NOT NULL auto_increment |
|
Type of statistic with which this record is meant to be used. |
int(11) |
NOT NULL default 0 |
groupCode |
Short code to describe the group, used for programming purposes only. |
varchar(255) |
NOT NULL |
description |
Short description of the group. |
varchar(255) |
NOT NULL |
longDescription |
More detailed description if required. |
text |
|
updateDate |
Date and time record was updated. |
timestamp |
default sysdate |
updateUser |
Htaccess userid who updated the record most recently. |
varchar(20) |
default NULL |
groupType
Field Name |
Description |
Definition |
Options |
|
Unique record identifier, auto incremented number, no meaningful value |
int(11) |
NOT NULL auto_increment |
description |
Short description of the group. |
varchar(255) |
NOT NULL |
longDescription |
More detailed description if required. |
text |
|
recordType |
Contains the database record name which corresponds to this group of statistics |
varchar(255) |
|
instruction
Field Name |
Description |
Definition |
Options |
|
Unique record identifier, auto incremented number, no meaningful value |
int(11) |
NOT NULL auto_increment |
|
Id of type of instruction, defined in groupDefinition record. |
int(11) |
NOT NULL |
|
Id of type of statistic with which this record is meant to be used. |
int(11) |
NOT NULL |
|
Id of organization associated with this instruction event, if any. |
int(11) |
default NULL |
courseNumber |
UNM course catalog course number, if any. |
varchar(255) |
default NULL |
departmentId |
link to UNM department code, if UNM course. |
int(11) |
default NULL |
courseTitle |
UNM course title, if any. |
varchar(255) |
default NULL |
individualName |
Name of individual if type of statistic is of individual nature. |
varchar(255) |
default NULL |
startDate |
Date instruction event started |
datetime |
default NULL |
endDate |
Date instruction event ended |
datetime |
default NULL |
startTime |
Time instruction event started |
time |
default NULL |
endTime |
Time instruction event ended |
time |
default NULL |
prepTime |
Amount of time to prepare for event |
varchar(255) |
default NULL |
numberParticipants |
Number of participants in event |
smallint(6) |
default 0 |
subjectArea |
Subject of instruction |
varchar(255) |
default NULL |
|
Id of location type used for event |
int(11) |
default NULL |
|
Id of room type used for event |
int(11) |
default NULL |
updateUser |
Htaccess userid who updated the record most recently. |
varchar(20) |
default NULL |
updateDate |
Date and time record was updated. |
timestamp |
default sysdate |
instructionMethod
Field Name |
Description |
Definition |
Options |
|
Unique record identifier, auto incremented number, no meaningful value |
int(11) |
NOT NULL auto_increment |
|
Id of method type used for this instruction method. |
int(11) |
NOT NULL default 0 |
|
Id of instruction record |
int(11) |
NOT NULL default 0 |
updateDate |
Date and time record was updated. |
timestamp |
default sysdate |
updateUser |
Htaccess userid who updated the record most recently. |
varchar(20) |
default NULL |
instructionPerson
Field Name |
Description |
Definition |
Options |
|
Unique record identifier, auto incremented number, no meaningful value |
int(11) |
NOT NULL auto_increment |
|
Id of person associated with this instruction event. |
int(11) |
NOT NULL default 0 |
|
Id of instruction record |
int(11) |
NOT NULL default 0 |
| fieldIdentifier | 0 for non-employee, 1 for employee | tinyint(4) | default 0 |
| organizationId | Id of organization for which the person is acting | int(11) | default 0 |
updateDate |
Date and time record was updated. |
timestamp |
default sysdate |
updateUser |
Htaccess userid who updated the record most recently. |
varchar(20) |
default NULL |
locationType
Field Name |
Description |
Definition |
Options |
|
Unique record identifier, auto incremented number, no meaningful value |
int(11) |
NOT NULL auto_increment |
description |
Location description |
varchar(255) |
NOT NULL default ' ' |
longDescription |
Longer location description |
text |
|
|
Id of type of instruction statistic with which this record is meant to be used. |
int(11) |
NOT NULL default 0 |
updateDate |
Date and time record was updated. |
timestamp |
default sysdate |
updateUser |
Htaccess userid who updated the record most recently. |
varchar(20) |
default NULL |
method
Field Name |
Description |
Definition |
Options |
|
Unique record identifier, auto incremented number, no meaningful value |
int(11) |
NOT NULL auto_increment |
description |
Description of method used with this statistic. |
varchar(255) |
NOT NULL |
longDescription |
More detailed description. |
text |
|
|
Id of type of instruction statistic with which this record is meant to be used. |
int(11) |
NOT NULL default 0 |
updateDate |
Date and time record was updated. |
timestamp |
default sysdate |
updateUser |
Htaccess userid who updated the record most recently. |
varchar(20) |
default NULL |
organization
Field Name |
Description |
Definition |
Options |
|
Unique record identifier, auto incremented number, no meaningful value |
int(11) |
NOT NULL auto_increment |
orgName |
Organization name |
varchar(255) |
NOT NULL default '' |
UNMorg |
Indicates whether the organization is with UNM or offsite. |
tinyint(4) |
default NULL |
orgType |
Id of organization type |
tinyint(4) |
default NULL |
updateDate |
Date and time record was updated. |
timestamp |
default sysdate |
updateUser |
Htaccess userid who updated the record most recently. |
varchar(20) |
default NULL |
organizationType
Field Name |
Description |
Definition |
Options |
|
Unique record identifier, auto incremented number, no meaningful value |
int(11) |
NOT NULL auto_increment |
description |
Description of type of organization |
varchar(255) |
NOT NULL default '' |
updateDate |
Date and time record was updated. |
timestamp |
default sysdate |
updateUser |
Htaccess userid who updated the record most recently. |
varchar(20) |
default NULL |
person
Field Name |
Description |
Definition |
Options |
|
Unique record identifier, auto incremented number, no meaningful value |
int(11) |
NOT NULL auto_increment |
lastName |
Person's last name |
varchar(255) |
NOT NULL default '' |
firstName |
Person's first name |
varchar(255) |
NOT NULL default '' |
telephone |
Person's primary telephone number |
varchar(255) |
default NULL |
Person's email address |
varchar(255) |
default NULL |
|
note |
Notes regarding this person |
text |
|
prefix |
Person's prefix |
varchar(255) |
default NULL |
suffix |
Person's suffix |
varchar(255) |
default NULL |
employeeId |
UNM employee id |
varchar(255) |
default 0 |
chatId1 |
Person's main chatname |
varchar(255) |
default NULL |
personType |
UNM employee or Not |
char(1) |
default 'N' |
updateDate |
Date and time record was updated. |
timestamp |
default sysdate |
updateUser |
Htaccess userid who updated the record most recently. |
varchar(20) |
default NULL |
reference
Field Name |
Description |
Definition |
Options |
|
Unique record identifier, auto incremented number, no meaningful value |
int(11) |
NOT NULL auto_increment |
| personId |
The id from person table that matches this netid, if there was a match. Zero if no match. | int(11) default 0 | |
| notes | Free-form notes regarding the reference event. | text | |
| methodId |
Method used to provide reference services | int(11) | |
| locationTypeId |
Location at which the reference services were provided | int(11) | |
| netId | UNM netid of the person providing the reference services | varchar(255) | |
| timeEntered | Timestamp record was created | datetime | |
| subjectId |
Subject categorization of reference event. | int(11) | |
| locationSubjectId |
Location-specific categorization of reference event | int(11) | |
| groupId |
Reference category as defined by ARL | int(11) | |
|
Id of type of statistic with which this record is meant to be used. |
int(11) |
NOT NULL |
updateDate |
Date and time record was updated. |
timestamp |
default sysdate |
updateUser |
Htaccess userid who updated the record most recently. |
varchar(20) |
default NULL |
role
Field Name |
Description |
Definition |
Options |
|
Unique record identifier, auto incremented number, no meaningful value |
int(11) |
NOT NULL auto_increment |
description |
Description of type of role |
varchar(255) |
NOT NULL default '' |
longDescription |
More detailed description |
text |
|
|
Id of type of statistic with which this record is meant to be used. |
int(11) |
NOT NULL |
| rank | Rank order of records for display - overrides alpha listing if wanted | int(11) | default 0 |
updateDate |
Date and time record was updated. |
timestamp |
default sysdate |
updateUser |
Htaccess userid who updated the record most recently. |
varchar(20) |
default NULL |
roomType
Field Name |
Description |
Definition |
Options |
|
Unique record identifier, auto incremented number, no meaningful value |
int(11) |
NOT NULL auto_increment |
description |
Description of type of room |
varchar(255) |
NOT NULL default '' |
longDescription |
More detailed description |
text |
|
|
Id of type of statistic with which this record is meant to be used. |
int(11) |
NOT NULL |
updateDate |
Date and time record was updated. |
timestamp |
default sysdate |
updateUser |
Htaccess userid who updated the record most recently. |
varchar(20) |
default NULL |
subject
Field Name |
Description |
Definition |
Options |
|
Unique record identifier, auto incremented number, no meaningful value |
int(11) |
NOT NULL auto_increment |
description |
Description of subject |
varchar(255) |
NOT NULL default '' |
|
Id of the location for which this subject is applicable. |
int(11) |
default 0 |
|
Id of type of statistic with which this record is meant to be used. |
int(11) |
NOT NULL |
| rank | Rank order of records for display - overrides alpha listing if wanted | int(11) | default 0 |
updateDate |
Date and time record was updated. |
timestamp |
default sysdate |
updateUser |
Htaccess userid who updated the record most recently. |
varchar(20) |
default NULL |
Physical implementation
Database
Platform: mysql
Host: libmysql.unm.edu
Name: statistics
Userid:
Passwords available from LIT
Web Servers
Platform: Apache web server
Host: elibrary.unm.edu
Directories: /var/www/html/applications/libraryStatistics
Host: libintra.unm.edu
Directories: /var/www/html/intranet/Main/Statistics
Patron access: None.
URLs
Administration by Library group designees
http://elibrary.unm.edu/applications/libraryStatistics/admin/
Application Input
http://elibrary.unm.edu/applications/libraryStatistics/
Reports
http://libintra.unm.edu.libproxy.unm.edu:8080/Main/Statistics/
Browsers
Web browsers designed for are Internet Explorer 6.x and Netscape 7.x/Mozilla.
Password Administration
Passwords to the administrative pages are created through the htpasswd command on the web server. The htpasswd and htgroup files are in the /var/www/html/ directory.
Author: Sue Swanback, October 9, 2008