3

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?

Community
  • 1
  • 1
dontknow
  • 85
  • 1
  • 1
  • 7
  • possible duplicate of [Wufoo's Database Schema - How would you design it?](http://stackoverflow.com/questions/1160076/wufoos-database-schema-how-would-you-design-it) – Barif Feb 14 '15 at 14:03
  • yes, it is..bcs i dont see how it can work... – dontknow Feb 14 '15 at 16:19

1 Answers1

5

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.

OmerGertel
  • 2,573
  • 1
  • 19
  • 27
  • when your table is big, then restore time can take longer, thats why im trying to prevent from hugh table size, regarding : "you can save the form submission in json format. " thanks omer, actually currently this is what im doing but still , thinking mb im missing something, pg is ok, but in this case..why not use mongo which should be scaling horiz better ? – dontknow Feb 14 '15 at 18:09
  • I assumed you already have some SQL DB in place, and would rather keep using it (this really depends on preferences and existing code. In Django, for example, keeping the ORM is a really good idea, and tying the form submissions in with existing models is a big plus). Also, I think it is a little easier to do aggregates on pg, if this is something you need. – OmerGertel Feb 16 '15 at 08:41
  • thanks omer for your reply, ill wait little longer see if anyone is having a different opinion. – dontknow Mar 01 '15 at 10:11