Transcript OHSU - Orbis Cascade Alliance

“Descent into Chaos”
Figuring out
database-driven
technology for OHSU
Library ejournals
Laura Zeigen, Sara Piasecki and Diane Carroll
OHSU Library
May, 2002
Online Northwest 2001



Database-driven Web page demo
Decided we needed this functionality for
access to full text articles at OHSU
How hard could it be?
The Need in 2001





26 alphabetical Static Web pages for 1500 journals
A second alphabet for titles in aggregator collections
(EBSCOhost).
Hard to maintain.
Hard for users to find information.
– Lots of scrolling.
– Not efficient.
Lack of flexibility (lists by provider, subject, etc).
The Need for 2002




Business Source Premier =1500 titles
ScienceDirect/Elsevier = 1200 titles (Trial)
ProQuest Medical collection = 225 titles
Database driven technology would allow access to
all full text titles subscribed to by the library.
The Challenge

No process yet defined at OHSU for developing
database-driven Web sites.

Not even any infrastructure in place for this!

Detective work
–
–
Started to ask around and was referred to 10 different people,
none of whom knew what the others were doing.
Started collaborating with a Web Manager from another
department on the same quest, as well as (KEY!) one of the
database administrators from our IT department.
Progress

Our primary criteria was that the system
developed had to address two crucial issues:
–
It had to have a reasonable learning curve for our
Web Managers, the people in departments across
campus, people who had widely divergent skill sets.
–
It had to be something that our IT department could
support or afford, if it was something that did not
currently exist in the infrastructure.
Progress (2)

IT department database administrator and
other Web Manager divided up working on a
handful of prototypes to see what technology
might best be suited to our environment:
–
–
–
–
PHP, an open source option
ASP, Microsoft’s product
ColdFusion with ORACLE, for which we have a site
license
ColdFusion with SQL server
Our own ejournals project


Once these IT decisions had been made, I worked with
the content/database source developed by Sara and
Diane to present this information on Web pages in a
variety of ways.
I used NOTEPAD to create my .cfm files
–

I wanted to eliminate the vagaries of different programs while
we were developing.
You could also use
–
–
–
Macromedia DreamWeaver UltraDev
Macromedia ColdFusion Studio
Editor “HomeSite”
Greatest Challenges
What we learned

Trying to program the conditional logic so our
OHSU workstations, OGI workstations, and
Off-campus links would show up properly as
being available or not was the hardest part of
the whole process.
What we learned

Become familiar with SQL (“Structured Query
Language”) and the basics of ColdFusion. It
will help you interpret the weird error messages
ColdFusion will throw up during the
development process and therefore make it
easier to troubleshoot where the issue may be.
What we learned (3)

Find a friendly and helpful IT person to help. If the IT
database administrator had not helped us through this
we would still be using static pages today!

The learning curve, even for the “easiest” option of
ColdFusion, was not that easy because there was no
one to guide us and no process yet in place.
–
Lobby your institution to put a dbdriven Web functionality in
place and this may help you for the future.
Example of the code and the
pages/records it brings up

E-journals front page
–

Code for this page
Search results page for search on “diabetes”
–
–
Code for this page
Code for conditional logic
Now that we have the technology…


How to account for all the necessary data, and
only the necessary data?
How to store the data to make it easy for
ColdFusion to query?
Evolution of a spreadsheet

Some of the necessary information had already
been gathered into an Excel spreadsheet:
–
–
–
–
Journal title
Provider
URLs (both on-campus, and through the off-campus
proxy server)
Dates of coverage
But we also needed…




URLs for OGI Science & Engineering Library
An additional column to indicate whether the
title was OGI-specific
Short description field (e.g., “Free to all”)
Special instructions for access (e.g.,
“Username and password required”)
And Cold Fusion and SQL
required…


ID field, a unique number generated by the
SQL Server software
An ‘altTitle’ column, to work around the inability
to search special characters through
ColdFusion
And while we were at it…




‘trialTitle’ field, to indicate which titles were free
for a limited time
‘dateAdded’ field, so lists of recently added
titles can be generated
‘topic’ field, to group titles by subject
‘suppress’ field, to hide entries from public view
The Initial Process


Once we had decided on a general table
structure, an Excel spreadsheet mimicking the
SQL table was created
This spreadsheet was sent to our IT contact,
who imported the data into SQL
Refinements

Technical:
–

some columns in the SQL table needed to be
resized to accommodate longer fields
Conceptual:
–
–
–
Patrons did not like the suppress feature!
Proliferation of topics clutters the main page
Use of embedded HTML to allow users to access
some websites directly (passwords, free content)
And a staff-side view under
development

Laura developed a second web interface for
staff-only use:
–
–
–
–
Provider now a searchable field
The addition of library “right” and “restriction” agreed
to upon executing the license
Paper or electronic Interlibrary Loans Allowed?
Can title be used in eRes or course packs?
Relative ease of maintenance

Add new entries (rows):
–
–



In batch via DTS from a prepared spreadsheet
Singly, through SQL insert commands
Global editing of whole fields (e.g., all Synergy
titles temporarily unavailable)
No delay in mounting changes to the Web
BUT, changes all require SQL commands
The Wish List





Add ISSNs, to allow for dynamic searching via Ovid
Online
Merge entries for single journals available through
multiple providers in web display
Add print holdings for journals also available
electronically
Ability to search multiple topics, or topic/keyword
And the top wish: more staff time to devote to
developing this resource!
Resources

SQL
–

SQLcourse.com, http://www.sqlcourse.com
ColdFusion (soon to be ColdFusion MX)
–
–
http://www.macromedia.com/software/coldfusion/
Macromedia ColdFusion Support Center

–
http://www.macromedia.com/support/coldfusion/
Macromedia ColdFusion Online Forum

http://webforums.macromedia.com/coldfusion/
Conclusion




This is what we did
It was harder than we thought
Good luck!
Questions?
–
–
–
Diane at [email protected]
Sara at [email protected]
Laura at [email protected]