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

我的要求是:

  • 需要能够动态添加任何数据类型的用户定义字段
  • 需要能够快速查询 UDF
  • 需要能够基于数据类型在 UDF 上进行计算
  • 需要能够基于数据类型对 UDF 进行排序

其他资料:

  • 我主要是找演员
  • 有几百万个主记录可以附加 UDF 数据
  • 当我最后一次检查时，在我们当前的数据库中有超过5000万条 UDF 记录
  • 大多数情况下，UDF 只附加到几千个主记录，而不是所有主记录
  • UDF 不是连接或用作键，它们只是用于查询或报表的数据

选择:

  1. 用 StringValue1、 StringValue2... IntValue1、 IntValue2等创建一个大表。我讨厌这个想法，但是如果有人告诉我这个想法比其他想法更好，为什么，我会考虑的。

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

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

  4. 创建多个 UDF 表，每个数据类型一个。因此，我们将为 UDFString、 UDFDates 等提供表。可能会做同样的 # 2和自动生成一个视图，任何时候一个新的领域得到添加

  5. XML 数据类型 XML DataType? ？我以前没有用过这些东西，但是看到过它们被提到过。不确定他们是否会给我想要的结果，尤其是在性能方面。

  6. 还有别的事吗？

62354 次浏览
小开

这是一个有问题的情况，没有一个解决方案看起来是“正确的”。然而，从简单性和性能方面来说，选项1可能是最好的。

这也是一些商业企业应用程序中使用的解决方案。

剪辑

另一个现在可用的选项是在数据库中使用 json 字段，但是在最初提出这个问题时这个选项还不存在(或者至少还不成熟)。

许多关系数据库现在支持基于 json 的字段(可以包括子字段的动态列表) ，并允许对它们进行查询

后退

Mysql

小开

我有经验，或1,3,4，他们都结束了要么混乱，与它不清楚的数据是什么或真正复杂的一些软分类，以分解成动态类型的记录的数据。

我想尝试使用 XML，您应该能够对 XML 的内容强制执行模式，以检查数据类型等，这将有助于保存不同的 UDF 数据集。在较新版本的 SQL 服务器中，可以对 XML 字段进行索引，这将有助于提高性能。 (见 http://blogs.technet.com/b/josebda/archive/2009/03/23/sql-server-2008-xml-indexing.aspx)

小开

SharePoint 使用选项1并具有合理的性能。

小开

如果您正在使用 SQLServer，请不要忽略 sqlvariable 类型。速度很快，应该能完成你的工作。其他数据库可能有类似的东西。

由于性能原因，XML 数据类型不是很好。如果您在服务器上进行计算，那么您必须不断地对这些进行反序列化。

选项1听起来很糟糕，看起来很糟糕，但是性能方面可能是你最好的选择。我之前已经创建了包含名为 Field00-Field99的列的表，因为这样的性能是无与伦比的。您可能还需要考虑 INSERT 性能，在这种情况下，这也是需要考虑的问题。如果您希望它看起来整洁，您总是可以在这个表上创建视图！

小开
最佳答案

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

优点:

  1. 因为您指出一些 UDF 对... 的一小部分有价值 整个数据集，一个独立的 桌子会给你最好的 因为那张桌子 只要它需要多大就多大 相关的索引也是如此。

  2. 通过限制必须为聚合或其他转换处理的数据量，还可以提高速度。将数据分割成多个表，可以对 UDF 数据执行一些聚合和其他统计分析，然后通过外键将结果连接到主表，以获得非聚合属性。

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

  4. 您可以完全控制使用数据类型, 检查约束、默认值等。 不要低估即时数据类型转换对性能的影响 约束也有助于 RDBMS 查询 优化器更有效地发展 计划

  5. 如果你需要使用外语 键，内置的声明性 参照物 完整性很少超过 基于触发器或应用程序级别 约束强制

缺点:

  1. 这样可以创建很多表。 强制模式分离和/或 变数命名原则会缓解 这个

  2. 还有更多的应用程序代码 需要操作 UDF 定义 和管理层，我希望这是 所需的代码仍然少于 原始选项1、3和4

其他考虑:

  1. 如果有任何关于 数据的性质 有意义的 UDF 被分组, 这是应该鼓励的，这样的话, 这些数据元素可以组合在一起 变成一个单一的表格。例如, 假设您有用于颜色的 UDF, 规模和成本 数据就是大多数这样的实例 数据看起来像

     'red', 'large', 45.03

    而不是

     NULL, 'medium', NULL

    在这种情况下，你不会招致 明显的速度惩罚 将3列合并到1个表中 因为很少有值是 NULL 和 你避免多做两张桌子, 这是少2个连接时需要 您需要访问所有3列

  2. 如果您碰到了来自 人口密集且 经常使用的，那么应该是 考虑列入 主桌

  3. 逻辑表设计可以带您到 某一时刻，但是当记录 数量变得非常庞大，你也 应该看看是哪张桌子 您选择的 RDBMS 提供了分区选项。

小开

我很可能会创建一个结构如下的表:

  • Varchar 姓名
  • Varchar 类型
  • 十进制数值
  • Varchar StringValue
  • 日期日期价值

确切的类型当然取决于您的需要(当然也取决于您正在使用的 dbms)。还可以对 int 和 boolean 使用 NumberValue (十进制)字段。您可能还需要其他类型。

您需要一些链接到拥有该值的主记录。为每个主表创建一个用户字段表并添加一个简单的外键可能是最简单和最快的方法。通过这种方式，您可以根据用户字段轻松快速地过滤主记录。

你可能想要一些元数据信息:

Table UdfMetaData 表 UdfMetaData

  • Int id
  • Varchar 姓名
  • Varchar 类型

表 MasterUdfValue

  • Int Master _ FK
  • Int MetaData _ FK
  • 十进制数值
  • Varchar StringValue
  • 日期日期价值

无论你做什么，我会 没有动态改变表结构。简直是维修的噩梦。我也会使用 没有的 XML 结构，它们太慢了。

小开

我有关于这个问题的 写好了。最常见的解决方案是 Entity-Attribute-Value 反模式，它类似于您在选项 # 3中所描述的。像避开瘟疫一样避开这个设计.

当我需要真正动态的自定义字段时，我使用的这个解决方案是将它们存储在一个 XML 块中，这样我就可以随时添加新字段。但是为了提高速度，还需要为需要搜索或排序的每个字段创建额外的表(不需要为每个字段创建一个表——只需为每个 可搜索字段创建一个表)。这有时称为倒索引设计。

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

或者您可以使用面向文档的数据库，在该数据库中，预计每个文档都有自定义字段。我会选择 太阳。

小开

我过去曾经非常成功地使用过这些选项(选项6? :)。

我创建了一个模型供用户使用(存储为 xml 并通过自定义建模工具公开) ，并从模型生成的表和视图中将基表与用户定义的数据表连接起来。因此，每种类型都有一个包含核心数据的基表和一个包含用户定义字段的用户表。

以文档为例: 典型的字段是名称、类型、日期、作者等。这个应该放在核心桌上。然后用户将使用他们自己的字段定义他们自己的特殊文档类型，例如 Contract_ end _ date、 new _ children 等等。对于这个用户定义的文档，将有一个核心文档表，xContracttable，连接在一个公共的主键上(因此，在核心表的主键上，xContractmain 键也是外键)。然后，我将生成一个视图来包装这两个表。查询速度快时的性能。还可以将其他业务规则嵌入到视图中。这招对我很管用。

小开

即使您允许用户添加自定义列，也不一定能够很好地查询这些列。在查询设计中，有许多方面允许它们执行良好，其中最重要的是关于应该首先存储什么的适当规范。因此，从根本上说，您是否希望允许用户不考虑规范就创建模式，并且能够从该模式快速获取信息？如果是这样，那么任何这样的解决方案都不太可能扩展得很好，尤其是如果你想让用户对数据进行数值分析处理的话。

选择一

IMO 这种方法为您提供了一个模式，但是您并不知道这个模式意味着什么，这对于报表设计人员来说是一个灾难和噩梦。也就是说，您必须拥有元数据才能知道哪个列存储哪些数据。如果那个元数据被搞乱了，它就有可能冲洗你的数据。另外，它使得将错误的数据放入错误的列变得非常容易。(“什么？String1包含修道院的名称？我以为那是查莉 · 辛最喜欢的毒品。”)

选择三，四，五

国际海事组织，要求2,3和4消除任何 EAV 的变化。如果您需要对这些数据进行查询、排序或计算，那么 EAV 是克苏鲁的梦想，也是您的开发团队和 DBA 的噩梦。EAV 将在性能方面造成瓶颈，并且不会为您提供快速获取所需信息所需的数据完整性。查询将很快转向交叉刺戈尔迪结。

选项二，六

这实际上留下了一个选择: 收集规范，然后构建模式。

如果客户希望在他们希望存储的数据上获得最佳性能，那么他们需要通过与开发人员一起工作的过程来了解他们的需求，以便尽可能有效地存储数据。它仍然可以存储在一个独立于其他表的表中，并使用基于表的模式动态构建表单的代码。如果您有一个允许扩展列属性的数据库，那么您甚至可以使用这些属性来帮助表单构建器使用漂亮的标签、工具提示等，这样就只需要添加模式即可。无论哪种方式，要有效地生成和运行报表，都需要正确地存储数据。如果有问题的数据有很多空值，那么一些数据库就能够存储这种类型的信息。例如，SQLServer2008有一个名为稀疏列(SparseColumns)的特性，专门用于具有大量空值的数据。

如果这仅仅是一包数据，不需要对其进行分析、过滤或排序，我想 EAV 的一些变体可能会起作用。但是，考虑到您的需求，最有效的解决方案将是获得适当的规范，即使您将这些新列存储在单独的表中，并在这些表之外动态构建表单。

稀疏柱

小开

这个问题似乎可以通过非关系型解决方案(如 MongoDB 或 CouchDB)更好地解决。

它们都允许动态模式扩展，同时允许您维护所寻求的元组完整性。

我同意 Bill Karwin 的观点，EAV 模型并不适合你。在关系系统中使用名称-值对本质上并不坏，但只有在名称-值对构成完整的信息元组时才能正常工作。当使用它时，您必须在运行时动态重新构建一个表，所有的事情都开始变得困难起来。查询成为一种维护轴心的练习，或者迫使您将元组重构推进到对象层。

如果不在对象层中嵌入模式规则，就无法确定空值或缺失值是有效条目还是缺少条目。

您将失去有效管理模式的能力。100个字符的 varchar 是“ value”字段的正确类型吗？200个字符？应该改成 nvarchar 吗？这可能是一个艰难的权衡，最终你不得不对你的集合的动态特性设置人为的限制。比如“您只能有 x 个用户定义的字段，并且每个字段只能是 y 字符长度。

使用面向文档的解决方案，比如 MongoDB 或 CouchDB，您可以在一个元组中维护与用户关联的所有属性。因为加入不是一个问题，所以生活是幸福的，因为这两个人都不擅长加入，尽管有大肆宣传。您的用户可以定义尽可能多的属性(或者您将允许) ，这些属性的长度直到您达到大约4MB 时才会变得难以管理。

如果您的数据需要 ACID 级别的完整性，那么可以考虑将解决方案分开，高完整性数据存储在关系数据库中，动态数据存储在非关系存储中。

小开

在注释中，我看到您说 UDF 字段将转储用户没有正确映射的导入数据。

也许另一种选择是跟踪每个用户创建的 UDF 的数量，并强制他们重用字段，方法是说他们最多可以使用6个(或其他一些同样随机的限制)自定义字段。

当您遇到这样的数据库结构问题时，最好回到应用程序的基本设计(在您的情况下是导入系统) ，并对其进行更多的限制。

现在我要做的是选项4(EDIT) ，添加一个指向用户的链接:

general_data_table
id
...




udfs_linked_table
id
general_data_id
udf_id




udfs_table
id
name
type
owner_id --> Use this to filter for the current user and limit their UDFs
string_link_id --> link table for string fields
int_link_id
type_link_id

现在确保创建视图以优化性能并使索引正确。这种规范化级别使得 DB 占用更小，但是您的应用程序更复杂。

小开
  1. 创建多个 UDF 表，每个数据类型一个。因此，我们将为 UDFString、 UDFDates 等提供表。可能会做同样的 # 2和自动生成一个视图，任何时候一个新的领域得到添加

根据我的研究，多表的数据类型不会对你的性能有帮助。特别是如果您有大量数据，比如20K 或25K 记录和50 + UDF。表现最差。

您应该使用具有多个列的单个表，比如:

varchar Name
varchar Type
decimal NumberValue
varchar StringValue
date DateValue
小开

我们的数据库支持 SaaS 应用程序(帮助台软件) ，其中用户有超过7000个“自定义字段”:

  1. 表 (EntityID, FieldID, Value)为 寻找的数据
  2. entities表中的一个 JSON 字段，该字段保存所有实体值，用于 展示数据。(这样您就不需要一百万个 JOIN 来获得值)。

您可以像 这个答案建议的那样，进一步将 # 1拆分为“每个数据类型一个表”，这样您甚至可以对 UDF 进行索引。

附注: 为“实体-属性-价值”方法辩护的几句话，每个人都在抨击这种方法。几十年来，我们一直使用 # 1而不使用 # 2，而且效果很好。有时候是生意上的决定。你是否有时间重写你的应用程序，重新设计数据库，或者你可以在云服务器上投入一些钱，这些服务器现在真的很便宜？顺便说一下，当我们使用 # 1方法时，我们的数据库拥有数百万个实体，被成千上万的用户访问，而一个16GB 的双核数据库服务器运行得很好

小开

我会推荐 # 4，因为这种类型的系统被用在 Magento，这是一个高度认可的电子商务 CMS 平台。使用单个表定义使用 菲尔德和 标签列的自定义字段。然后，为每种数据类型创建单独的表，每个表中都有一个索引，该索引由 菲尔德和数据类型 价值列进行索引。然后，在您的查询中，使用以下内容:

SELECT *
FROM FieldValues_Text
WHERE fieldId IN (
SELECT fieldId FROM Fields WHERE userId=@userId
)
AND value LIKE '%' + @search + '%'

在我看来，这将确保用户定义类型的最佳性能。

根据我的经验，我曾经在几个 Magento 网站工作过，这些网站每月为数百万用户提供服务，托管数千个具有自定义产品属性的产品，而且数据库可以轻松处理工作量，甚至可以进行报告。

对于报告，可以使用 PIVOT将 菲尔兹表 标签值转换为列名，然后将每个数据类型表的查询结果透视到这些透视列中。


提交答案