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

Question

我的 friend 正在构建一个供不同独立医疗单位使用的产品。

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

让我们假设这些数据保存在名为 exams 的表中，其中包含 temperature、pressure 等列...(以及 id、patient_id 和 timestamp)。大多数测量值存储为 float ，但有些是其他类型(字符串、整数...)

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

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

他倾向于一个单独的表(比如说一个表 custom_exam_data，其中包含 exam_id、custom_field_id、float_value, string_value, ...)

我担心这会使搜索更难实现且效率更低。

我倾向于直接修改考试表(同时避免列名与某些方案发生冲突，例如在所有自定义字段前加下划线或将它们命名为 custom_1，...)

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

希望有更多经验的人可以考虑这个问题。

注意事项:

• 他正在使用 Ruby on Rails，但我认为这个问题几乎与框架无关，除了他只在 SQL 数据库中寻找解决方案这一事实。

• 我稍微简化了这个问题，因为自定义字段需要对多个表可用，但我相信这不会真正影响采取的方向。

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

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

Answer 1

这些年我遇到过很多次这种情况，我同意你最初的想法，直接修改数据库表，使用动态SQL生成语句。

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

SQL Server 提供了 INFORMATION_SCHEMA 表，可让您在运行时创建、查询和修改表架构。它具有完整的类型检查、约束、事务、计算以及您需要的一切已经内置，不要重新发明它。