A few times people have raised the question of why the plugin’s database table (wp_cf7dbplugin_submits) is structured the way it is.<\/p>\n
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:<\/p>\n
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.<\/p>\n Typical questions\/objections are:<\/p>\n (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.<\/p>\n (2). Schema is not normalized. It takes up more space and queries will not be as performant as compared to a normalized form.<\/p>\n My rationale for the design is to favor simplicity, flexibility and maintainability over what I expect to be negligible performance optimizations.<\/strong><\/p>\n If every form had its own table, we have these issues:<\/p>\n (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.<\/p>\n (2).\u00a0 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.<\/p>\n (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.\u00a0 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.<\/p>\n (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.<\/p>\n In summary, if I made a normalized one-table-per-form design, then:<\/p>\n (1) I have to write a whole bunch of table management code which I can now completely avoid.<\/p>\n (2) I have to integrate tightly with Contact Form 7 and Fast Secure Contact form plugin data structures, making this plugin more fragile.<\/p>\n","protected":false},"excerpt":{"rendered":" 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 […]<\/p>\n","protected":false},"author":1,"featured_media":0,"parent":102,"menu_order":13,"comment_status":"closed","ping_status":"closed","template":"","meta":{"jetpack_post_was_ever_published":false,"footnotes":""},"class_list":["post-71","page","type-page","status-publish","hentry"],"jetpack_shortlink":"https:\/\/wp.me\/P1mptf-19","jetpack_sharing_enabled":true,"jetpack-related-posts":[],"_links":{"self":[{"href":"https:\/\/cfdbplugin.com\/index.php?rest_route=\/wp\/v2\/pages\/71","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/cfdbplugin.com\/index.php?rest_route=\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/cfdbplugin.com\/index.php?rest_route=\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/cfdbplugin.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/cfdbplugin.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=71"}],"version-history":[{"count":9,"href":"https:\/\/cfdbplugin.com\/index.php?rest_route=\/wp\/v2\/pages\/71\/revisions"}],"predecessor-version":[{"id":843,"href":"https:\/\/cfdbplugin.com\/index.php?rest_route=\/wp\/v2\/pages\/71\/revisions\/843"}],"up":[{"embeddable":true,"href":"https:\/\/cfdbplugin.com\/index.php?rest_route=\/wp\/v2\/pages\/102"}],"wp:attachment":[{"href":"https:\/\/cfdbplugin.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=71"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}CREATE TABLE IF NOT EXISTS wp_cf7dbplugin_submits (
\nsubmit_time DECIMAL(16,4) NOT NULL,
\nform_name VARCHAR(127) CHARACTER SET utf8,
\nfield_name VARCHAR(127) CHARACTER SET utf8,
\nfield_value LONGTEXT CHARACTER SET utf8,
\nfield_order INTEGER,
\nfile LONGBLOB)<\/code><\/p>\n