在SQL数据库中存储自定义字段的首选方式是什么?[英] What is the preferred way to store custom fields in a SQL database?

本文是小编为大家收集整理的关于在SQL数据库中存储自定义字段的首选方式是什么?的处理/解决方法,可以参考本文帮助大家快速定位并解决问题,中文翻译不准确的可切换到English标签页查看源文。

问题描述

我的朋友正在构建一个产品要由不同的独立医疗单位使用.

数据库存储在不同时间进行的大量测量,例如温度,血压等...

让我们假设这些保存在一个名为exams的表中,temperature,pressure,等等(以及id,id,patient_id和timestamp).大多数测量值存储为浮子,但有些是其他类型的(字符串,整数...)

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

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

他倾向于单独的桌子(例如exam_id,custom_field_id,float_value,float_value,float_value,string_value,...)的字段custom_exam_data)

我担心这会使搜索更加难以实现和效率降低.

我倾向于直接修改检查表(同时避免使用某些方案对列名进行冲突,例如将所有自定义字段带有下划线或将其命名Custom_1,...)

他担心动态修改数据库并为每个医疗单元具有不同的模式.

希望有些人在这个问题上有更多经验的人.

注意:

  • 他在轨道上使用Ruby,但我认为这个问题几乎是框架不可知的,除了他仅在SQL数据库中寻找解决方案的事实.

  • 我有点简化了问题,因为自定义字段需要多个表格,但我相信这确实不会影响采取方向.

  • (添加)非常通用的报告模块需要搜索,排序,生成统计信息等. >

  • (添加)将对标准字段以及自定义字段进行过滤用户输入.例如,将在给定范围内检查数字(不能具有-12或+444的温度)等...因此,转换为适当的SQL类型不是问题.

推荐答案

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

创建字符串userAttribute或键/值列首先听起来很吸引人,但它会导致 inner-platporform "> inner-platporform "> inner-platform "> inner-plat效应效果最终您必须重新实现外国密钥,数据类型,约束,交易,验证,排序,分组,计算等.在您的RDBMS内部.您最好只使用平面文件而不是SQL.

SQL Server提供INFORMATION_SCHEMA表,可让您在运行时创建,查询和修改表格架.这具有完整的检查,约束,交易,计算以及您已经内置的所有内容,不要重新发明它.

其他推荐答案

当有充分记录的模式时,这么多人为此提出了临时解决方案:

entity-attribute-value(eav)模型

两个替代方案是XML和嵌套集. XML更易于管理,但通常放慢速度.嵌套集通常需要某种类型的专有数据库扩展名,而无需弄乱,例如SQL Server 2005+中的CLR类型.它们违反了第一范围的形式,但仍然是最快的解决方案.

其他推荐答案

Microsoft Dynamics CRM每次进行更改时都会更改数据库设计来实现这一目标.我认为是讨厌的.

我会说一个更好的选择是考虑一个属性表.即使这些经常皱眉,它也可以为您提供所需的灵活性,并且您可以随时使用动态SQL创建视图,以再次将数据转移.只需确保在创建这些视图时始终使用左JON和FKS,以便查询优化器可以更好地完成工作即可.

本文地址:https://www.itbaoku.cn/post/597549.html

问题描述

My friend is building a product to be used by different independent medical units.

The database stores a vast collection of measurements taken at different times, like the temperature, blood pressure, etc...

Let us assume these are held in a table called exams with columns temperature, pressure, etc... (as well as id, patient_id and timestamp). Most of the measurements are stored as floats, but some are of other types (strings, integers...)

While many of these measurements are handled by their product, it needs to allow the different medical units to record and process other custom measurements. A very nifty UI allows the administrator to edit these customs fields, specify their name, type, possible range of values, etc...

He is unsure as to how to store these custom fields.

He is leaning towards a separate table (say a table custom_exam_data with fields like exam_id, custom_field_id, float_value, string_value, ...)

I worry that this will make searching both more difficult to achieve and less efficient.

I am leaning towards modifying the exam table directly (while avoiding conflicts on column names with some scheme like prefixing all custom fields with an underscore or naming them custom_1, ...)

He worries about modifying the database dynamically and having different schemas for each medical unit.

Hopefully some people which more experience can weigh in on this issue.

Notes:

  • he is using Ruby on Rails but I think this question is pretty much framework agnostic, except from the fact that he is only looking for solutions in SQL databases only.

  • I simplified the problem a bit since the custom fields need to be available for more than one table, but I believe this doesn`t really impact the direction to take.

  • (added) A very generic reporting module will need to search, sort, generate stats, etc.. of this data, so it is required that this data be stored in the columns of the appropriate type

  • (added) User inputs will be filtered, for the standard fields as well as for the custom fields. For example, numbers will be checked within a given range (can't have a temperature of -12 or +444), etc... Thus, conversion to the appropriate SQL type is not a problem.

推荐答案

I've had to deal with this situation many times over the years, and I agree with your initial idea of modifying the DB tables directly, and using dynamic SQL to generate statements.

Creating string UserAttribute or Key/Value columns sounds appealing at first, but it leads to the inner-platform effect where you end up having to re-implement foreign keys, data types, constraints, transactions, validation, sorting, grouping, calculations, et al. inside your RDBMS. You may as well just use flat files and not SQL at all.

SQL Server provides INFORMATION_SCHEMA tables that let you create, query, and modify table schemas at runtime. This has full type checking, constraints, transactions, calculations, and everything you need already built-in, don't reinvent it.

其他推荐答案

It's strange that so many people come up with ad-hoc solutions for this when there's a well-documented pattern for it:

Entity-Attribute-Value (EAV) Model

Two alternatives are XML and Nested Sets. XML is easier to manage but generally slow. Nested Sets usually require some type of proprietary database extension to do without making a mess, like CLR types in SQL Server 2005+. They violate first-normal form, but are nevertheless the fastest-performing solution.

其他推荐答案

Microsoft Dynamics CRM achieves this by altering the database design each time a change is made. Nasty, I think.

I would say a better option would be to consider an attribute table. Even though these are often frowned upon, it gives you the flexibility you need, and you can always create views using dynamic SQL to pivot the data out again. Just make sure you always use LEFT JOINs and FKs when creating these views, so that the Query Optimizer can do its job better.