I am dealing with a specific use case for analysis with DataSHIELD where we work with data from a relational database. Namely, the following example depicts the situation: there are (at least) two tables in the database, Observation and Patient, where each observation has patient_id as a foreign key. The observations record some biomarkers’ values from the patient on a specific date. The same biomarker can be observed multiple times for the same patient.
Patient table (snippet):
Observation table (snippet):
|1||1||2019-02-27||Fasting Blood Glucose|
|2||1||2020-02-27||Fasting Blood Glucose|
|5||2||2020-02-17||Fasting Blood Glucose|
To my understanding, the DataSHIELD analyst does not have an access to the id-s of any of these tables, which makes it impossible to join them on the analyst side. On the other hand, the problem also occurs when trying to flatten this data into a single data object, to make it ready for analysis in DataSHIELD; having a flat table (patient per row) seems infeasible for this case.
What would be an appropriate solution for this case?
Thank you in advance,