如何为用户定义的字段设计数据库？

我的要求是:

需要能够动态添加任何数据类型的用户定义字段

需要能够快速查询UDF

需要能够基于数据类型对UDF进行计算

需要能够根据数据类型对UDF进行排序

其他信息:

我主要是在寻找表现

有几百万条Master记录可以附加UDF数据

当我上次检查时,我们当前的数据库中有超过50mil的UDF记录

大多数情况下,UDF仅附加到几千个Master记录中,而不是全部记录

UDF未加入或用作键.它们只是用于查询或报告的数据

选项:

使用StringValue1,StringValue2创建一个大表... IntValue1,IntValue2,...等我讨厌这个想法,但如果有人能告诉我它比其他想法更好,为什么会考虑它.

创建一个动态表,根据需要按需添加新列.我也不喜欢这个想法,因为除非你索引每一列,否则我觉得性能会很慢.

创建一个包含UDFName,UDFDataType和Value的表.添加新的UDF时,生成一个View,它只提取该数据并将其解析为指定的任何类型.不符合解析标准的项返回NULL.

创建多个UDF表,每种数据类型一个.所以我们有UDFStrings,UDFDates等的表.可能和#2一样,并且只要添加新字段就自动生成View

XML数据类型？我之前没有使用过这些,但已经看过它们了.不确定他们是否会给我我想要的结果,尤其是性能.

别的什么？

Phil Helmer.. 49

如果性能是主要考虑因素,我会选择#6 ...每个UDF一个表(实际上,这是#2的变体).此答案专门针对此情况以及所描述的数据分布和访问模式的描述而定制.

优点:

因为您指示某些UDF具有整个数据集的一小部分的值,所以单独的表将为您提供最佳性能,因为该表将仅支持UDF所需的大小.相关指数也是如此.

您还可以通过限制必须为聚合或其他转换处理的数据量来提高速度.出分裂的数据到多个表中,您可以执行一些对UDF数据的聚集和其他统计分析,那么结果加入到主表通过外键来获得非聚集的属性.

您可以使用反映实际数据的表/列名称.

您可以完全控制使用数据类型,检查约束,默认值等来定义数据域.不要低估即时数据类型转换带来的性能损失.此类约束还有助于RDBMS查询优化器开发更有效的计划.

如果您需要使用外键,内置的声明性参照完整性很少会通过基于触发器或应用程序级别约束实施来执行.

缺点:

这可能会创建很多表.实施模式分离和/或命名约定可以缓解这种情况.

操作UDF定义和管理需要更多的应用程序代码.我希望这仍然比原始选项1,3和4更少的代码.

其他考虑因素:

如果有关于UDF分组有意义的数据性质,应该鼓励这样做.这样,这些数据元素可以组合成一个表.例如,假设您有颜色,大小和成本的UDF.数据的趋势是这些数据的大多数实例看起来像

 'red', 'large', 45.03 

而不是

 NULL, 'medium', NULL

在这种情况下,通过组合1个表中的3列,您不会产生明显的速度损失,因为很少的值将为NULL并且您避免再生成2个表,当您需要访问所有3列时,需要的连接数减少2个.

如果您从人口密集且经常使用的UDF访问性能墙,则应考虑将其包含在主表中.

逻辑表设计可以带您到某个点,但是当记录计数变得非常大时,您还应该开始查看您选择的RDBMS提供的表分区选项.

---

Bill Karwin.. 22

我已经写了很多关于这个问题的文章.最常见的解决方案是Entity-Attribute-Value反模式,它类似于您在选项#3中描述的内容. 避免这种设计像瘟疫一样.

当我需要真正动态的自定义字段时,我使用此解决方案的方法是将它们存储在一个XML blob中,这样我就可以随时添加新字段.但为了使其快速,还需要为每个字段创建需要搜索或排序的附加表(每个字段不需要一个表 - 每个可搜索字段只有一个表).这有时被称为倒排索引设计.

您可以在这里阅读2009年关于此解决方案的有趣文章:http: //backchannel.org/blog/friendfeed-schemaless-mysql

或者,您可以使用面向文档的数据库,期望每个文档都有自定义字段.我选择索尔.

1> Phil Helmer..：

---

如果性能是主要考虑因素,我会选择#6 ...每个UDF一个表(实际上,这是#2的变体).此答案专门针对此情况以及所描述的数据分布和访问模式的描述而定制.

优点:

因为您指示某些UDF具有整个数据集的一小部分的值,所以单独的表将为您提供最佳性能,因为该表将仅支持UDF所需的大小.相关指数也是如此.

您还可以通过限制必须为聚合或其他转换处理的数据量来提高速度.出分裂的数据到多个表中,您可以执行一些对UDF数据的聚集和其他统计分析,那么结果加入到主表通过外键来获得非聚集的属性.

您可以使用反映实际数据的表/列名称.

您可以完全控制使用数据类型,检查约束,默认值等来定义数据域.不要低估即时数据类型转换带来的性能损失.此类约束还有助于RDBMS查询优化器开发更有效的计划.

如果您需要使用外键,内置的声明性参照完整性很少会通过基于触发器或应用程序级别约束实施来执行.

缺点:

这可能会创建很多表.实施模式分离和/或命名约定可以缓解这种情况.

操作UDF定义和管理需要更多的应用程序代码.我希望这仍然比原始选项1,3和4更少的代码.

其他考虑因素:

如果有关于UDF分组有意义的数据性质,应该鼓励这样做.这样,这些数据元素可以组合成一个表.例如,假设您有颜色,大小和成本的UDF.数据的趋势是这些数据的大多数实例看起来像

 'red', 'large', 45.03 

而不是

 NULL, 'medium', NULL

在这种情况下,通过组合1个表中的3列,您不会产生明显的速度损失,因为很少的值将为NULL并且您避免再生成2个表,当您需要访问所有3列时,需要的连接数减少2个.

如果您从人口密集且经常使用的UDF访问性能墙,则应考虑将其包含在主表中.

逻辑表设计可以带您到某个点,但是当记录计数变得非常大时,您还应该开始查看您选择的RDBMS提供的表分区选项.

2> Bill Karwin..：

---

我已经写了很多关于这个问题的文章.最常见的解决方案是Entity-Attribute-Value反模式,它类似于您在选项#3中描述的内容. 避免这种设计像瘟疫一样.

当我需要真正动态的自定义字段时,我使用此解决方案的方法是将它们存储在一个XML blob中,这样我就可以随时添加新字段.但为了使其快速,还需要为每个字段创建需要搜索或排序的附加表(每个字段不需要一个表 - 每个可搜索字段只有一个表).这有时被称为倒排索引设计.

您可以在这里阅读2009年关于此解决方案的有趣文章:http: //backchannel.org/blog/friendfeed-schemaless-mysql

或者,您可以使用面向文档的数据库,期望每个文档都有自定义字段.我选择索尔.

---

对于初学者,你会将谁的属性设为NOT NULL？如何在不使所有属性UNIQUE的情况下创建属性UNIQUE？它从那里开始.您最终编写应用程序代码以提供RDBMS已为您提供的功能,甚至必须编写某种映射类以简单地插入逻辑实体记录并将其取回.

---

简短的回答是"不要混合数据和元数据".为`fieldname`或`tablename`创建varchar列是将元数据标识符存储为数据字符串,这是许多问题的开始.另请参阅http://en.wikipedia.org/wiki/Inner-platform_effect

---

@Thomas:在倒排索引设计中,您可以使用标准模式解决方案来处理数据类型,以及UNIQUE和FOREIGN KEY等约束.当您使用EAV时,那些根本不起作用.我同意EAV的倒排索引份额是非关系的特征,因为它支持每行不同的属性,但这是一个妥协点.

---

@thitami,我多年来学到的是*任何*解决方案可能是适合您应用的解决方案.甚至EAV可能是某些特定应用程序最不好的解决方案.在不知道您的查询的情况下,您无法选择优化策略.每种优化都会以牺牲其他查询为代价来改进某些查询.

3> Stefan Stein..：

---

我很可能会创建一个具有以下结构的表:

varchar名称

varchar类型

十进制NumberValue

varchar StringValue

date DateValue

确切的课程类型取决于您的需求(当然还有您正在使用的dbms).您还可以将NumberValue(十进制)字段用于int和booleans.您可能还需要其他类型.

您需要一些指向拥有该值的主记录的链接.为每个主表创建一个用户字段表并添加一个简单的外键可能是最简单,最快速的.这样,您可以轻松快速地按用户字段过滤主记录.

您可能希望获得某种元数据信息.所以你最终会得到以下结果:

表UdfMetaData

int id

varchar名称

varchar类型

表MasterUdfValues

int Master_FK

int MetaData_FK

十进制NumberValue

varchar StringValue

date DateValue

---

无论你做什么,我都不会动态地改变表结构.这是一场维护噩梦.我也不会使用XML结构,它们太慢了.

4> Data Monk..：

---

这听起来像一个非关系解决方案可能更好地解决的问题,如MongoDB或CouchDB.

它们都允许动态架构扩展,同时允许您维护所寻求的元组完整性.

我同意Bill Karwin的观点,EAV模型对你来说并不是一种高效的方法.在关系系统中使用名称 - 值对本质上并不坏,但只有在名称 - 值对完整的元组信息时才能正常工作.使用它时强制您在运行时动态重建表,各种事情开始变得困难.查询成为枢轴维护的练习或强制您将元组重建推送到对象层.

如果没有在对象层中嵌入架构规则,则无法确定null值或缺失值是有效条目还是缺少条目.

您失去了有效管理架构的能力.100个字符的varchar是"value"字段的正确类型吗？200字？它应该是nvarchar吗？这可能是一个艰难的权衡,最后你必须对你的集合的动态性质进行人为限制.像"你只能有x个用户定义的字段,每个字符只能是y字符长".

使用面向文档的解决方案(如MongoDB或CouchDB),可以在单个元组中维护与用户关联的所有属性.因为连接不是问题,所以生活很幸福,因为尽管有炒作,但这两者都不能很好地加入连接.您的用户可以根据需要(或允许)定义尽可能多的属性,这些属性的长度在您达到大约4MB之前难以管理.

如果您有需要ACID级完整性的数据,您可以考虑拆分解决方案,其中高完整性数据存储在关系数据库中,动态数据存储在非关系存储中.

5> Thomas..：

---

即使您提供了添加自定义列的用户,也不一定会查询这些列的效果.查询设计中有许多方面可以使它们表现良好,其中最重要的是对应该存储的内容的正确规范.因此,从根本上说,您是否希望允许用户在不考虑规范的情况下创建模式,并能够快速从该模式中获取信息？如果是这样,那么任何此类解决方案都可以很好地扩展,尤其是如果您想允许用户对数据进行数值分析时,这是不可能的.

选项1

IMO这种方法为您提供了模式,而不了解模式意味着什么是灾难的处方和报表设计者的噩梦.即,您必须拥有元数据才能知道哪些列存储了哪些数据.如果元数据搞砸了,它就有可能阻塞你的数据.此外,它可以很容易地将错误的数据放入错误的列中.("什么？String1包含修道院的名称？我认为这是Chalie Sheen最喜欢的药物.")

选项3,4,5

IMO,要求2,3和4消除了EAV的任何变化.如果你需要对这些数据进行查询,排序或计算,那么EAV就是Cthulhu的梦想,也是你的开发团队和DBA的噩梦.EAV将在性能方面造成瓶颈,无法为您提供快速获取所需信息所需的数据完整性.查询将很快转向交叉表Gordian结.

备选方案2,6

这确实留下了一个选择:收集规范,然后构建模式.

如果客户希望在他们希望存储的数据上获得最佳性能,那么他们需要经历与开发人员合作的过程,以了解他们的需求,以便尽可能高效地存储.它仍然可以存储在与其余表分开的表中,其代码可以根据表的模式动态构建表单.如果您有一个允许列上的扩展属性的数据库,您甚至可以使用它们来帮助表单构建器使用漂亮的标签,工具提示等.所有必要的是添加模式.无论哪种方式,要有效地构建和运行报告,都需要正确存储数据.如果有问题的数据有很多空值,则某些数据库可以存储该类型的信息.例如,SQL Server 2008有一个名为Sparse Columns的功能,专门用于包含大量空值的数据.

如果这只是一包没有进行分析,过滤或排序的数据,我会说EAV的一些变化可能会成功.但是,根据您的要求,最有效的解决方案是获得正确的规范,即使您将这些新列存储在单独的表中并在这些表中动态构建表单也是如此.

稀疏列

6> 小智..：

---

创建多个UDF表，每种数据类型一个。因此，我们会有用于UDFStrings，UDFDates等的表。可能会与＃2相同，并在添加新字段时自动生成View。

根据我的研究，基于数据类型的多个表不会对性能有所帮助。特别是如果您有批量数据，例如具有50多个UDF的20K或25K记录。性能是最差的。

您应该使用具有多个列的单个表，例如：

varchar Name
varchar Type
decimal NumberValue
varchar StringValue
date DateValue