The How and Why of Database-Driven Web sites:
a case study of the Mid-Hudson Library System’s
Reliance on the Internet as an information resource has grown tremendously during the last several years. Because of the wide expanse of information available, experiencing the vast reality of the Web can be quite disconcerting for occasional users, such as staff at small libraries or public library patrons. So staff at the Mid-Hudson Library System, a state-chartered entity charged with serving the needs of 65 public libraries (mostly small libraries) in a five-county area in the mid-Hudson Valley of New York State, worked hard to develop a Web resource the member libraries would find useful and relevant.
Once the staff gave the Web site an overhaul and developed a low maintenance way to handle the information the member libraries stated they needed, efforts turned towards enhancing the site with “bells and whistles”. One item high on both the member libraries’ and the staff’s list of improvements was a method to deal with the ever-changing content of the System directory. This resource listed all pertinent information about each member library—contact information, staff, hours, trustees, legislative districts, etc.—information perfect for retrieval from the Microsoft Access database that held it. The major obstacle was how to get that information out of Access and onto the site in a dynamic fashion so as to eliminate constant manual intervention to keep it up-to-date.
A previous iteration of the Web site had been totally database-driven, using FileMaker’s FileMaker Pro 4.1 as the database engine. There had been many problems with this approach, and Web Team discussions of how to implement another database project inevitably led back to the tribulations of the old FileMaker site. As Web Team Leader, I did not want to doom the project before it began, so I suggested the Team look for a project smaller than the directory with which we could test implementing database-driven content using our current Web tool, Macromedia’s Dreamweaver.
While the Team brainstormed for a possible test case, a staff member inquired about how we could mount a listing of successful but easy library programs on the Web site. Such a directory of “easy to do but successful library programs” seemed to be perfect for the test case. Further discussions clarified that the idea was to allow library staff to submit these programs and that this information (such as target group, category, and cost) would be searchable so staff at other libraries could locate and use it. A draft submission form was available, but it was an e-mail form that would require staff time to input the program information into any database of easy programs that was developed. The form had never been implemented.
I took on developing this project into our test case for database-driven content. The EZProgram project would require learning how to display, search and input records between an Access database and our Dreamweaver-based Web site. Once EZProgram was operational, the plan was to apply the developed process to our System directory and any other Access database content we wanted available on our site.
The first step was to purchase the latest and greatest version of Dreamweaver, UltraDev 4. This product came highly recommended from consultants Mid-Hudson was working with for other Web projects (Web-based training and children’s homework help). And since it was in the Dreamweaver family staff would be able to move to this version without much retraining.
Next came the research. I searched articles and scoured books on how best to implement EZProgram. I discovered that many books purporting to cover getting Access database information onto a Web site were either written for someone with considerable more database experience than I (and thus were practically incomprehensible to me) or contained instructions on how to publish a static HTML page using tools available in Access rather than generating dynamic pages. My frustration grew each time I searched Amazon.com or wandered in Barnes and Noble; I was convinced somewhere there had to be the information I needed but I just wasn’t successful in locating it.
Then I found it, what I considered my Holy Grail—Foundation Dreamweaver UltraDev 4. Aimed at beginning Web designers, the book contains basic information on using UltraDev including how to generate dynamic database-driven Web page content. A terrific supportive Web site included forums where users and the authors posted information, problems and solutions.
Leaving research behind me the realm of database design loomed. As I waited for the EZProgram Input Form Committee to reach a consensus, I tried to use the information from the Book to develop a prototype. Our Web server set-up required the use of Active Server Pages (ASP), so as I developed the site in Dreamweaver I made the appropriate selection when needed. A download of Microsoft’s Personal Web Server (PWS) is crucial to the testbed, so that you can see if what you have developed with the database and UltraDev actually works. [If you are running Windows 2000 or Windows XP Professional (NOT XP Home Edition) you should install IIS instead of PWS]. Next was the ODBC set-up (ODBC is a programming interface that allows applications to access data in database management systems that use SQL—structured query language; you can configure this aspect in UltraDev or separately in Windows). Then I set up the Access database. I guessed on the data elements based on the original e-mail input form. Following the instructions in the Book I created a display records page and a submit records page. I was able to successfully submit a record, so I knew that I was able to communicate between the Web page and my database. So in my testbed I tried what I considered the “real” test—displaying the records I had just submitted.
Errors. First I saw errors related to the ODBC set-up. The Macromedia Web site has some basic technical support information, with direct links to Microsoft’s support pages. But I was not able to find the exact error I was experiencing.
So I turned to the Book. I tried to take some of the examples from the CD-ROM and alter them for EZProgram, thinking that might be a quick and dirty way to solve the problems. But success continued to elude me. Since I was doing this testbed on my home PC, I thought perhaps a change of venue might help and brought everything to work and tried it there. But luck was not with me.
Next I turned to those great online forums for the Book. I did have some success in debugging the ODBC problem, but now I was generating VB (Visual Basic) script errors. Further research was unsuccessful in overcoming the problem I was experiencing.
I was already registered for the LITA Regional Institute on Database Driven Web sites that was coming to my area in a couple of weeks. I pinned my hopes on finding the information I needed to make EZProgram work at this Institute. During this interim time the EZProgram Input Form Committee finalized the data elements they wanted.
The Institute provided background and instructions in handling different ways of delivering database-driven content, and I learned I had missed a point or two in getting things set up. When I returned from the Institute I had the information I needed to design an Access database that would work well with UltraDev to input, display and retrieve information. I scrapped the prototype I had been working with to start fresh. I enhanced what I had done following the Book with what I had learned from the Institute, and this time when I tested there were no errors--things worked just fine.
The next hurdle was getting it to work on our Web site. The System Computer Administrator was not familiar with ODBC and ASP, and it took awhile to convince him that he needed to add an ODBC connection on the Web server in order for EZProgram to work. During these negotiations our Executive Director suggested using another machine to house the database, which isn’t a bad idea. But that would require adding an ODBC connection pointing to the database server, and that was one variable too many for the Administrator.
The first “live” test resulted in a VB script error. The Macromedia site had information on this particular error (the ODBC connection had not been set-up properly). Once this situation was corrected EZProgram worked like a dream. But it wasn’t too much to look at. I had concentrated on the functionality of input, retrieval and display. Now came the time to make the EZProgram pages look a little better until the System’s graphic designer could help with the look of the entire site.
So I developed an EZProgram Central page, where the user could choose to input or search. Other pages that I developed were a detailed results page, where all the details of a program display, and a successful input page, that displays a brief message that the input was successful (when it is) and gives the user options to enter another or go back to EZProgram Central.
I made changes to the Access database and the EZProgram Input form based on the EZProgram Input Form Committee’s input. Then I created a new input page. The display and EZProgram Central pages remained the same.
First staff at Mid-Hudson tested EZProgram. They entered test programs and displayed them to their satisfaction. Then Committee members were asked to do the same. EZProgram was tweeked to add some of the suggestions generated from the testing, and then readied for an unveiling to the entire Programs Committee, a group of member library directors and staff who had been angling for this EZProgram information for quite some time.
Heated discussions ensued on program categories and age ranges and whether the contributor’s contact information should display. The actual functionality of the site seemed to meet the Committee’s expectations. I agreed to implement the changes they suggested and then go through another round of testing.
During this test two requests were made---one for more sophisticated searching and another for keyword access. Our Youth Services Coordinator provided me with a list of keywords, which was easy to add to the Access database and the input form. But I wasn’t sure how to implement more sophisticated searching.
Another round of research was ahead. I found some of what I needed in the Book, but not everything. A visit to Borders & Books resulted in locating Inside Dreamweaver UltraDev4, which has an easy-to-understand chapter on developing search capabilities. I was successful in getting the search capabilities to work on my home testbed computer, but before I was able to upload the pages to the Web server for final testing I found myself downsized from Mid-Hudson Library System.
I am unsure as to the future of the EZProgram implementation and enhancements. By taking on this project I learned a great deal about database design in Access. I also learned the importance of good database design to implementing database-driven content on a Web site. I became familiar with UltraDev’s easy-to-use features in bringing dynamic content to a Web site. But as far as how the plan to apply the developed database process to our System directory and other Access database content succeeds for the Mid-Hudson site may remain a mystery. All I know is I regret not being able to follow-through on the EZProgram implementation.
Developing database-driven Web site content can be a challenge. A good knowledge of Access and Dreamweaver can help a novice navigate through the process. Also helpful is an understanding of ODBC, either by you or your Web server administrator. Dogged determination and good research skills will also aid anyone undertaking such a project. But the benefits of providing content through database-stored information outweighs any tribulations endured along the way.
Harris, Stuart. Dreamweaver UltraDev 4 for dummies. Foster City, CA : IDG Books, c2001. ISBN 0-7645-0797-4 : $24.99.
Nicholson, Sean. Inside Dreamweaver UltraDev 4. Indianapolis, IN : New Riders, c2002. ISBN 0-7357-1107-0 : $44.99.
Paddock, Rob and Spencer Steel. Foundation Dreamweaver UltraDev4 . Birmingham, UK : friends of ED, c2001. ISBN 1-903450-34-9 : $29.99.
Ray, John. SAMS teach yourself Dreamweaver UltraDev in 21 days. Indianapolis, IN : SAMS, c2001. ISBN 0-672-31901-2 : $39.99.
FAQs listing at Dreamweaver FAQ.com
Foundation Dreamweaver UltraDev book support site
Macromedia’s Dreamweaver UltraDev support site
Microsoft’s site for PWS download
(The Windows 98 CD contains PWS, in the NT4 Option Pack-- x:\add-ons\pws\setup.exe)
UltraDev 101 : Formatting and Repeating Records
UltraDevGuru.com: UltraDev Tutorials - Some useful links
Macromedia Dreamweaver UltraDev Technical Help Forum
LITA’s Regional Institute on Database-Driven Web sites, Albany, NY, July 2001 (regional institute information available at http://www.lita.org/institut/index.html).
December 23, 2001