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

问题描述

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

• 假设：
  动态表单构建器，用户可以创建不具有结构的表单。




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

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

我的解决方案的问题是大量使用：

假设我有100万用户，

每个用户将有3个表单

每个表单将有10个字段

，每个表单每天将有5个提交。

所以FormSubmissionHeader将以每天1百万X3X5的速度增长=每天1500万行。

和FormSubmissionFieldsData将增长15X10（每日提交的数量，双倍的字段数），意味着每天的1.5亿行。

所以基于这个解决方案：

我看不到它会如何工作。

我看到两种选择：

1. 使用MONGODB动态存储提交的标题和数据（NO SQL）




2. 创建每个表单的表格，并直接存储数据，每个字段
   相关的数据类型。

这是一个我看到的类似问题。

Wufoo的数据库模式 - 如何你设计它吗？

我想知道你们有什么想法或者可以建议别的吗？

解决方案

你必须努力说服我，每天有150万行是这个日子和时代的大量数据。这真的取决于你打算提交的内容。只要索引正确，大多数查询都可以正常工作。

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

所以窗体表不变，但提交表将有几个关于提交（用户，表单ID，日期等）的列表和一个json列答案作为一个json对象，你也可以查询。

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?

解决方案

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.

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