CODEWITHSUNDEEP
phpmysqldatabase-design
Saqueib
Saqueib

Reputation: 3520

Form designer app with mysql data submission

I want to develop an app in PHP with following features

I have done the UI part and form designer.

Problem

I am not able to figure out how to design database for accepting form submission, for instance If an user created a form with following fields

But later user can add or remove fields without messing with the database design.

Please help me with MySQL database design for storing form into table and there submission.

Upvotes: 1

Views: 228

Answers (1)

eggyal
eggyal

Reputation: 125895

Something like this:

CREATE TABLE forms (
  form_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, -- the UID of this form
  owner   INT,                                     -- the user who "owns" the form
  PRIMARY KEY (form_id)
);

CREATE TABLE fields (
  form_id  BIGINT UNSIGNED NOT NULL,
  field    VARCHAR(10),                            -- the field name
  PRIMARY KEY (form_id, field),
  FOREIGN KEY (form_id) REFERENCES forms (form_id)
);

CREATE TABLE submissions (
  submission_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  form_id       BIGINT UNSIGNED NOT NULL,
  field         VARCHAR(10),
  value         BLOB,                              -- stores PHP's serialize($var)
  PRIMARY KEY (submission_id),
  FOREIGN KEY (form_id) REFERENCES forms (form_id),
  FOREIGN KEY (form_id, field) REFERENCES fields (form_id, field)
);

I would suggest that modifications to a form's design create a new (copy) form in the database, so as not to invalidate any historical submissions based on the old design.

Upvotes: 3

Related Questions