动态formbuilder的数据库模式 [英] database schema for a dynamic formbuilder

问题描述

我知道已经有一个类似问题的answear，但我认为答案不够强，所以我会问我自己的具体问题。

• 假设：
  动态表单生成器，用户可以创建不知道结构的表单。




• 解决方案：
  表单提交，数据将存储在2个表结构中：

  
  
  1. FormSubmissionHeader表将存储一些基本数据关于提交内容（formid，userid，datetime等）
  
  
  2. FormSubmissionFieldsData（FormSubmissionHeaderID，FIELDID，FIELDVALUE）
  
  

我的问题与大量使用：
假设我有100万用户，
每个用户将有3种形式
每个表单将有10个字段
，每个表单每天有5个提交。

因此FormSubmissionHeader将增加每天300万X3X5 =每天的行数

和FormSubmissionFieldsData将增加15X10（每天提交的数量，两倍的字段数），意味着每天150万行。

所以基于这个解决方案：

我看不到它是怎么工作的。

我看到两个选项：

1. 使用MONGODB存储提交的标题和数据






2. 动态创建每个表格的表格并直接存储数据， ol>
   
   

   这是类似问题。

   
   
   

   
   
   

   我不知道你们是怎么想的，或者可以这样设计的

   
   
   

   感谢

   解决方案

   努力工作以说服我，每天150米行是这一天和很大的数据。这真的取决于你打算如何处理提交。大多数查询将正常工作，只要您正确索引。

   
   
   

   但是如果你真的必须减少行数，你可以以json格式保存表单提交。我相信Postresql支持这种开箱即用。您可以同时获得两个世界，结构化和半结构化数据的最佳结果。

   
   
   

   所以表单表没有改变，但是提交表会有几个关于提交（用户，表单ID，日期等）的列和一个json列答案作为一个json对象，你可以查询它。

   
   

   I know that there is already an answear 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 dont 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?

   Thanks

   解决方案

   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.

   这篇关于动态formbuilder的数据库模式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！