Relationships: Hard work

1
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
2
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,
ARTSTORARTSTOR_103_41822001026051
3
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.

4
Factors related to Cost/Benefits

• Before Project

• After Full Implementation

• 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 hodge-podge
  system to maintain both Subject Guide pages and
  database subscription information (some
  information public accessible, some staff only)
  less than ideal.

• 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.

5
Project Timeline
When working on projects, time is a much more
flexible dimension!
6
Initial Decisions

• ISS (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).

7
Database-driven Subject Guides
From this all design and content hardcoded
(2001)
8
Database-driven Subject Guides
To this CSS design, content hardcoded (2005)
9
Database-driven Subject Guides

• Through this evolving entity diagram
• (SQL Server 2005)

10
Database-driven Subject Guides

• SELECT TOP (100) PERCENT relatedversion.callnumber
  cutter, relatedversion.relatedversiontitle FROM
  relatedversion INNER JOIN relatedversion_subject_b
  ridgeON relatedversion.relatedversionid
  relatedversion_subject_bridge.relatedversionid
  INNER JOIN subject ON relatedversion_subject_brid
  ge.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_identif
  ier.accessinfoid electronic_resource.accessinfoi
  d 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 _at_erid) AND
  (subject.subjectid 24)

• Using these queries
• (or similar ones Visual Web Developer 2005)

11
Database-driven Subject Guides
To this CSS design with database-driven
content! (2007)
12
Contact Information Page

• Another section of the entity diagram.
• (again, SQL Server 2005)

13
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_librar
  y_bridge ON organization.orgid
  organization_library_bridge.orgid WHERE
  (organization.orgid 12)

14
Contact Information Page
Produces the Contact Information Page, in this
case, for EBSCO
15
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.

16
Lessons Learned

• Everything takes twice as long as you think it
  will.
• Background reading is essential, but theres 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.
  

17
Questions?
18
Thank You!

• Susan Henthorn
• CPO LIB
• Berea College
• Berea, KY 40404
• 869-985-3268

email susan_henthorn_at_berea.edu
library homepage http//www.berea.edu/hutchinslibrary/default.asp
Susans 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