From Database to Web Site. Transforming a PC Relational Database to a World Wide Web Resource
by
Jane A. Keefer, West Chester University
West Chester, PA 19380
jkeefer@wcupa.edu

Introduction. This presentation describes and discusses some of the many issues and concerns that arise in the creation of a web site (http://www.mse.jhu.edu/research/folkindex) from a relational database developed on a standalone personal computer. A brief history of the database, an index to folk music recordings (a.k.a. the Folk Music Index), is described below and some of basic issues involved in the process are examined. As in all technology dependent processes, the specific time at which the various phases of development are undertaken generally has a major influence in the implementation of access, display and the structure of the resource. It is quite probable that the current project would be different if initiated even two years earlier or later.

History and background. The Folk Music Index had its beginnings in the late 1970s. As a teacher of traditional music styles on banjo, guitar, fiddle and dulcimer, I was constantly in need of both written and recorded sources for the music that my students wanted to learn. Started originally on old-fashioned 4x6 cards, I began indexing both recordings, magazines and books in my personal collection. By 1986 my card index had grown to nearly 12 file drawers of cards, at which point the index stopped, as I returned to school to pursue my current profession as a librarian.

In 1990, with the purchase of a shiny new 386 PC and the acquisition of some incredible bargains and rarities as the LP gave way to the CD, I decided to put my index into a database, starting with the recordings, for which indexes are much harder to come by. Over the next few years, many key folk recordings of the sixties and earlier were added, as more and more vinyl albums found their way into used record stores and local record shows.

In 1995, with the emergence of the World Wide Web and the development and expansion of library resources onto the web, I was encouraged by my colleagues at the Milton S. Eisenhower Library (MSEL) of The Johns Hopkins University to mount my local PC database onto the library's web server. After determining that there were no similar offerings on the web at that time, it was felt that providing discographic information for traditional and folk material would represent a major service to the community of folk musicians and researchers.

Maureen Beck, current MSEL webmaster, assigned a student assistant, Paul Yamashita, to help me and from our initial conversations the idea of providing active hypertext links between cross referenced titles emerged. We also worked out the initial browse only access and static file design for the site. Arrangements with the Library Systems department were made and in late 1995, the first edition of the index was mounted and made available as a link on the MSEL music resources page.

Now in it's 5th edition update, the index currently includes a search engine, frame based browsing, and additional links to publisher resources. Although I am no longer employed at Johns Hopkins, the Eisenhower library has generously continued to support the index fully and statistics indicate that it is one of the most actively used resources on Milton's Web. On behalf of the folk community, I extend my most sincere thanks for this support.

The PC Database Setup. Both hardware and software determine the parameters of database design. The initial hardware for the database was a generic 25Mhz 386 machine with an 80Meg hard drive and 8 Meg RAM. Currently the database resides on a Digital Starion Pentium 75Mhz system with a 1 Gig hard drive and 32 Meg RAM.

The software selected to create and maintain the database is Alpha4, originally v. 2.1 for MS-DOS and now currently at v.6. Of all the software available at the start of this project in 1990, Alpha4 combined exceptional ease of use with a conceptually straight forward way to develop a fully relational design for the index and at a price that to this day remains very competitive. Its dbf file format meant that it would be compatible with a wide variety of other software and its ability to write scripts, applications and complex reports were important factors in making the transition to the world wide web a relatively straightforward proposition. The addition of new material to the database is simplified considerably by the extensive lookup features built into the software. Music titles, performer names or composers, once entered, need never be entered again.

Currently the size of the files residing on the PC hard drive constitute about 50Meg with about 35 Meg corresponding to the content (dbf) and index(ndx) files. The html files generated by the report writer for the web site total about 25 Meg. The website in it's entirety is created and tested on the local PC before being sent via ftp to the MSEL server for public access.

PC Database Structure. Utilizing the relational capabilities of the Alpha4 software, the current design of the database consists of six content files each with unique primary keys and three link files with compound primary key derived from the content files. These files include:

Content Files (primary key name in parenthesis)

  1. Music Title - 26,600 records (MuCode)
  2. Performer Name - 12,100 records (PfCode)
  3. Composer - 4,200 records (CrCode)
  4. Sources - 2,500 records (SrCode)
  5. Publishers - 570 records (PubCode)
  6. Instrument/Tunings - 125 records (InCode)/140 records (TuCode)
Link files (unique compound keys constructed from the above primary keys above plus the source location field, SrLoc)
  1. Performances - 51,100 records (MuCode, PfCode, SrCode, SrLoc)
  2. Side Performers - 12,550 records (PfCode, SrCode)
  3. Tuning citations - 1740 records (TuCode, SrCode, SrLoc)
  4. Instrumental performances - 20,300 records (SrCode, SrLoc)

World Wide Website Structure. The WWW protocol was originally developed to provide easy access to static files which contained hypertext links both internally and to external files. Thus the transference of the relational data structure to a file based system required some modifications to the file definitions contained in the PC software. Given this file based nature of the web, it was natural to present the indexed information in a standard discographic layout. Since file size has an impact on web access transfer times, some preliminary estimates were made of how many records in this format would constitute a typical file size of 30-50K. Using these estimates, unique file names were assigned to specific alphabetic spans for both the performer and title dbf files. A separate database file was created to record this file name information and insure that newly added records would be assigned the correct file name within the A to Z sequence.

Listed below are the various static file types that now make up the web site along with the DOS file size ranges they span.

Once the discographic file name parameters are established, active hypertext linking is available between all performers and titles simply by using the primary key for each record as the value of the HTML Anchor Name tag. Since each primary key is unique, the correct reference is assured and this device illustrates the functionality of the relational file structure as an early form of hypertext technology, much as the card catalog with its main and added entries can now be seen as a crude effort at relational data structuring.

The discographic files for performers and titles with the appropriate HTML markup tags are created using the Alpha4 report writer facility which contains powerful grouping facilities as well as providing extensive calculated fields and displays. These files are then transmitted to the MSEL website via ftp from my local PC.

The local PC-based database thus remains the primary information source and recordings are continually being added to the index. Updates to the MSEL website are done about twice a year and currently the site is in it's fifth edition or update. A typical update can take 3 or 4 hours to send from the local PC via modem to the JHU website.

Web Site Design. Just as the HTML markup language has evolved since it's initial beginnings in the mid 90's, so has the design of the Folk Music Index website. The first edition opened with a simple table of contents pointing to the separate discographic title and performer files. Each of these files had, in turn, links to return the user to the two table of contents or to the MSEL music home page. Although users are presumed to have some knowledge in advance to locate specific title or performer information, humanities databases with their many variations in titles, spelling, and names, have always relied on extensive cross referencing along with easily browsable indexes. In folk music where one title may be attached to many songs or melodies and a single song or tune may be referred to by many titles, such cross referencing is critical. About 15% of the titles in the index are cross references, and all cross references are have active hypertext links.

In late1996 a colleague at MSEL, Jim Farrugia (now at OVID systems), designed a custom search engine for the web site. At the same time I decided to implement a frames based browsing interface that could also be integrated with the keyword search engine. Users would now simply move to any alphabetic letter to browse quickly up and down a list of titles or performers for that letter. Clicking on any one entry in the list frame would bring up the corresponding web file discographic entry in the main frame. The search engine query form also functions within the context of frames. Results for either a performer or title query are displayed as a browsable list with entries hotlinked to the appropriate full discographic file. All words entered by the user are "anded" and automatic truncation is assumed.

Logs of all searches are provided and as in most retrieval settings, the search terms entered by users are highly varied. Distribution among users follows the standard Bradford form with a few users being responsible for many searches and most users searching only once or twice. Statistics on the daily usage show a steadily increasing usage over the past year. A peak was reached in September 1998 with an average of 62 hits/day. Daily usage is also quite variable ranging from less than 10 to more than 100 a day. At present the daily average is in the high 50's. When compared to the overall usage of the MSEL website, the Folk Music Index generally is the second or third most requested directory of the entire site, constituting some12 to 25 percent of the total library website requests.

Current and Future Considerations. Currently the model used to develop the Folk Music Index web site is being applied to local projects at West Chester University (WCU). An index to a major historical WCU text developed by the Special Collections librarian has now been converted into a relational database structure and a prototype web file has been developed for access on the library's web site. Similarly, a graduate student in Geology is now developing a database based on the model above for purposes of creating a website documenting the history of women in Geology.

Some ideas under consideration for the future enhancement of the database and website, include:

In conclusion, this project demonstrates that relational databases have much to offer in terms of creating static hypertext linked files based on the primary keys within the database. Many relational databases now offer web pack add-ons to create html files based on search queries. These queries, often SQL based, are not always suited to the less precise type of information found in the Humanities. Even in non SQL based systems it is not apparent yet that both the browsing and keyword features are readily available for web site implementation. Nevertheless, one potential future approach to be considered is to merge the database and the web site, so as to provide access to the latest indexing at all times.

Poster session presentation, ACRL Conference, Detroit, MI, April 9, 1999