| | | > Home > Technology > Database design
Conventional form design typically employs "column-per-field" relational database tables (“fat table”) per form plus another fat table per form for repeating data. The main problems with the conventional design approach are:
Large number of tables are generated (exceeding some databases management systems’ permitted number of tables, e.g., Access) increasing difficulty of understanding and maintaining the system
Backing up the large numbers of tables is time consuming; copying large numbers of data tables increases the difficulty of providing backup systems
Repeating data (i.e., checkboxes) requires a separate table, which further swells the number of tables
Difficulty adding columns when forms are operational
Relative difficulty programmatically of saving interim form data (e.g., on a field by field basis rather than all fields in the form) without special case programming
Relative difficulty programmatically of editing form data without special case programming
Relative difficulty programmatically of accessing data for report generation, cross-tabbing to other forms without elaborate special case programming.
fm2x Qii is an almost entirely database-driven design. Programming, while not a trivial part of Qii, is almost entirely subordinate to the database. Forms operation involves the data held in three key tables and their relationships.
Data from Qii forms are stored in an innovative column store type architecture (http://en.wikipedia.org/wiki/Column-oriented_DBMS), implemented in the relational database MySQL. This means there is a single table (“thin table”) for data storage for all forms and that each row stores only a single value per field per response. Of course, clients are unaware of these details and their data are delivered to them in conventional “fat table” format. Qii also can maintain parallel "thin" and "fat" tables on a per-form basis.
Qii column store (“thin table”) architecture addresses these problems efficiently:
There are only four key tables (questionnaires, questions, responses and data) in the entire Qii system; this facilitates understanding the Qii database
It is easy to create backup servers because there are fewer tables to copy
Archiving into the single data_archive table is a straightforward and flexible operation.
Column store architecture simplifies programming. It makes it possible to process form data algorithmically, indeed there is no efficient way otherwise, even from different forms, without resorting to special-case programming. Data saving and editing from forms also is simplified considerably, since a single operation handles all data. Column-store allows repeating data to be stored straightforwardly with single-value data; in practice, however, repeating data in Qii are stored as “stringified lists” to make no-value detection completely positive. See recent discussion discussion on the subject of column store.
Repeating data (i.e., checkboxes) could be stored directly into the data table as repeating rows; in practice, however, they are stored as “stringified lists”
“Columns” (i.e., form fields) can be added while forms are operational
Saving interim data requires no special case programming because there is a single storage data type
Editing form data requires no special case programming because there is a single storage data type
Accessing data for report generation, cross-tabbing to other forms with a single routine and requires no special cases.
|
