The Tableform Database Interactive Environment


Tableform is a web-based database visualizer and frontend system for MySQL databases. Its goal is to provide an intuitive generic frontend (that is, an editing and viewing environment) for any MySQL database. Tableform consists of a collection of interlinked web tools using PHP pages, Javascript libraries, and special housekeeping database tables. Some of these tools are for database designers and administrators while others are for those who merely enter and retrieve data.

The major benefit of Tableform over other generic database administrative systems is that it is aware of the relations in a relational database and provides suitable (though generic) tools for entering and viewing data across relationships, and these tools are often intuitive enough to be quickly mastered by someone not especially familiar with databases. While it is true that relationships in a database can reach a complexity where a generic tool would be inappropriate, in many cases a generic tool is adequate, or are "good enough" until better tools are developed. These tools include features to allow image upload, WYSIWYG text editing, selection of items from a list pulled from a related foreign table, and interactive list reordering of items in one table that have a relation to an item in another.

In the classic example of a database used to manage a wine store, setting up the entire backend is as simple as creating the tables in a graphical web-based tool and then interlinking their relations. From then on, the system knows when to, for example, substitute dropdown menus for "region" in the form to edit a wine's record. It also knows (only from looking at the model) when to provide hyperlinked lists of customers, suppliers, or sales at the bottom of that form.

For database administrators and designers, Tableform offers the tools of a database visualizer. It allows for multiple interlinked table maps to be created, saved, and even exported as PDF files. It has multi-option web-based wizards for importing and exporting large amounts of data and table definitions in formats ranging from XML to SQL to CSV. There is also a cross-table search engine, a mechanism to track database changes from a known state, and an elaborate query analyzer developed over years of active use in debugging actual databases.

Ideal Tableform Applications

With the development of Tableform, the goal has been to provide a spectrum of tools applicable for most users for most database implementations. This makes it an ideal set of administrative tools for a website that needs to be built rapidly, without a dedication of effort to the tools needed for administrators to maintain and retrieve data. In applications where Tableform cannot provide an adequate set of administrative tools, those tools can always be custom-built for the application, and take advantage of Tableform's code libraries. Tableform has been used in contexts where several different PHP/MySQL applications have been integrated, and in those cases it is best to use those applications' tools for the management of their data, while using Tableform to manage other tables (or to make modifications and examine relational data in the instances where the applications fail to provide tools).

Products Having Functionality Similar to Tableform

phpMyAdmin - a free open source PHP web application that allows users to edit data in a database, run SQL queries, and import and export data. Also allows for the graphical design of databases. Does not provide relationally-aware data entry forms or other relationally-aware tools. Table editing is entirely generic and editor pages, being entirely the same, would not be suitable as a general-purpose administration. Does not provide a security model for different levels of administration.

Drupal - a free open source PHP/MySQL content management web application. This is a content-management tool with its own database schema, though it has a module-based system allowing foreign database structures to be integrated and managed (this requires custom development for user-created modules supporting custom arrangements of database tables).

Content Management With Tableform

Tableform was designed from the ground up to be relationship-aware, and this extends to the data entry tools themselves. Unlike the data editing tools of a conventional database administrative tool, Tableform analyzes the relations of a table to provide the best editing tool it can. The tools are created in real time based only on the database table definitions and the list of relations between them. To get a sense of the difference between Tableform's approach and that of a conventional database tool, examine these two forms:

Typical generic database data-entry form:

Tableform database data-entry form:

As you can see, a typical database data entry tool (such as that provided by phpMyAdmin) provides generic spaces to enter the data for every field of a row and pays no attention to either meta-information about those fields or the relationships those fields have to other tables. When presented with a place to type in a lifeform_id, you're expected to know what IDs correspond to what lifeforms in the lifeform table. But Tableform follows the relationships from one table to the next, providing a dropdown box populated with human-readable lifeforms. Similarly, Tableform notes the existence of a number of mapping tables and uses these to populate lists of associated items to be edited, added, reordered, or deleted (this is like the mechanism populating the dropdown box of lifeforms, but run backwards). A tool like phpMyAdmin has no equivalent; you're forced to look up IDs in the various tables and then manually enter them into the mapping tables. A tool such as Drupal does have system allowing associated items to be added or removed from base items, but these mechanisms are custom-built for specific Drupal tables and are not generic. In Tableform, you see, this relationship-following allows any database to have its functional administrative tools built on the fly, using information derived from the low-level definitions of the tables and relationships in the database.

Other points about this editor are that it automatically provides file upload capability (in this case for a picture of Wilma the Cat) and can, if configured for it, provide a WYSIWYG editor for the entry of large blocks of text (this functionality was turned off in this example). As you can see, this data entry form is intuitive enough to be used by someone otherwise unfamiliar with databases.

As always, in all possible case, if it makes sense for the user or administrator to navigate to another tool to either explore a relation in the data or to act on the data or the data structure with another data entry form (and if that user or administrator has sufficient permissions) a link is provided.

Administrative Tools Integrated into Tableform

Tableform includes a large suite of tools allowing administrators and developers to interact with a database, whether it is one in development, one being integrated into an existing system, or one that has already gone live: