代码之家  ›  专栏  ›  技术社区  ›  Marc-André Lafortune

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

  •  16
  • Marc-André Lafortune  · 技术社区  · 15 年前

    我的朋友正在制造一种产品,供不同的独立医疗单位使用。

    数据库存储了大量不同时间的测量数据,如温度、血压等。

    让我们假设这些是放在一个叫做 exams 带列 temperature ,请 pressure 等。以及 id , patient_id timestamp )大多数度量值都存储为浮点数,但有些是其他类型的(字符串、整数…)

    虽然这些测量中的许多是由他们的产品处理的,但它需要允许不同的医疗单位记录和处理其他自定义测量。一个非常漂亮的用户界面允许管理员编辑这些自定义字段,指定它们的名称、类型、可能的值范围等…

    他不确定如何存储这些自定义字段。

    他向一张单独的桌子(比如说一张桌子)倾斜 custom_exam_data 像这样的字段 exam_id , custom_field_id ,请 float_value , string_value ,…)

    我担心这会使搜索变得更难实现,也会降低效率。

    我倾向于直接修改检查表(同时使用一些方案避免列名冲突,例如用下划线在所有自定义字段前加前缀或将它们命名为自定义的_1,…)

    他担心动态修改数据库以及为每个医疗单位使用不同的模式。

    希望有更多经验的人能参与这个问题。

    笔记:

    • 他正在使用RubyonRails,但我认为这个问题基本上与框架无关,除了他只在SQL数据库中寻找解决方案。

    • 由于自定义字段需要可用于多个表,因此我将问题简化了一点,但我相信这不会真正影响要采取的方向。

    • (添加)非常通用的报告模块需要搜索、排序、生成统计信息等。因此,需要将此数据存储在适当类型的列中

    • (已添加)将筛选标准字段和自定义字段的用户输入。例如,将在给定范围内检查数字(温度不能为-12或+444),等等…因此,转换到适当的SQL类型不是问题。

    10 回复  |  直到 15 年前
        1
  •  9
  •   Dour High Arch    15 年前

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

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

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

        2
  •  7
  •   Aaronaught    15 年前

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

    Entity-Attribute-Value (EAV) Model

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

        3
  •  4
  •   Rob Farley    15 年前

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

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

        4
  •  1
  •   GSerg    15 年前

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

        5
  •  1
  •   Paulo Santos    15 年前

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

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

        6
  •  1
  •   grenade    15 年前

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

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

        7
  •  1
  •   Chuck Vose    15 年前

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

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

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

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

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

        8
  •  1
  •   benzado    15 年前

    定义两个新表: custom_exam_schema custom_exam_data .

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

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

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

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

        9
  •  0
  •   Pierre    15 年前

    我将把这些自定义字段存储在一个表中,其中每个记录(数据类型、数据值、数据单元)将在一行中使用。所以从一个样本到数据会有一个关系。您还可以创建一个表来记录您将使用的所有类型的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
  •  0
  •   Greg    15 年前

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

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

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

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

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