CODEWITHSUNDEEP
pythonmysqldatabasedatabase-design
martin's
martin's

Reputation: 3955

How to design a database to represent data that changes in structure?

Working on designing a database to store and track the history of the contents of a web form. Normally this would not be a problem. The naive implementation being a history table that records form field contents along with a timestamp.

Here's the issue: This form could change over time. Fields might be renamed, added or eliminated.

How does one model that and ensure data integrity across the entire historical record.

The thought I have right now is that the history table could be done with just two fields: timestamp and data. And, in this case, data would be a JSON string corresponding to the form fields and their data at the time the snapshot was taken. This means the software could, at any time, show rolled-back versions of the form regardless of how the structure may have changed.

What might be other approaches to representing this in the database?

Using Python/Django and MySQL, which probably not relevant.

EDIT 1:

Clarification. Imagine wanting to record the history of a form on a website you do not control. It's about creating a database to store and record the history of that page. Think Git for a page with a form and data that can change in structure and content every year or two.

EDIT 2:

One option would be to create a complex table structure whereby a form can be described by using a table that stores the various types of form fields available at any given time and then a form_contents or form_history table would ultimately glue it all together into a structure that could record the history of a form with varying structure over time. I can see this potentially getting very complicated.

Upvotes: 0

Views: 194

Answers (1)

Benny Hill
Benny Hill

Reputation: 6240

If I understand you correctly I'd probably do something like this:

CREATE TABLE IF NOT EXISTS `form_history` (
    `id`                    int unsigned    NOT NULL AUTO_INCREMENT,
    `when`                  datetime        NOT NULL,
    `field_accept`          varchar(255)    DEFAULT NULL,
    `field_accesskey`       varchar(255)    DEFAULT NULL,
    `field_alt`             varchar(255)    DEFAULT NULL,
    `field_autocomplete`    varchar(255)    DEFAULT NULL,
    `field_autofocus`       varchar(255)    DEFAULT NULL,
    `field_checked`         varchar(255)    DEFAULT NULL,
    `field_class`           varchar(255)    DEFAULT NULL,
    `field_contenteditable` varchar(255)    DEFAULT NULL,
    `field_contextmenu`     varchar(255)    DEFAULT NULL,
    `field_data`            text            DEFAULT NULL,
    `field_dir`             varchar(255)    DEFAULT NULL,
    `field_disabled`        varchar(255)    DEFAULT NULL,
    `field_draggable`       varchar(255)    DEFAULT NULL,
    `field_dropzone`        varchar(255)    DEFAULT NULL,
    `field_form`            varchar(255)    DEFAULT NULL,
    `field_formaction`      varchar(255)    DEFAULT NULL,
    `field_formtarget`      varchar(255)    DEFAULT NULL,
    `field_height`          int unsigned    DEFAULT NULL,
    `field_hidden`          varchar(255)    DEFAULT NULL,
    `field_id`              varchar(255)    DEFAULT NULL,
    `field_lang`            varchar(255)    DEFAULT NULL,
    `field_list`            varchar(255)    DEFAULT NULL,
    `field_max`             varchar(255)    DEFAULT NULL,
    `field_maxlength`       int unsigned    DEFAULT NULL,
    `field_min`             varchar(255)    DEFAULT NULL,
    `field_multiple`        varchar(255)    DEFAULT NULL,
    `field_name`            varchar(255)    DEFAULT NULL,
    `field_pattern`         varchar(255)    DEFAULT NULL,
    `field_placeholder`     varchar(255)    DEFAULT NULL,
    `field_readonly`        varchar(255)    DEFAULT NULL,
    `field_required`        varchar(255)    DEFAULT NULL,
    `field_size`            int unsigned    DEFAULT NULL,
    `field_spellcheck`      varchar(255)    DEFAULT NULL,
    `field_src`             varchar(255)    DEFAULT NULL,
    `field_step`            int unsigned    DEFAULT NULL,
    `field_style`           varchar(255)    DEFAULT NULL,
    `field_tabindex`        int unsigned    DEFAULT NULL,
    `field_title`           varchar(255)    DEFAULT NULL,
    `field_translate`       varchar(255)    DEFAULT NULL,
    `field_type`            varchar(255)    DEFAULT NULL,
    `field_value`           varchar(255)    DEFAULT NULL,
    `field_width`           int unsigned    DEFAULT NULL,
    PRIMARY KEY (`id`), KEY (`when`)
) ENGINE=InnoDB COMMENT='Field definitions';

You could add columns for event attributes as well if that's important to you.

Here's some example data:

|----|---------------------|-----|-----------------|-----|------------|-----|
| id | when                | ... | field_maxlength | ... | field_name | ... |
|----|---------------------|-----|-----------------|-----|------------|-----|
|  1 | 2015-06-01 00:00:01 | ... |              10 | ... | username   | ... |
|  2 | 2015-06-01 00:00:01 | ... |              10 | ... | password   | ... |
| .. | ................... | ... | ............... | ... | .......... | ... |
| 17 | 2015-06-08 00:00:01 | ... |              32 | ... | username   | ... |
| 18 | 2015-06-08 00:00:01 | ... |              32 | ... | password   | ... |
| 19 | 2015-06-08 00:00:01 | ... |              25 | ... | fname      | ... |
| 20 | 2015-06-08 00:00:01 | ... |              25 | ... | lname      | ... |
| .. | ................... | ... | ............... | ... | .......... | ... |
|----|---------------------|-----|-----------------|-----|------------|-----|

This very simple example data shows only two fields on a form (username and password). On the 1st they both had a maxlength of 10 but on the 8th they the maxlength value was increased to 32 and two new fields were added to the form: fname and lname.

Upvotes: 3

Related Questions