9. Database-driven design
9.1 When do you need it
Very often, we come across many cases like the following in our teaching practices:
1) There are too many teaching materials to handle on an individual basis. For example, over the years, we may have collected several hundred multiple-choice questions. Now we want to be able to have quick access to the large collection based on some criteria from a web browser;
2) In a web-based online test, we want to give each student a set of test items that are presented in different order;
3) When our websites become big, we want to give our webpages a structure so that when there is a need to update, we can modify a single place so that other pages will be updated automatically.
What is common across the three scenarios above is the concept of data structure, i.e., we want to organize the data (whether they are multiple-choice questions or webpages, etc.) in a structured way so that we can access them in a convenient way. This is where a database comes into play.
Before we move on, we need to answer the question: 'I am a novice computer user and don't know computer programming at all. Can I use database in my web-based project?'.
The answer is YES. Even if you don't have time to learn database technology very well, there are many publishing tools available that have made the life of database publishing easier. In this section, we will show you how it can be done. First, let us talk very briefly about the basic concepts in database applications.
9.2 Database: An introduction
When we talk about database, we are talking about structured data, i.e., organizing data in a meaningful (whatever that is) way so that they can be queried based on a certain set of rules. The most common data structure we use is relational. Most of us are familiar with this type of structure already. For example, we often use addressbooks in our favorite email program to keep track of the many associates we come across in daily life. The following is a tiny portion of a fictional email addressbook:
Serial number Firstname Lastname 1 nobody@example.com Jun Da 2 postmaster@example.com Jun Da 3 help@example.com Jun Da
To put it in simplified terms, organizing data into one or more meaningful tables constitutes a relational database. That is, a database can contain one or many more of such tables. In the above table, there are four columns and three rows (excluding the first row which are labels of the columns). In computer jargon, field is often used to refer to columns and record to rows.
Notice that in the above table, the email column contains unique email addresses. We can use this unique value to identify each record. In this case, we can assign the so-called 'key' to the 'email' column. If there are more than one keys in a table, we often assign a primary key to one of the columns which contain unique values in each row.
If we want to search for some information from a database that is stored electronically, we often use a set of query language called Structured Query Language or (more often) SQL. There is an excellent online SQL tutorial by James Hoffman. If you want to deal with database for your web-based project (in whatever level), it is suggest that you study that tutorial.
In a database query, we often perform four standard operations: Select, Insert, Update and Delete.
Of course, there are many more query options available in addition to the fours standard operations.
9.3 Database applications
There are many database software available. The most powerful ones are database server such as Oracle, Microsoft SQL server and MySQL. However, most language teachers will not have to deal with these kinds of so-called enterprise database servers, since they require considerable computer knowledge and skills to deal with. Instead, in a classroom-based environment, many of us can use desktop database applications. The two most popular ones are Microsoft Access and FileMaker Pro. The former is a desktop database application running on the Windows platform, whereas the latter is often used by Mac users. The major difference between an enterprise database server (such as Oracle) and a desktop database application (such as Access) is their data handling capabilities. An Oracle database server, for example, can handle thousands upon thousands of tables, whereas Access will only be able to handle a limited number of tables, each of which with 50 thousands of records for acceptable performance. There are many other differences between them but they all share two common features: 1) They organize data using a relational database structure; 2) They use the same set of SQL (with minor variations) for data mining.
9.4 Web and database integration
Many universities in the United States publish their directory information on line. An example is the University of Texas's online directory where you can search for someone's phone number, email address, etc. if you know his/her name. Such a directory is made available online with a web server as its frontend and a database server at its backend.
An example of publishing interactive language instruction and learning materials online is my Chinese Character Frequency List, where you can search for frequency information about a particular character in eight frequency lists.
9.4.1 How they are integrated
I will use my Chinese Frequency List database as an example. The following figure gives a brief illustration how database is integrated with the web server for publication:
To search for frequency information about a particular character, one uses the search form to enter Chinese character into the web form. After he clicks on the Submit button, the information is passed on to the web server. The web server then activates a certain (server-side) script to query the database for frequency information. Once the database finds the information, it passes back to the web server which in turn passes it on to the web browser.
Note that a web server and database server are two different pieces of software. They are 'glued' together by such scripting languages as PHP and VisualBasic, etc..
9.4.2 Off-the-shelf options
To publish database-driven webpages is not as difficult as one might have thought. For language learning materials development, two options are available which require minimal programming and system support. What is required is that you have to know how to create tables and enter data into those applications and that you have access to the necessary system resources. (Such a task is not difficult at all and can be learned within a very short period of time.)
9.4.2.1 FileMaker Pro
FileMaker Pro is a database application that comes in two versions: Desktop version and Server version. Both versions can run on either the Windows or MacOS platform. We will talk about the Desktop version here.
FileMaker Pro comes in with built-in web publishing options where a table can be displayed, searched and entered/deleted by changing some configurations on a certain table.
(Placeholder: a sample illustration)
9.4.2.2 Microsoft FrontPage
Frontpage2000 provides an easy way to publish Access database online. To use this option, the following system requirement should be met:
- Microsoft Access2000: You use it to create tables and enter/modify data;
- Microsoft FrontPage 2000: You use it to create webpages which enable database publishing;
- Microsoft FrontPage 2000 Server Extensions, which is installed on Microsoft Internet Information Server (Version 4);
- An account on the web server where the FrontPage Server Extensions is installed and a so-called FrontPage subweb has been created for you.
Server administrators are expected to install the FrontPage Server Extensions on the web server and set up the account for you. Once you have access to these resources, to publish your database on the web is only a few clicks away.
(Placeholder: sample configuration and illustration of creating such a table.
9.4.3 Home-grown solutions
There are times when off-the-shelf solutions (such as Microsoft FrontPage) will not meet all our needs, in which case we have to do some scripting on our own.
System administrator is needed to help with both web server and database server configurations to make such scripting possible.
If you are using Microsoft Internet Information Server as your web server, the preferred option is perhaps to program using VisualBasic and publish your webpages using the ASP (Active Server Page) technology, both of which are Microsoft's creations.
Other server-side scripting options on a WindowsNT server include Perl and PHP.
If you have access to a Unix machine, an excellent option is the Apache web server which can be configured with MySQL as its backend database server and PHP as the scripting language. My Chinese Character Frequency Search was created using this combination of software and programming technology. For an illustration of how such a system is installed and configured, you may check out my 'Quick Installation Tutorial'. The following section provides a sample page for using the system.
9.5 A case study
1) A simple case: See my Chinese Character Frequency Search page;
2) A more complicated case: See next session on online language testing system