ph: +1 416-691-2516
info @ archimuse.com
published: March 2004
Data In, Data Out: Content Management for the Web
Christine Sonnabend Vitto, United States Holocaust Memorial Museum, USA
How do you create quality web sites from your database content? How can you utilize all of the information that your organization collects and generates? The Education Division of the United States Holocaust Memorial Museum, partnering with our technology experts and other divisions in the Museum, have created an integrated database system (HEDDA) that marries legacy databases with new front-end design, held together by middleware. This system has been designed with the web in mind, and is being used in many creative ways.
This paper will explore various web projects and the database creation process from a project manager's point of view. I will be using examples from our system, such as: 1) a database that was in a flat file format, then was converted to our integrated system and posted on our web site; 2) a database that allows the people whose information is stored in the database itself to edit and update their own records through the web; 3) a database containing data on Holocaust education in the United States that will be posted on our web site in a graphic, interactive format; and, 4) a database that tracks who gets the Museum's educational publications. Visitors to our web site can order materials through a simple web form, and our staff can send that record with one click into the database.
With these examples, I will be able to show how the back-end databases and web sites work together to solve problems for managers and eliminate duplicate efforts across our institution.
Keywords: databases, content management, integrated database system, HEDDA, middleware
When I started working in the Education division two and a half years ago, the staff kept track of only off-site programs, and they used paper index cards. Upon returning from a program, the staff member was supposed to fill out a card and leave it with the Deputy Director. Then when reports were given either monthly, quarterly, ad hoc or all of the above, someone would have to go through these cards and analyze the data for the reports. Other tracking mechanisms being used by the Division included paper records of how many visitors came to our Education Resource Center and got educational packets, plus an old Access database where names and addresses of people getting packets were stored.
When the Director of Education and I realized this, after receiving a request from Museum management to make reports of all educational programs, and to put state profiles data from our Access database onto the Museum's Web site, we decided that we could not continue with the paper-based system and different Access databases, and ever expect to get things done. The Director turned to me and gave me a new project: find a way to do get reports of our programs and place materials on our Web site. And that's how the concept of HEDDA (our integrated database system) was born.
HEDDA stands for Holocaust EDucation DAtabases. As I was looking at Education's content and needs, I realized that I could build separate systems and just deal with the immediate problems: 1) how to get accurate reports in a timely manner on Education's activities to Museum management; and 2) place the data from the State Profiles database (the state of Holocaust Education in the United States) onto the Museum's Web site; or I could do something that had short- and long-term benefits for the division's content management, and create a way link up all of the division's databases and share content with others in the division and across the Museum. I chose to go with the long-term solution, rather than fixing these problems piecemeal.
I am the Education Division's project/content manager, working directly with a database programmer, who was the technical project manager. These roles evolved over time, and while I stayed the overall HEDDA project manager, each database has a database manager who is the content expert. On the technical side, the head of Database Development took over the project management role from the programmer. My job was somewhat like middleware too: I was expected to meet with the content experts, help them decide what they wanted, write up technical specifications and drawings that both sides could read, and then bring the documents to the programmer. He would go through the documentation, ask questions or request more information, and I would get it from the content side. We chose to use this method of working because we had found that in the past, when the content people spoke to the technical people, we ended up with a lot of frustrated and angry people, and a product that did not meet our needs.
We created the ´Program Tracking´ database first, and gave the Education staff access so that they could record their programs and activities themselves. Then we created reports for internal and external use. We decided to do this database in Lotus Notes, since the Museum had recently purchased this software for our email system, and we were anxious to try out some of the other functionality.
Another way we decided to use the Lotus Notes databases was to create the access point through the Workspace area of Lotus Notes.
Figure 1: Screen shot of the Workspace icons in Lotus Notes.
Once the icon ´HEDDA on Dev1´ is selected, then the menu opens, and depending on an individual's rights within the database, they would see the choices and make a selection.
Figure 2: Screen shot of the HEDDA menu in Lotus Notes.
Not everyone can see the ´Documentation´ and ´Integrated Tests´ menu items, since these are for the programmer and myself to put our documents and to test functionality. Also, for the International Programs database, the database manager decided she would like to access her database through the Internet, as she had done in the past. She rarely uses the Lotus Notes entry point, although I insisted that we needed to have it. The colors and font of the menu were chosen specifically because we have a staff member who has vision impairment and he could not see anything in our old menu design.
Integrating the Web into HEDDA
Once we talked to the Museum´s web and technology staff, we realized that we would not be able to use our legacy databases (mostly in Microsoft Access) on the Museum Web site. We would have to either convert these legacy databases or create an interface to connect the databases to the Web site. That is when we developed the concept and reality of ´middleware´ or the ´middle tier.´ A definition of the middle tier is (see http://www.javaworld.com/javaworld/jw-03-1999/jw-03-middleware.html#sidebar1)
Figure 3: Diagram of the HEDDA middleware architecture.
One of the exciting things that HEDDA does is integrate database frameworks with each other to save on resources. We did this by assigning the authority to certain pick lists within the HEDDA framework. For example, if a database contains a field for the US states, then it references the original list of US states, and the authority resides in the State Profiles database. If any changes are made to this list, and this can only be done by authorized individuals, the changes are filtered down to the other databases that share the lists. All of the databases in the HEDDA system share the one list of US states, for example, and the users do not even know that this is happening.
Another example is the list of International States. The authority for this list resides with the Educational Programming database manager. She may decide to add, delete, or edit names of these states, and then the changes would show up in the other databases. No one makes any of these changes in a vacuum; however, because they would need to communicate with the other databases managers to make sure that no one's data would be adversely affected.
Figure 4: UML Diagram of the database structure for the International Programs database.
Another thing we did that made this system attractive to so many people in the Museum was allow for individualization in the look and feel of each database. For example, purple is the favorite color of the content manager of Educational Resources. It was a simple thing to create a purple background for her online resources section, so we did it. It may sound like a trivial matter, and please understand that functionality always came first, but if we could give desired functionality, plus choice of style, colors, and a fonts, then it was a sweeter deal, and more people were interested.
Visitor Services and Educational Resources Materials Tracking
The Education Division produces many educational publications and brochures, and we hand them out for free to the public. We also have created PDFs for most of the documents and any visitor to our Web site can download and print free of charge. While the Education Division responds to email, snail mail, phone and fax request, materials are also given out on-site at the Museum's Information Desk by Visitor Services staff, which is in a completely different division of the Museum. (We closed the Museum's Education Resource Center one year ago.) When I investigated further, it turned out that Education and Visitor Services tracked eight of the same publications, from the same warehouse, even though Education was responsible for printing. For example, Visitor Services may receive a new publication from Exhibitions to give out at the Information Desk, and Education would not necessarily need to know about it. Education may develop a new publication and ask that Visitor Services give it out at the Information Desk, and both divisions would need to track their usage. Without a shared system, each division would track their use separately, and then hopefully communicate with each other to make sure the numbers agree. But communication is not guaranteed, and errors can be made with two separate systems. Since we need to track how many materials we are using, both to be able to report on what we're doing and to be able to replace materials in a timely fashion, and Visitor Services needs to track the materials that it gives out (many of which are the same), I proposed to Visitor Services and Education staff that we share a tracking system in HEDDA. Both sides agreed.
Visitor Services already had a tracking system in Excel. While this worked well for them as an off-the-shelf product, they were excited about being able to use a more sophisticated database to retrieve data and create reports. We also designed a utility for both that would email the Operations staff in the Museum when it was time to order materials from the warehouse. straight from the database.
Education did not have a formal tracking system for educational materials, but a combination of paper and an Access database. When both sides were asked to come up with required fields, we found that they were very similar, and some were the same. One of the advantages of using a system like HEDDA, too, is that we could customize the look and functionality for Education and Visitor Services, and still connect the content in the back-end. So, what would look to an outsider like two completely different databases really was one set of data.
Figure 5: Screen shot of the Education Materials Tracking database.
Figure 6: Screen shot of the Visitor Services Materials Tracking database.
Another way that these two areas share data is when a new publication is added. We created a shared form, which is the same on both database systems, that requires each party to check with the other before proceeding. The fields are filled out then the form is routed to either or both databases, based on the answers to the questions: ´Is this material shared between Visitor Services and Ed Resources? (Please make sure to contact the other party before adding materials.)´ and ´If no, is this data for Visitor Services or for Ed Resources?´ We use check boxes to answer the questions, and if it is a shared resource, then the data can be viewed in both databases. If the material is not shared, then whichever box is checked -- Visitor Services or Ed Resources -- is where the data on the material can be viewed.
Figure 7: Screen shot of the Add Materials form.
International Programs Database
This particular database contains a list of Holocaust organizations nationally and internationally, and the content is posted on the Museum's web site. There are over 1,000 records stored in the database. The data is reliant on data entry of several individuals, some at the Holocaust Museum and some from the organizations themselves. The original database was designed as a flat file, and did not include pick lists or any form of standardization. Consequently, it was possible to have twenty different spellings of a word, and it was not possible to search the data. A different area of the Museum than Education owns the database, but it has a rich amount of content. When we started looking at the content, we wanted to be able to use their list to show Holocaust organizations in the 50 states. Why should we duplicate their efforts? Once we proposed this idea, we found out that the data could not be shared while it was in the current system, and it would have to be converted then updated. There was also considerable interest in other areas of the Museum to make sure that the content was accurate and ensuring that other areas of the Museum could benefit from it. Since, HEDDA was created and in progress, we decided it made sense to use the HEDDA model for this project as well.
Figure 8: Screen shot of back-end International Programs database data entry form.
The data is organized in a specific way in both the back-end and the front-end view of the database. You can search by state, country, or organization. Because both the International Programs data and the States Profiles data are connected by HEDDA, it has become much easier to standardize and to share data. Now, from the State Profiles database, we will be able to show one seamless list of Holocaust organizations by state when queried.
Mandel Fellowship Database
The Mandel Fellowship is a grant program run by the Education Division, which selects about fifteen teachers per year to come to the Museum and study, and then create a Holocaust project for their school or their community. These teachers must have generally taught the Holocaust for a number of years and be leaders in their communities. Originally, this program did not have a separate database or tracking system. When we decided to create HEDDA, we developed a database for this content in SQL, since we knew there was going to be a web component from the beginning. Since there are over 150 fellows, and the amount grows each year, and only one coordinator, we decided it would be easier to have each fellow update his or her entries themselves. So, after we designed the database itself, we designed a web form that would be available to all fellows with an Internet Explorer browser. It is password protected, and when a fellow selects his or her name from the list, then he/she inputs the password, and then only their content is drawn from the database to the web site. The fellow can then add new data or edit existing data, save the work, and then it will download into the database. The changes and additions are posted in real-time, so we can view the results of the data entry instantly.
Figure 9: Screen shot of back-end of Mandel Fellowship database data entry screen.
Figure 10: Screen shot of the Mandel Fellowship web data entry form
We have just started another project involving the data from the Mandel Fellowship database and the Museum's Web site. Right now, if you go to the Museum's Web site and view the Mandel Fellowship Web site, there is a list of who the fellows are for each year, their schools, and the titles of their projects. Since many of the fellows do have either Web sites, or some parts of their projects in electronic format, we are going to provide this information -- either a link or the actual content -- on the Museum's Web site. And it will be more than that because we are going to use the data in HEDDA: the fellow name, school address, school email, Fellowship Year, URL of Web site, Project Title, and a two-to-three sentence description of the project, written by the fellows themselves. All of this content can be entered or edited through the Web form we created for the database, by the fellows.
Figure 11: This is a diagram of how the Web site will pull data from the HEDDA database, and which fields will be pulled.
States Profiles Database
The States Profiles database contains information pertaining to Holocaust Education from each state in the United States. This data is in an older Access database that was created many years ago from a publication on Holocaust Education in the United States. We are trying to make our content accessible to the public as much as possible, and we have data from all 50 states in this database. Some examples of the data collected is: Holocaust organizations, Department of Education contacts for each state, Holocaust-related curricula, Holocaust education-related research, and legislation from each state relating to Holocaust education.
We knew that we could not place the Access database onto our Web site, but we did not wish to re-do all the previous work that had been done. If we had, the project would have taken much longer to complete, and would have added a lot of complexity as well. In hindsight, we probably should have just converted it to SQL, but the whole point of having HEDDA and the middleware was to avoid time-consuming and difficult database conversions, and to enable all of our legacy databases to talk to one another. So, we kept the Access database, and built middleware for the front and back-end to talk, and so that the data could go up onto the Museum's Web site.
The data is accessed in the back-end through a United States map, which I created in-house for internal use. Once we had the graphic, then I created an image map for the states, and gave it to the programmer, who connected it into the database. We also use a list of states in alphabetical order (including Washington, D.C.) on the left side of the screen.
Figure 12: Screen shot of United States map for States Profiles database.
Each section of data is on a tab. One of the tabs, called USHMM Programs is much like a report, and shows the functionality of the HEDDA integrated database system. This page pulls data from other HEDDA databases to show a report on the screen for each state. It pulls Mandel Fellowship data, such as the number and names of the fellows from each state; the number of entries from each state from the May Family National Art & Writing Contest by year (from Educational Resources database); the number of teacher packets sent to each state by year (from Educational Resources database); the number of school groups from each state by year (planned future integration into HEDDA); number of teachers attending the Arthur and Rochelle Belfer Conferences from each state by year (planned future integration into HEDDA), and much more.
For the Web site for States Profiles, we are going to create a new United States map, and allow users to access States' data directly through the map, or through a pick-list, much like the back-end. We are thinking of these data sets in terms of queries, so that when a user comes to the Web site, they may wish to see all of the Holocaust organizations in the state of North Carolina, plus the contact information for the North Carolina state Department of Education. The user will be able to select the data sets and then have the data displayed and formatted for printing, if desired. One thing we have not decided, however, is whether or not to make the changes to the back-end visible on the Web site in real-time, or have the front-end updated each evening at 1:00 am, for example.
Educational Resources Database
The Educational Resources database tracks information on programs and projects run by our National Outreach staff. They have a mailing list of every person who has received our free teacher packets, data and statistics for the May Family Art & Writing Contest, data on bulk orders of teaching materials, data on reference requests, and a section on materials tracking, to inventory all of our educational publications. The first section of the database, the mailing list, is our largest, and the most difficult to work with. It is an Access database, with approximately 40,000 records, but with many errors and duplicate entries. Also, before HEDDA, there were no pick lists and so standard data like a state name was entered many different ways. When we placed this database into the HEDDA framework, we had to do something about the state problem. We ended up creating a new field for state, so that there were two fields for state " old without a pick list and new with a pick list. We have someone who does our data entry, and she needs to go through record-by-record and transfer the data from the old to the new. Once this is complete, we will delete the old field.
We have created a way to avoid double data entry when we receive forms requesting educational materials. In the old system, when someone wished to order an educational packet online, he/she filled out the on-line form and submitted it. It then had to be printed out, and data entered into the Access database. Another programmer before HEDDA created a utility to move the forms into the database, but it was complicated with many steps, and the users were not happy with it. With HEDDA, we asked that there be a simplified process to get the forms from the web to the database, without any extra data entry or complicated steps.
We decided that the database manager needed to view the forms before they went into the database to weed out the joke forms or duplicates. It also was not a good idea, for security reasons, to connect the database directly to the web site. We created a new web page for the on-line form in JSP, which allowed us to send the data from the form to a new container within the HEDDA database framework. The container is called "Online Request Forms", and was created so that the database manager could view the data and approve forms to go directly into the Resource Request section of the Educational Resources database.
Figure 13: Screen shot of the Online Request Forms container from the Educational Resources database.
As you can see, the design allows the database manager to quickly check names, but she can click on any to see the rest of the details if she needs to do so. She can approve the names one by one, or approve a whole batch at a time. Best of all, it requires one click of the mouse, and no typing.
Figure 14: Screen shot of Educational Resources Resource Request data entry screen with Search.
As you can see from the examples above, our efforts to integrate some of our duplicate functions have saved staff considerable time and frustration. Also, by implementing common-sense database architecture, we have enabled content from our databases to reach our Web site, and for this to be done in a smart, efficient manner. It also makes sense to work with other parts of the Museum, especially when we are using the same data, and both sides can benefit from working together. Another by-product of this project has been to forge powerful and lasting partnerships with other staff and areas of the Museum, and hopefully these relationships will continue to benefit everyone in forthcoming projects.
What We Learned: A Reality Check
It was hoped that by using middleware technology we could eliminate the need to migrate legacy databases, thereby saving us time and money by allowing us to continue using the legacy systems. For a while, this looked like it would happen, and we were making plans to place other databases into the HEDDA framework, but then we hit a brick wall, literally and figuratively. The programmer that worked on this project suddenly left the institution, and there is no one here currently who can continue the work. After much debate and discussion with our technology personnel, it was decided that the current staff and the new hire behind our programmer would not be able to support the system the way it had been built. Consequently, we have had to make a decision to migrate all legacy databases to SQL, hire a staff member who knows SQL, plus other languages, and database programming, and re-do much of the work that has already been done.
I still think that our original concept of linking our databases and that of others within the Museum through an integrated database system is a sound conceptual ideal, and could be a reality. I have been told that the new programmer and existing staff will be working with me and the database managers to convert our legacy systems and try and get them up and running in the integrated system as planned. While I am very pleased to have this commitment from our staff, I also realize that we will have to go backwards and start over in many cases. It will be frustrating for the database managers who have already been through this and would like to see their systems working the same as before.
What have we learned from our mistakes, and why did this first attempt fail? I think that our biggest problem was not having institutional database and programming standards. Now I know to insist on creating and using such standards, in any other project of this magnitude. We also did not have a very good system of knowledge management, which means that in our case when someone left, there was no one else who could pick up the project and continue it."
I have learned a lot from this project. I have been able to learn valuable skills in database management and how to write and describe technical documentation. The documents I create today and worlds better than the first ones I did a year and a half ago, for example. I also know about some standards and criteria that Education itself needs to have to make a complete database system, such as separating out first and last name fields. I know that the next steps will be slower than before, but excellence cannot be rushed, and I am determined that we will have an integrated database system in the Education Division.
Staylor, Mills Wellons, III "HEDDA" 2002.
Staylor, Mills Wellons, III "HEDDA Architecture" 2001.
Staylor, Mills Wellons, III "SOAP Protocol" 2002.
Staylor, Mills Wellons, III "SOAP Protocol - DSAPI" 2002.
Staylor, Mills Wellons, III "SOAP Protocol - WSDL" 2002.