RELATIONSHIPS: HARD WORK

roomagitatedInternet και Εφαρμογές Web

8 Δεκ 2013 (πριν από 3 χρόνια και 6 μήνες)

77 εμφανίσεις

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


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.


Before Project…

After Full Implementation…

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

susan_henthorn@berea.edu

library homepage

http://www.berea.edu/hutchinslibrary/default.asp

Susan’s homepage

h瑴瀺⼯L慣u汴y⹢敲敡⹥摵.h敮瑨潲n猯

PowerPoint

http://faculty.berea.edu/henthorns/ACA_presentation.ppt

handout/bibliography

http://faculty.berea.edu/henthorns/ACA_bibliography.pdf