The Tableform Database Interactive Environment
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:
- Table-to-Table Copy - allows for a source table to be copied to a destination table, with mappings for which fields in the source end up as which in the destination, with provisions for conversion formulæ and a script to run after every copied row.
- Manual SQL Tool - a comprehensive query analyzer. Raw SQL is typed in and run, and results are displayed in tabular form. Queries are stored in a "Recent SQL queries" list to allow them to be run again. Provision is provided to allow freeform PHP scripts to be run on the rows, and there is also a provision for nesting subqueries if they are specified in the Raw SQL. Results can dynamically resortable by the datatype of any field. There is also a provision allowing for the direct editing of results. Finally, where possible, links are provided in the results allowing them to be edited within Tableform's relational data editing facilities.
- Install Table Packages - solutions for various common web development problems (for example, a rotating ad-banner system) can be installed. (Eventually this system will be expanded to allow users to include their own packages.)
- Display Database Stats - information about the database's state.
- Analyze Database Changes - allows an administrator to save the database's state and then compare it later to see what has changed in the database. This is particularly useful when determining the behavior of third party web applications. This is similar to a database log analyzer, but changes are displayed as lists of actual changed data.
- Import Database from SQL - allows a SQL dump of a database to be imported. Analyzes the SQL first to show a digest of what it will be doing.
- Backup Database to a SQL File - a simple dump of the database.
- Comprehensive SQL Export Tools - allows for the export of parts of a database, providing numerous options. One feature allows the export to follow relations to be sure related data in other unmentioned tables is also copied. Another makes sure to export SQL code ensuring that existing primary keys are not overwritten.
- Search the Entire Database - searches every table in the database for the search term, providing results in tabular, dynamically-resortable form hyperlinked to editing tools.
- Show/Create Maps of the Database - creates a graphical map of the database with relations drawn between primary and foreign keys. The tables in the map can be interactively moved and the changes saved and exported to PDF files.
- Scan the Database for Possible Relations Based on Field Names - relations between tables have to be specified in the relations table, but if a simple naming convention is followed when tables are created, it's possible to scan the database with this tool and it will automatically figure out the relations between tables.
- Copy Information Between Similar Tables Using Concordances - provides a means for fields from one table to be copied to another if fields in the two tables match.
- Search the Entire Database for Tables Having a Given Field Name - usefrul for debugging unfamiliar database applications.
- Delete a Range of Records From a Table and Also Delete Dependent Records in Related Tables - a relationship-aware form of delete.
- Create a Table - an interactive database table designer that is relationship-aware.