[discussion]: Include `week(_id)` in REDCap data

[martonvago]

When transforming the REDCap data to our representation, so far we’ve been using participant_id + event_id as the PK. So the timeline was given by the events.

Now, it looks like we’re moving to an organisation where the timeline will be given by the study weeks. How do we want to represent this in the data? I assume have participant_id + week(_id) as the PK. [Question 1️⃣] But do we still want to include event for information? Can this extra categorisation ever be useful to analysts?

Now to actually add the week info, we need to be able to determine what week each row in the data corresponds to. In the majority of cases, we can already do this: any row coming from a visit will have the week of that visit and any row coming from a week-specific form will have that week. However, we have a handful of other forms where getting the week is not so straightforward. As far as I can see, these usually have a date field, so we could get the week based on that.

[Question 2️⃣] Should I start working on extracting the week info like this or should I hold off because we expect to get a nice mapping? (This overlaps with some of Signe's questions in #183 .)

@K-Beicher @lwjohnst86

[lwjohnst86]

This will require some clarifications about what the timeline actually is and how participants are processed, but from my understanding, we might actually need up to four PK/FKs: participant_id, phase_id, visit_id, and week_id, to ensure that all data can eventually be used effectively during analysis. Ideally, this information is encoded directly into the forms/filled in by someone.

[martonvago]

Okay, so what I'm hearing is that I shouldn't be trying to extract that information from the data as it is now.

This is kinda blocking me from progressing with the data staging flow. What I can do now is implement the staging flow using only the REDCap event (instead of visit, phase, week) because, structurally, participant_id + event_id is a PK. Then we can refactor later. Should I do that?

[lwjohnst86]

Yea, I know, this is blocking all of us. For now, you can keep working towards processing raw into staging, knowing that we'll have to do some updates to incorporate the PK/FK correctly. And also knowing that you'll probably run into a hard wall until this is resolved.

[lwjohnst86]

Rename this to study_week_id to clarify that it is not a calendar week.