批评我的MySQL数据库设计无限制DYNAMIC字段 [英] Critique my MySQL Database Design for Unlimited DYNAMIC Fields
问题描述
为建立自己的表单样式网站寻找可扩展，灵活和快速的数据库设计 - 例如 Wufoo
规则：
- 用户只能创建1个表单， li>
- 用户可以创建自己的字段或从标准字段中选择
- 用户1表单具有用户想要的字段数量
- 值可以是另一个值的兄弟姐妹比如一个照片的价值可以有名字，位置，宽度，高度作为兄弟姐妹值
特殊规则：
- 用户每天最多可以提交5次表单
- 价值的日期很重要
- 可以报告价值的灵活性（对于单个用户，跨所有用户，1个字段，多个字段）是非常重要的 - 数据可视化（大多数将按时间顺序，例如所有所有用户的2009年7月照片）。
表用户
uid
表field_user - 为用户表单指定一个字段
fid
uid
weight - int - 用于订购用户表单上的字段
表字段
fid
creator_uid - int - 字段'创建者'
标签 - varchar - 例如电子邮件
value_type - varchar - 用于确定'values'表中的哪个字段将被填入（例如，如果'int'，则此字段的值将提交数据values.type_int字段和所有其他.type_x字段将为NULL）。
field_type - varchar - eg 'email' - 用于特殊条件，例如验证规则
表值
vid
parent_vid
fid
UID
日期 - 日期
date_group - int - value 1-5（用户每天最多可能提交5个表单）
type_varchar - varchar
type_text - text
type_int - int
type_float - float
type_bool - bool
type_date - 日期
type_timestamp - timestamp
我知道这种方法将意味着价值表中的记录将只有1个数据与其他.type_x字段包含NULL的...但从我的理解，这个设计将是最快的解决方案（少查询，少连接表）
昨天在 OSCON Josh Berkus给了一个很好的DB设计教程，他花了很大一部分，无情地撕破了这样的 EAV il表;您应该能够在OSCON网站上找到他的幻灯片，最终在线完成他的整个教程的录音（后者可能需要一段时间）。
你每个属性需要一个连接（多个实例的值表，每个属性您获取或更新一个），所以我不知道你的意思是少连接表。连接同一张表的许多实例并不是一个特别快速的操作，您的设计使索引几乎不可行和无法使用。
至少作为次要改进使用每种类型单独的表为您的属性的值（可能一些索引可能适用于这种情况，尽管MySQL限制每个查询每个表的一个索引，即使有点可疑）。
Looking for a scalable, flexible and fast database design for 'Build your own form' style website - e.g Wufoo.
Rules:
- User has only 1 Form they can build
- User can create their own fields or choose from 'standard' fields
- User's 1 Form has as many fields as the user wants
- Values can be the sibling of another value E.g A photo value could have name, location, width, height as sibling values
Special Rules:
- User can submit their form a maximum of 5 times a day
- Value's Date is important
- Flexibility to report on values (for single user, across all users, 1 field, many fields) is very important -- data visualization (most will be chronologically based e.g. all photos for July 2009 for all users).
Table "users"
uid
Table "field_user" - assign a field to a users form
fid
uid
weight - int - used to order the fields on the users form
Table "fields"
fid
creator_uid - int - the field 'creator'
label - varchar - e.g. Email
value_type - varchar - used to determine what field in the 'values' table will be filled in (e.g. if 'int' then values of this field will submit data into the values.type_int field - and all other .type_x fields will be NULL).
field_type - varchar - e.g. 'email' - used for special conditions e.g. validation rules
Table "values"
vid
parent_vid
fid
uid
date - date
date_group - int - value 1-5 (user may submit max of 5 forms per day)
type_varchar - varchar
type_text - text
type_int - int
type_float - float
type_bool - bool
type_date - date
type_timestamp - timestamp
I understand that this approach will mean records in the 'Value' table will only have 1 piece of data with other .type_x fields containing NULL's... but from my understanding this design will be the 'fastest' solution (less queries, less join tables)
At OSCON yesterday, Josh Berkus gave a good tutorial on DB design, and he spent a good fraction of it mercilessly tearing into such "EAV"il tables; you should be able to find his slides on the OSCON site soon, and eventually the audio recording of his whole tutorial online (the latter will probably take a while).
You'll need a join per attribute (multiple instances of the values table, one per attribute you're fetching or updating) so I don't know what you mean by "less join tables". Joining many instances of the same table isn't a particularly fast operation, and your design makes indices nearly unfeasible and unusable.
At least as a minor improvement use per-type separate tables for your attributes' values (maybe some indexing might be applicable in that case, though with MySQL's limitation to one index per query per table even that's somewhat dubious).
这篇关于批评我的MySQL数据库设计无限制DYNAMIC字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
