医学价值观：1个超级考试表或许多考试表 [英] Medical Values: 1 Super Exam Table or Lots of Exam Tables

问题描述

在医疗领域，患者有大量考试（HbA1C，Lipid，Renal等），并且都基本符合ExamX（ID，ID_Patient，Date，Value）的格式。

In the medical domain, a patient has a ton of exams (HbA1C, Lipid, Renal, etc..) and they all basically follow the format of ExamX(ID, ID_Patient, Date, Value)

但是，您可以通过使用SuperExam（ID，ID_Patient，ExamType，Date，Value）从多个表中选择一个。

But you can go from many tables to one by using SuperExam(ID, ID_Patient, ExamType, Date, Value)

值在第二个表中不是离散的。一些考试有多个值，因此需要进行分隔。 Ex值= .9,105,108，.4

One note, value would not be discrete in the second table. Some exams have multiple values, so they would need to be delimited. Ex value= .9,105,108,.4

我通常使用第一种格式，但是我也看过第二种结构。我猜到使用第二个版本，您将无需更改数据库模式，就可以更灵活地使用数据库，但似乎报告将是一场噩梦。

I've typically used the first format, but I have seen the second structure several times too. I guess with the second version, you would have a lot more flexibility with your data without having to change the db schema, but it seems like reporting would be a nightmare.

哪个是更好的设计？

推荐答案

很可能你要采取EAV方法（如另一个所述）回答）并创建一个数据字典。然后，您不仅可以存储考试的具体数据，还可以使用表结构来存储在考试中定义数据的数据（考试模板，值之间的关系等）。您基本上使用RDBMS的关系方面来创建您自己的关系系统。

In all likelihood you're going to have to take the EAV approach (as described in another answer) and create a data dictionary. You would then not only be storing the specific data for an exam, but you would also have a table structure for storing data that defines the data in an exam (exam templates, relationships between values, etc.). You're basically using the relational aspect of your RDBMS to create your own relationship system.

这听起来很复杂 - 而且在某种程度上，虽然没有那么多这似乎是可以看出的 - 但是如果您打算将该医疗系统扩展和维护，长远而言值得。

This sounds complex--and it is, to an extent, though not as much as it might seem--but it's worth it in the long run if you intend for this medical system to be scalable and maintainable.

不要在一列中存储多个值。永远由于任何原因。

Do not store multiple values in one column. Ever. For any reason.

这是一个简单的例子...

Here's a quick example...

exam:
    exam id,
    date,
    patient id,
    ...other miscellaneous scalar data thats 1-1 with an exam

exam value:
    exam value id,
    exam id,
    value id,
    value

这是您的BASIC结构，用于存储考试数据。然后你有...

This is your BASIC structure for storing exam data. Then you have...

value:
    value id,
    description

group:
    group id,
    description

group value:
    group value id,
    group id,
    value id,
    sort order

再次，非常基本和初步，但它希望能让您一瞥我在说您可能希望进一步了解具体特定群体的特定考试类型，然后为考试分配考试类型，但我会将其留给您。

Again, very basic and rudimentary, but it hopefully gives you a glimpse into what I'm talking about. You may want to take it a step further and define specific exam types that have particular groups, then assign an exam type to an exam, but I'll leave that up to you.

这篇关于医学价值观：1个超级考试表或许多考试表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！