在SQL数据库中存储自定义字段的首选方法是什么?

多年来,我不得不多次处理这种情况,我同意您最初的想法,即直接修改DB表,并使用动态SQL生成语句。

首先,创建字符串userattribute或键/值列听起来很有吸引力,但它会导致 inner-platform effect 最终不得不重新实现外键、数据类型、约束、事务、验证、排序、分组、计算等。在RDBMS中。您也可以只使用平面文件,而不使用SQL。

SQL Server提供 INFORMATION_SCHEMA 允许您在运行时创建、查询和修改表架构的表。这有完整的类型检查、约束、事务、计算,以及您已经内置的所有东西,不要重新发明它。

奇怪的是,当有一个记录良好的模式可供使用时,有这么多人为此提出了特别的解决方案:

Entity-Attribute-Value (EAV) Model

两种选择是XML和嵌套集。XML易于管理,但通常速度较慢。嵌套集通常需要某种类型的专用数据库扩展,而不会造成混乱,比如SQL Server 2005+中的clr类型。它们违反了第一种正常形式,但仍然是执行速度最快的解决方案。

Microsoft Dynamics CRM通过在每次更改时更改数据库设计来实现这一点。我觉得很讨厌。

我想说一个更好的选择是考虑一个属性表。尽管这些经常被人反对,但它给了您所需的灵活性,并且您可以始终使用动态SQL创建视图,以再次将数据透视出来。只要确保在创建这些视图时始终使用左联接和FK,这样查询优化器就可以更好地完成它的工作。

我看到你朋友的想法在商业会计软件包中得到了应用。表被分为两部分,第一部分是系统单独定义的包含字段,第二部分是包含字段,如用户_String1、用户_String2、用户_Float1等。这些表通过标识值进行链接(当一条记录插入主表时,第二部分中会插入一条具有相同标识的记录)。每个需要用户字段的表都是这样拆分的。

嗯,每当我需要在数据库字段中存储一些未知类型时,我通常将其存储为字符串,根据需要对其进行序列化,并且还存储数据类型。

这样,您就可以拥有任何类型的数据,使用任何类型的数据库。

我倾向于将度量存储在数据库中,作为一个字符串(varchar),另一列标识度量类型。我的理由是,它可能是以字符串形式从用户界面传入的,在存储用户输入get之前,转换为任何其他数据类型都可能导致损坏。

缺点是,当您使用某种度量标准来过滤结果集时,仍然需要执行强制转换,但至少存储和持久性机制不会引入损坏。

我不能告诉你最好的方法,但是我可以告诉你Drupal是如何在使用目前可用的标准RDBMS的同时实现一种无模式结构的。

一般的想法是有一个带有字段列表的模式表。每行实际上只有两列,“table”:字符串列和“column”:字符串列。对于这些列中的每一列,它实际上定义了一个完整的表,其中只有一个ID和该列的实际数据。

真正的诀窍是,当您处理数据时,从列出所有可能列的bundle表中不会有超过一个的join,这样您最终不会像您想象的那样损失速度。这也将使您能够扩展到比少数医疗公司远得多的领域,而不像您提议的自定义前缀。

MySQL非常快地返回列数很少的短行的行数据。这样一来,这个方案结束得相当快,同时允许您有很大的灵活性。

至于搜索,我的建议是索引页面内容而不是数据库内容。使用solr解析呈现的页面,并保留到实际页面的链接,而不是使用智能SQL在数据库中搜索。

定义两个新表: custom_exam_schema 和 custom_exam_data .

自定义检查数据 有一个 exam_id 列,再为每个自定义属性添加一列。

自定义检查模式 会有一个 行 描述如何解释 柱 的 自定义检查数据 表。它有像这样的柱子 name , type , minValue , maxValue 等。

例如,要创建一个自定义字段来跟踪一个人的手指数,您可以添加 ('fingerCount', 'number', 0, 10) 到 自定义检查模式 然后添加一个名为 fingerCount 到 exam 表。

有人可能会说在运行时更改数据库模式是不好的,但我认为配置这些自定义字段是设置的一部分,不会太频繁。尽管如此,这个方法允许您随时处理更改,并且不会有干扰核心表模式的风险。

我将把这些自定义字段存储在一个表中,其中每个记录(数据类型、数据值、数据单元)将在一行中使用。所以从一个样本到数据会有一个关系。您还可以创建一个表来记录您将使用的所有类型的CUTSOM。例如:

create table DataType
(
id int primary key,
name varchar(100) not null unique
description text,
uri varchar(255) //<-- can be used for an ONTOLOGY
)


create table DataRecord
(
id int primary key,
sample_id int not null,//<-- reference to the sample
dataType_id int not null, //<-- references DataType
value varchar(100),//<-- the value as string
unit varchar(50)//<-- g, mg/ml, etc... but it could also be a link to a table describing the units just like DataType
)

假设您朋友的数据库必须存储来自多个来源的数据值,如人口统计学值、诊断、干预、生理学值、生理学检查值、住院值等。

他可能还需要定义选项,比如说他的数据库缺少比赛,而部队工作人员需要患者的比赛(不同的比赛更不可能患上某些疾病),他们可能需要在几个选项中使用下拉列表。

我建议使用其他具有这些选项的表,或者您只使用“自定义域选项”表,在某些情况下,该表完全相同,但具有不同的名称。

考虑到数据库:
-需要灵活
-可以添加和自定义来自多个表的数据
-为了分发和统一起见,您可能希望保持数据库主结构的完整性。
-该数据必须有限制、警报和警告
-数据必须有单位(10公斤或10磅)?
-这些数据可以有多种选择
-这些数据可以具有不同的权限(从简单用户到管理员)
-这些数据可能需要在不修改代码的情况下生成报告(自动化)
-这些数据可能需要在不修改代码的情况下在系统内进行交叉引用分析。

自定义表是我的解决方案,修改每个表的风险太大。