Web-Enabled Databases for Everyone
Jonathan Maybaum, Professor of Pharmacology, University of Michigan Medical School Introduction UM.SiteMaker is a portable, platform-independent Java application developed by the University of Michigan (UM) that permits individuals to create and maintain highly customized websites and web databases, with little training or support. This article reviews UM.SiteMaker's history and major capabilities, emphasizing the "Data Tables" feature that allows users to create their own simple web databases without requiring programming or database administration skills. Who needs a web database? The motivation for developing UM.SiteMaker came from interviews conducted among UM Medical School faculty in 1998, to identify their most urgent IT needs. Two areas that were near the top of almost everyone's list were websites and databases. Although we expected that website-building would be a high priority, it was somewhat surprising to hear so many requests for help with databases. Based on these responses, we anticipated that the databases created in UM.SiteMaker would be primarily related to faculty research. However, as discussed below, both the range of users and the spectrum of uses for UM.SiteMaker databases have turned out to be far more diverse than we imagined. More recently, we conducted a brief survey among representatives of the university members of the Common Solutions Group (CSG, http://www.stonesoup.org), to determine if the demand for web-enabled databases that we saw at UM is also present elsewhere, and if so, how these other institutions are responding to this need. Two of the major findings of this survey were: • • Eleven of the twelve responding institutions indicated significant demand for help with databases, within their user communities The most common solution that is currently provided for user-initiated databases is provision of a MySQL/PHP service, with or without fee-for-service consultation
At UM, our experience with offering "a la carte" MySQL/PHP service has been that it is useful for a small number of technically experienced people, but that it is not helpful to most members of the community, who lack the technical knowledge to use it. This situation is consistent with the picture reported at the other CSG institutions, and we interpret it to reinforce our view that there exists in the academic world a significant unmet need for solutions that enable mainstream users to create and manage their own web-enabled databases. Does this mean that every single person in academia really wants to make a web database, and is capable of doing so? Well, perhaps not everyone (at least, not yet) but our goal was to be as inclusive as possible. One of our most important early design
decisions was to define the level of sophistication that would be needed in order for a person to make their own database. We concluded that someone who is comfortable working with simple spreadsheets should be able to grasp the concept of organizing a basic data structure (Data Table) into columns (fields) and rows (records). While this description excludes some users, it probably encompasses a majority of the academic user community, and it became the benchmark that we adopted. Divide and conquer Despite that fact that making a web-enabled database is a complex undertaking, we felt that it would be possible to break down the process into pieces that could each be managed by our target user group. We ultimately identified four major issues and devised approaches to address them: 1. Making a website Although it may sound like an obvious point, in order to make a web-enabled database, one must also make a website. There are several chores associated with constructing even the simplest website that can take a significant amount of time and effort for a person who does not routinely do this kind of work, such as: • designing page layouts, color schemes and graphic elements • ensuring effective and consistent navigation • authentication and access control The first two versions of UM.SiteMaker were designed to facilitate the template-driven construction of customized websites, including support for each of the items above, as illustrated by the example in Fig. 1. This work provided a platform that was extended in version 3, to include user-defined databases.
Fig. 1: A UM.SiteMaker website as viewed by a visitor (left) or by the site owner, in Configuration mode (right)
Page 2 of 8
2. Database administration The mechanics of defining database table structures vary widely from one system to another, but in every case it is necessary that the user be able to create tables and specify the columns or fields in each table. In a conventional setting, this would require that the user be given an account with administrative privileges for the database system and learn how to log into that account and operate the interface (or enter SQL commands) to accomplish these tasks. In our situation, users are presumed to have no experience in database design and to have no knowledge of SQL or any other command-line language for database manipulation. Therefore, it is important to provide a simple graphical interface and to allow for great flexibility in re-working table structures (e.g., dropping or renaming tables or fields), since they will be learning about database design as they go. Our most important innovation in this area was to implement Data Tables as abstract objects, rather than having them correspond to specific tables in an underlying relational database. This made it somewhat easier to create a flexible user interface for manipulating data structures (Fig. 2), and also ensured that the application is not dependent on a specific database technology.
Fig. 2: Defining a Data Table in UM.SiteMaker
3. Permissions Shared databases are typically designed so that each user has an appropriate level of access to data. For example, the owner would be able to view and edit all data, whereas some data might by publicly viewable (in read-only form) and other data might be editable by specific individuals or groups. Organizing these permissions can be a headache, even for experienced programmers, and it was a significant challenge to give inexperienced users a way to manage permissions effectively. A key concept in our approach is that each audience for a particular dataset (i.e., Data Table) will view that dataset through its own instance of a special type of page, called a "Data Access" section. Fig. 3 shows an example of a weblog built using UM.SiteMaker Data Tables. This website contains pages for each of three distinct audiences to view the weblog:
Page 3 of 8
• "View Weblog" (left) is accessible by anyone, and displays all of the weblog entries in read-only format, sorted in reverse chronological order • "Add/Edit Items" (right) is accessible only by members of an access group called Invited Bloggers, and permits those users to add new items and to edit or delete items that they have created (but not items belonging to other Invited Bloggers) • "Weblog Admin" (not pictured) is accessible only by the site owner, and permits all actions on all items
Fig. 3: A UM.SiteMaker weblog as viewed by the public (left) or Invited Bloggers (right)
Each of these three Data Access sections is, in essence, a complete database front end. Although they all point to the same data source (the Data Table shown in Fig. 2), each one is configured to suit the intended audience. For example, Fig. 4 shows some of the settings for the "Add/Edit Items" page used by the Invited Bloggers. In this area, users are allowed to Browse, Search, Add and Delete records (A), although the set of records that each user sees is limited to the ones that they created themselves, according to the conditions set in the Default Search filter (B).
Fig. 4: Configuration settings for the Data Access section used by Invited Bloggers, to add, edit and delete their own items
Page 4 of 8
4. Forms The basic operations of adding, finding, viewing and editing records in a database require forms that are predictable in some cases, but that may require extensive customization in other cases. Creating these forms is a tedious (but obligatory) step that adds a considerable amount of overhead to the process of making a conventional web database. We reduced this burden substantially by including in each Data Access section a set of pre-built forms. Fig. 5 shows the tab for configuring the Single Record form in our weblog example. The site owner can decide whether a particular field should be shown, whether or not it is editable in this context, and how it should be formatted and ordered. Users who are familiar with HTML may customize the forms much more extensively (under the Custom Layouts tab, details not shown).
Fig. 5: Configuration of a Single Record of a Single Record form in a UM.SiteMaker Data Access section
Outcomes: Never underestimate the ingenuity of your community... As noted above, our original thought was that Data Tables would be used largely by faculty for research-related purposes. This has indeed been the case, to some extent, as in the website shown in Fig. 6A, where Dr. Tom Kerppola used a Data Table to organize his lab's publications. Another popular use of Data Tables by students and faculty that we anticipated is creation of a customized event calendar for seminars or other presentations, as in Fig. 6B. However, in addition these uses, we have been surprised by the number of simple applications that many local staff members have built using Data Tables. There have been several cases where small conferences were organized in this way, including acceptance of abstracts and registration forms, as in Fig. 6C. Also, some staff members have built directories for their local units, as in Fig. 6D.
Page 5 of 8
Fig. 6A: Research Publications Database
Fig. 6B: Seminar Schedule
Fig. 6C: Meeting Registration
Fig. 6D: Student Directory
Other novel incarnations of Data Tables include image gallery, office hours signup, service request form, equipment inventory, online grade posting and exam archive. Although Data Tables have been used by users of many backgrounds and descriptions, our most interesting observation was that the people who seem to be most significantly empowered by Data Tables are local IT support staff. These people are knowledgeable about their users' needs, and are generally computer-savvy, but do not posses highlevel programming skills. For these staff, UM.SiteMaker serves as a platform for rapidly and inexpensively building highly customized, lightweight web applications. What is UM.SiteMaker's place in the world? Considering how many web-publishing systems exist in academia, it is important to make clear what distinguishes UM.SiteMaker from the rest. Simply put, it is this: Local Empowerment What service is provided at your institution for individual faculty, students and staff to make professional looking, functional, highly customized websites, to express their creative output? The answer is usually "space on a conventional web server", or some minor variation thereof. We all realize, however, that most academic citizens do not have the skill, time or resources to build effective websites in that environment. What about other existing academic web applications? While many universities have course management system, portals, and enterprise content management systems,
Page 6 of 8
none of these adequately addresses the requirements of non-technical individuals who need to make highly customized websites. In contrast, this is precisely the kind of need that UM.SiteMaker was designed to meet. As discussed earlier in this article, the issue of creation of web-databases by individuals is similar to the situation that exists for web-databases, i.e., the usual "solution" is to offer subscription to a service (PHP/MySQL) that is too complicated to be useful to anyone except a small minority of technical specialists. As a result, construction of even the simplest web-database application ordinarily requires anywhere from days to months worth of effort, with a typical cost in the thousands of dollars. The Data Tables feature in UM.SiteMaker changes this equation dramatically, by permitting a much broader segment of the academic community to build their own applications. One of the ultimate goals of empowering mainstream users in this way is to change a basic premise of academic software development. Currently, many open source projects have programming interfaces (APIs) that permit skilled programmers to write modules of code that add new functionality to a core application. While this is a sound practice in terms of software architecture, it implies that the module's author will be able to include enough flexibility so that users can customize its functionality to meet their needs. The problem is that programmers are unlikely to be able to anticipate all of the creative ways that a scholar might want to use any particular module. Although the module could be revised over time to add more and more configuration options, this will make it increasingly more complex and difficult to use. Furthermore, cost of customization in this development model, in terms of time and dollars, is substantial. Instead, we want to place the power to customize the functionality (not just the cosmetics) of academic software as close to individual academic users as possible, either by making it simple enough for them to master themselves, or to enable their local, non-programmer support staff to assist them. Our initial experiences with UM.SiteMaker Data Tables give hope that this kind of local empowerment is indeed possible. Current status and future plans During the 2.5 years that UM.SiteMaker has been available as a university-wide production service, it has exceeded our expectations in terms of its rate of adoption and the purposes for which it is being used. There are currently over 2300 websites published at UM using this system, with participation from over 100 units (schools, colleges, departments, centers, institutes, etc.) and traffic has been doubling every 6-8 months. During the early stages of the UM.SiteMaker project, we made the judgment that it would be risky to depend on central university funding to support development indefinitely. After considering various alternatives, we decided to pursue a licensing strategy, and we negotiated a contract with Global Village Consulting (GVC, Vancouver, BC) to become our principal developer and exclusive licensee. GVC has recently begun marketing the system under the name "GVC.SiteMaker", and has signed up both K-12 and non-profit organizations as clients. In order to attract other colleges and universities to our user community, GVC offers two types of licenses:
Page 7 of 8
1. Educational institutions that are willing to make a significant commitment to enhance the system will be granted a license to deploy it for their users, and will be given access to source code (with restrictions concerning its distribution and commercial use) in exchange for their contribution. This is, in essence, a type of "community source license". 2. Institutions that do not wish to participate in development may purchase a deployment license, which does not include source code access. The fee for this license is non-recurring for any particular version of the software, although subsequent updates would require a license renewal. We believe that this model is both equitable and sustainable. It permits larger or more technically capable institutions to make local modifications as they see fit, and to join in partnerships with other like-minded community members to collaborate on feature development. At the same time, it provides a funding model that allows organizations with modest resources to obtain reliable support of thoroughly tested and documented releases of the application, at a reasonable cost. The direction of future SiteMaker development will be determined jointly by UM, GVC and other institutions that choose to participate. Some of the items that are under consideration are: - Extending the relational aspects of Data Tables. Currently, Data Tables are mostly flat structures, with a limited relational character. We would like to extend their relational capabilities to the degree that this is possible while still maintaining a relatively simple user interface. - WYSIWYG Editing. This enhancement would supply the option of using a word processor-like interface for creating formatted pages. There are a number of candidate technologies that might be considered for this purpose, although many of them are slow or buggy, or are dependent on users having a specific platform or web browser. - Enhancement of file handling. We are considering the idea of allowing access to UM.SiteMaker files through a WebDAV interface, as well as implementing other improvements for handling large numbers of uploaded files. Organizations that are interested in SiteMaker development or deployment should contact Global Village Consulting: info@gvcsitemaker.com
Page 8 of 8