有关数据收集元素集的表设计 [英] Table design about sets of data collection elements

问题描述

让我知道您是否需要其他信息，因为这是我第一次在论坛上发帖。

Let me know if you need additional information as this is my first post to the forums.

该设计用于临床研究。最简单的解释方法是为一个方案举例，该方案适用于某种形式或形式的所有研究/方案。说我有

The design is for clinical studies. Easiest way to explain would be to give an example to a scenario which applies to all studies/protocols in some shape or form. Say I have:

• Study1，Study2，Study3


• Study1具有Protocol1，Protocol2，Protocol3


• 每个协议都有数据收集（形式，问题和样本收集的集合，在研究和/或协议中可能会重叠）


• 所有这些数据收集计划在诊所就诊时完成。

• Study1, Study2, Study3
• Study1 has Protocol1, Protocol2, Protocol3
• Each protocol has "data collection" (set of forms, questions and sample collections, which can overlap across studies and/or protocols)
• All these data collections are scheduled to be completed in clinic visits.

我可以建立类似于调查表的研究，方案和问题之间的所有关系/调查设计结构。但这是协议定义以及如何将协议链接回数据收集项的棘手问题，例如：

I can build all the relationships between Study, Protocol and Questions similar to a questionnaire/survey design structure. However this is where it gets tricky with the protocol definitions and how to link protocols back to the data collection items, some examples are:

• Protocol1的表格需要在注册后每3个月填写一次，直到24个月，然后每6个月填写一次。


• Protocol1在6个月，15个月，27个月然后每年一次收集样本。
li>

• Protocol1还有另一个样本收集，需要在4、5和6岁时进行。


• 一些数据收集项目正在报名中，有些是

• Protocol1 has a form that needs to be filled every 3 months after enrollment to 24 months, then every 6 months.
• Protocol1 has a sample collection at 6month, 15month, 27month and then annually.
• Protocol1 has another sample collection which needs to happen at the age of 4, 5 and 6.
• Some data collection items are at the enrollment, some are every visit, etc..

我想要的是为特定患者进行该门诊的待办事项清单基于研究协议数据收集之间的所有关系，但我不确定如何为后端的协议定义这些条件标准以进行查询？还是我试图做一些不切实际的事情？

What I want is to have a "To-do list" for that clinic visit for a specific patient based on all the relationships between study-protocol-datacollection but I am not sure how to define these conditional criteria for protocols at the back-end to be able to query? or am I trying to do something unrealistic?

**我正在使用SQL Server

**I am using SQL Server by the way

推荐答案

我自己也建立了类似的模式，我建议您采用生成所有将来计划日期并将其存储在与患者链接的表格中的方法，而不是尝试即时计算。我认为这将为您省去很多麻烦和麻烦的查询。例如，您可能有一个这样定义的表：

Having set up similar schemas myself I would recommend you take the approach of generating all future schedule dates and storing these in a table linked to patient rather than try to calculate these "on the fly". I think this will save you a lot of headaches and difficult queries. For example you could have a table defined like this:

CREATE TABLE PatientSchedule
(
     PatientId INT, /* foreign key into Patient table */
     ProtocolId INT, /* foreign key into Protocol table */
     StudyId INT,  /* foreign key into Study table */
     DataCollectionId INT, /* foreign key into DataCollection table */
     SampleCollectionId INT, /* Foreign key into sample table */
     ScheduleDate DATE
)

（显然，您需要根据自己的特殊关系来对此进行调整，但希望您能明白这一点）。

(you'll obviously need to adapt this based on your particular relationships but hopefully you get the idea).

然后，当特定患者登记参加特定的研究/方案时，可以为该患者预先填充此表格，并插入所有计划的日期，直到将来可能成为实际最大的日期为止。

This table can then be pre-populated for a particular patient when they enroll for a particular Study/Protocol inserting all scheduled dates upto whatever date is likely to be the realistic maximum in the future.

对于特定诊所，访问待办事项列表查询应该像下面这样简单：

For a particular clinic visit the "To Do List" query should then be as simple as something like:

SELECT * FROM PatientSchedule
WHERE PatientId = ??
AND ScheduleDate = <clinic visit date>

如果日后由于任何原因更改了计划日期，则更新更新日期应该不会太困难PatientSchedule表。

If schedule dates are changed for any reason in the future it shouldn't be too difficult to update the PatientSchedule table.

这篇关于有关数据收集元素集的表设计的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！