Transcript Relationships: Hard work but worth the effort!

RELATIONSHIPS: HARD WORK
… BUT WORTH THE EFFORT!
An Example of a Homegrown Relational Database Used
to Tame the Electronic Resources “Beast”
Susan K. Henthorn
2007 ACA Summit, 20 October 2007
Why get involved, anyway?
Those who are enamored of practice
without theory are like
a pilot who goes into a ship without
a rudder or compass and never has
any certainty where he is going.
Practice should always be based
upon
a sound knowledge of theory.
- Leonardo DaVinci
DaVinci self-portrait, The Image Gallery, ARTSTOR:
ARTSTOR_103_41822001026051
Sabbatical Goal
To design and develop a database that would serve two primary
purposes:
 Provide information for database-driven web pages on the
library's website
 Allow for more systematic management of the college's electronic
resource subscriptions

Studying database theory would also provide a more solid
background for the author's work as the Systems Administrator for
the online catalog system, an Oracle database queried via SQL
and CGI scripts.
Factors related to Cost/Benefits
Before Project…



Web Content Management system might be
implemented in the future, but not soon.
Turnkey ERM system cost prohibitive, given
benefits (i.e., contract/license information not
available for many consortial subscriptions).
Time and transparency for current ‘hodgepodge’ system to maintain both Subject Guide
pages and database subscription information
(some information public accessible, some staff
only) less than ideal.
After Full Implementation…



Subject Liaisons will maintain content for specific
pages, with immediate/live updating a prime
benefit.
Subscription information (maintenance windows,
links to help documents, etc.) will be live for
patrons at point of access, on demand,
eliminating the need to either ‘splash’ it on the
library home page or unnecessarily clutter other
pages.
Staff will have access to current, up-to-date
technical contact information and other
subscription details without needing to consult the
Electronic Resources Librarian.
Project Timeline
When working on projects, time is a much more flexible dimension!
Initial Decisions
 IS&S (IT) decision regarding web server prescribed
our selection of Microsoft SQL Server 2005 for
database architecture.
 Consultation with Web Programmer led to use of
Microsoft Visual Web Developer 2005 for search
query and web page development.
 Reference staff decided what information should be
available via the web and to whom (public and/or
staff).
Database-driven Subject Guides
From this – all
design and content
hardcoded (2001)
Database-driven Subject Guides
To this – CSS
design, content
hardcoded (2005)
Database-driven Subject Guides
Through this
evolving
entity
diagram…
(SQL Server 2005)
Database-driven Subject Guides

Using these
queries …
(or similar ones –
Visual Web
Developer 2005)


SELECT TOP (100) PERCENT relatedversion.callnumbercutter, relatedversion.relatedversiontitle
FROM relatedversion INNER JOIN relatedversion_subject_bridge
ON relatedversion.relatedversionid = relatedversion_subject_bridge.relatedversionid
INNER JOIN subject ON relatedversion_subject_bridge.subjectid = subject.subjectid
WHERE (subject.subjectid = 24) ORDER BY relatedversion_subject_bridge.weightingfactor
SELECT electronic_resource.ertitle, access_information_identifier.primaryaccessuri,
access_information_identifier.primarylocationind, access_information_identifier.altlocationind,
access_information_identifier.proxyprefix + access_information_identifier.primaryaccessuri AS Expr1,
electronic_resource.erid
FROM access_information_identifier INNER JOIN electronic_resource
ON access_information_identifier.accessinfoid = electronic_resource.accessinfoid
INNER JOIN er_subject_bridge ON electronic_resource.erid = er_subject_bridge.erid
INNER JOIN subject ON er_subject_bridge.subjectid = subject.subjectid
WHERE (subject.subjectid = 24) ORDER BY er_subject_bridge.weightingfactor
SELECT description.description FROM subject INNER JOIN er_subject_bridge
ON subject.subjectid = er_subject_bridge.subjectid INNER JOIN electronic_resource
ON er_subject_bridge.erid = electronic_resource.erid INNER JOIN description
ON er_subject_bridge.descriptionid = description.descriptionid
WHERE (er_subject_bridge.erid = @erid) AND (subject.subjectid = 24)
Database-driven Subject Guides
To this – CSS design with
database-driven content!
(2007)
Contact Information Page
Another
section of the
entity
diagram….
(again, SQL
Server 2005)
Contact Information Page
Using this
query…
(again, Visual
Web Developer
2005)

SELECT contact.contactname, contact.contacttitle, contact.contactaddress,
contact.contactemail,
contact.contactphone, contact.contactfax,
organization_library_bridge.accountidassigned, organization.orgname,
organization.orgaltname, organization.orgaddress
FROM contact
INNER JOIN organization
ON contact.orgid = organization.orgid
INNER JOIN organization_library_bridge
ON organization.orgid = organization_library_bridge.orgid WHERE
(organization.orgid = 12)
Contact Information Page
Produces the Contact Information Page, in this case, for EBSCO
Resource Maintenance
This piece of the project is still a work in progress. The process uses multiple tables, pulling field
labels from some, field weighting from another, and additional editable data from yet
others. Because of the complexity of this piece of the process, it has been left until last in the
project. Implementation should happen by December.
Here is an outline of how the process will be designed to work:

Library liaison for particular subject determines changes need to be made on subject guide page.

Liaison clicks on ‘Edit’ button in top right corner of page.

Liaison is authenticated to make changes via network login and is taken to new web page
containing data in editable form.

Liaison makes corrections, additions and deletions as necessary.

New data is immediately reflected in subject guide.
Lessons Learned
Everything takes twice as long as you think it will.
 Background reading is essential, but there’s no substitute
for launching into the project software.
 Entity diagrams can take over your life (and your brain)!
 There is an art to developing effective queries.
 The final product is seldom final for very long.

Questions?
Thank You!
Susan Henthorn
CPO LIB
Berea College
Berea, KY 40404
869-985-3268
email
[email protected]
library homepage
http://www.berea.edu/hutchinslibrary/default.asp
Susan’s homepage
http://faculty.berea.edu/henthorns/
PowerPoint
http://faculty.berea.edu/henthorns/ACA_presentation.ppt
handout/bibliography
http://faculty.berea.edu/henthorns/ACA_bibliography.pdf