Database schema for a dynamic formbuilder

问题:

I know that there is already an answer for a similar question but I think that the answer is not strong enough, so I'll ask with my own specific issues.

• assumption: dynamic form builder, users can create form with structure which is not known.

• solution: Form submission, data will be stored in a 2 table structure:

  1. FormSubmissionHeader table that will store some basic data about the submission(formid,userid,datetime,etc)
  2. FormSubmissionFieldsData(FormSubmissionHeaderID ,FIELDID,FIELDVALUE)

My problem with this solution is with mass usage:
Assuming I got 1 million users,
each user will have 3 forms
each form will have 10 fields
and each form will have 5 submissions per day.

So the FormSubmissionHeader will grow in 1 million X3X5 per day = 15 million rows per day.

and FormSubmissionFieldsData wil grow by 15X10 (number of daily submission, double number of fields), meaning 150 million rows per day.

So based on this solution:

I don't see how it is going to work.

I see two alternatives :

1. use MONGODB to store submissions header and data (NO SQL)

2. dynamically create table per form and store the data directly there with relevant datatypes per field.

This is a similar question which I saw..

Wufoo's Database Schema - How would you design it?

I wonder what do you guys think of it or can you suggest something else?

回答1:

You'll have to work hard to convince me that 150m rows a day is a lot of data at this day and age. It really depends on what you plan to do with the submissions. Most queries will work fine, as long as you index correctly.

But if you really must reduce the number of rows, you can save the form submission in json format. I believe Postresql supports this out of the box. You can get the best of both worlds, structured and semi structured data together.

So the form table is unchanged, but the submission table will have a few colums about the submission (user, form id, date, etc.) and one json column with the answer as a json object, which you can query on as well.