Database table structure rationale

November 25th, 2012 Leave a comment Go to comments

A few times people have raised the question of why the plugin’s database table (wp_cf7dbplugin_submits) is structured the way it is.

The table is set up with one row per form entry. So each row contains the submit time, form name, field name and field value. The MySQL definition is this:

CREATE TABLE IF NOT EXISTS wp_cf7dbplugin_submits (
submit_time DECIMAL(16,4) NOT NULL,
form_name VARCHAR(127) CHARACTER SET utf8,
field_name VARCHAR(127) CHARACTER SET utf8,
field_value LONGTEXT CHARACTER SET utf8,
field_order INTEGER,
file LONGBLOB)

Typically, people would have expected to see a separate table for each form, where each table’s columns reflect its form fields. They don’t expect to see this single consolidated table for all form data.

Typical questions/objections are:

(1). The database table structure does not match the table structure seen on the web page. The database data has to be pivoted into that table. This is not a trivial data transformation. Sometimes people want to write their own queries to against the table to retrieve or load data, and it is not so obvious/easy to do this.

(2). Schema is not normalized. It takes up more space and queries will not be as performant as compared to a normalized form.

My rationale for the design is to favor simplicity, flexibility and maintainability over what I expect to be negligible performance optimizations.

If every form had its own table, we have these issues:

(1). Need to integrate more tightly with Contact Form 7 and Fast Secure Contact form plugins to read their form definitions and create a table. With the existing “soft” schema, we can completely ignore the form definitions. We just save what gets posted.

(2).  When a user changes form definitions in Contact Form 7 and Fast Secure Contact form plugins, we would need to make appropriate changes to existing tables. But what if there is already data in the DB table that no longer matches any field in the updated form? How should we handle “legacy data”? With the existing schema, both coincide without conflict. The Admin panel display table shows you all the form submissions, and if one form submission did not have certain field, then it simply shows a blank cell in that column. If you delete all those legacy submissions, then any columns specific to them simply vanish.

(3). Integrating more tightly with Contact Form 7 and Fast Secure Contact form plugins creates dependencies on the inner workings of these plugins, thereby making my plugin more fragile.  In other words, it becomes sensitive to changes in those plugins and unexpected changes in new versions of those plugins may violate assumptions I would have to make in code, thereby breaking this plugin. As it stands, with the existing schema, the plugin requires the absolute minimal knowledge of other plugins. And what it does depend on are WordPress hooks defined by those plugins, which constitute an API. Therefore, I can feel confident that those other plugin writers will maintain support for their APIs and therefore my plugin will not have forward-compatibility issues.

(4). Although the current schema is in theory a less performant schema and take up more bytes in the DB, I believe this difference is negligible, and a good trade-off choice considering the benefits.

In summary, if I made a normalized one-table-per-form design, then:

(1) I have to write a whole bunch of table management code which I can now completely avoid.

(2) I have to integrate tightly with Contact Form 7 and Fast Secure Contact form plugin data structures, making this plugin more fragile.

  1. November 18th, 2011 at 12:45 | #1

    Hi,

    Thanks for a great plugin.

    I completely agree with you. It is much better for your plugin to be simpler, more stable, and less reliant on the other plugins.

    If people are struggling to extract the data from the table they just need to create a view using phpmyadmin to give them a simple table construct for their form data. In the SQL box just type “CREATE VIEW AS” then append the SQL omitting the ORDER BY clause e.g.

    CREATE VIEW form_view_name AS
    SELECT `submit_time` AS ‘Submitted’,
    max(if(`field_name`=’your-name’, `field_value`, null )) AS ‘your-name’,
    max(if(`field_name`=’your-email’, `field_value`, null )) AS ‘your-email’,
    … snip …
    GROUP_CONCAT(if(`file` is null or length(`file`) = 0, null, `field_name`)) AS ‘fields_with_file’
    FROM `wp_cf7dbplugin_submits`
    WHERE `form_name` = ”
    GROUP BY `submit_time` ;

    Then they can just type SELECT * FROM form_view_name; to retrieve their data.

    Regards,
    David

  2. Paul Dillon
    April 14th, 2013 at 15:33 | #2

    Good tip, David.

    I created a view then queried the view and got the desired results

  3. mighty55
    September 30th, 2013 at 19:08 | #3

    @David Williamson
    I tried using your code David and can’t get it to work. I am wanting to create a view to be pulled into an external table.

    When I submit the create view I keep getting this error:

    #1064 – You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘GROUP_CONCAT(if(‘file’ is null or length(‘file’) = 0,null,’field_name’)) AS ‘fie’ at line 7

    Any thoughts on what this could be?

  1. February 26th, 2011 at 16:44 | #1
You must be logged in to post a comment.