I have three tables:
forms which contains basic information about a form
form_parts which contains information about a certain input field and has a FK to forms (each form parts must have one form_id and each form may have one or more form_parts)
part_answers which contains the answer that the user submits for each field, has a FK to form_parts (each form_part may have multiple answers by different users and each part answer must have one form_part)
Now the problem is that when an editor whishes to edit the form, or a part of the form, if a user already answered one or more of those edited form_parts, his or her answer won't necessarily match the question anymore.
So how should I proceed?
I have thought of only one option so far, but it feels like a hack:
Set the form in question to inactive, or updated on a certain column (like a status column), and re-insert the updated version with it's corresponding answers.
This way, the data that has already been submitted will always continue to match the form it was submitted for, but I'm wondering if this is truly an optimal solution or if I forgot about a better method.
forms - sql :
CREATE TABLE `forms` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(255) NOT NULL, `created_on` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, `modified_on` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, `type` varchar(255) DEFAULT NULL, `product_id` int(11) NOT NULL, `updated` tinyint(1) NOT NULL DEFAULT '0', PRIMARY KEY (`id`), UNIQUE KEY `formulieren_id_uindex` (`id`), UNIQUE KEY `formulieren_name_uindex` (`name`), KEY `forms_products_id_fk` (`product_id`) ) ENGINE=InnoDB AUTO_INCREMENT=28 DEFAULT CHARSET=latin1
form_parts - sql:
CREATE TABLE `form_fields` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(255) NOT NULL, `pdf_placeholder` varchar(255) NOT NULL, `datatype` varchar(255) NOT NULL, `regular_expression` varchar(255) DEFAULT NULL, `form_id` int(11) DEFAULT NULL, `input_placeholder` varchar(255) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `formulier_onderdelen_id_uindex` (`id`), KEY `formulier_onderdelen_formulieren_id_fk` (`form_id`), CONSTRAINT `form_fields_forms_id_fk` FOREIGN KEY (`form_id`) REFERENCES `forms` (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=117 DEFAULT CHARSET=latin1
part_answers - sql:
CREATE TABLE `part_answers` ( `id` int(11) NOT NULL AUTO_INCREMENT, `form_part_id` int(11) NOT NULL, `order_product_id` int(11) NOT NULL, `answer` varchar(255) NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `part_answers_id_uindex` (`id`), KEY `part_answers_form_parts_id_fk` (`form_part_id`), KEY `part_answers_order_products_id_fk` (`order_product_id`), CONSTRAINT `part_answers_form_parts_id_fk` FOREIGN KEY (`form_part_id`) REFERENCES `form_fields` (`id`), CONSTRAINT `part_answers_order_products_id_fk` FOREIGN KEY (`order_product_id`) REFERENCES `order_products` (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=137 DEFAULT CHARSET=latin1