Correct primary key in resources?

[martonvago]

We initially took the PK to be event in all resources, but this can't be right because many participants have their data recorded at each event (e.g., we will have data for participant 1, participant 2, etc. recorded at visit 5).

We have a record_id_s column in the raw data, which seems to be unique to each participant. Is this correct? Should the PK then be (record_id_s, event)?

@lwjohnst86 @K-Beicher

[martonvago]

Ah, to make things more complicated, it looks like one form can be submitted multiple times per visit, so not even (record_id_s, event) is unique. We have a column for these submissions (redcap_repeat_instance). Does this mean the PK has to be (record_id_s, event, redcap_repeat_instance)?

[lwjohnst86]

Hmm, is there not a participant ID? 🤔

[K-Beicher]

records_id is the participant ID. And yes, we could have the same form filled out more than once (even though it would be rare), if for instance they report more than one adverse event. I'll have a look at the data in a minute and see if I can figure out what is going on.

[martonvago]

I thiiiink that's record_id_s, but even that is not going to be unique across events and form resubmissions. Because the same participant is going to have data for many events and (potentially) many form resubmissions. At least that's how I understand it.

[lwjohnst86]

The combination of ID and date should be enough of a PK/FK no?

[lwjohnst86]

Hmm, looks like all the instruments don't have a ID and date attached to them. How are they joined in the REDCap table?

[martonvago]

The API returns one big table starting with the columns record_id_s, redcap_event_name, redcap_repeat_instrument, redcap_repeat_instance, id_number_pre.

[lwjohnst86]

Ah, that's right.

[martonvago]

Okay then based on this what about a PK composed of:

• record_id_s renamed to participant(_id)
• redcap_event_name renamed to event
• redcap_repeat_instrument renamed to repeat_instrument? If there is a redcap_repeat_instance, this is the instrument that was repeated. Because of our metadata transformations, this is not guaranteed to be the same as the resource name.
• redcap_repeat_instance renamed to repeat_instance or repeat_instrument_instance?

So far, we've added more columns to the PK on a case-by-case basis (e.g. minutes_from_meal for VAS data). If the new PK component is derived from the raw instrument's name/identity (as is the case for VAS), then having both the new component and redcap_repeat_instrument in the PK might be redundant. So maybe we can omit redcap_repeat_instrument from the PK?

[lwjohnst86]

Sounds good, but let's have @K-Beicher confirm.

Also sounds like we should have a resource that only contains the IDs so all resources can use as a foreign key. Would participant_id, event, collection_datetime (what would be the equivalent there? or like, visit number?), and the repeat columns? Would those be found in almost all other resources?

[martonvago]

Ah, so you would put the key components in a separate table and reference that from an id column in the individual resources? Would that have a row for each row across all resources (maybe other than the transformed ones)? In that table each row would correspond to a form submission, I guess. Would this be more helpful for the analysts than leaving the PK components in each resource? There is also the option of hashing the long PK and saving the hash in a separate id column in each resource.

---

When we export all the data in one, we get record_id_s, redcap_event_name, redcap_repeat_instrument, redcap_repeat_instance for all resources (because it's one table). It's a bit confusing because the redcap_ fields are not listed in the metadata.

I can't see a collection_datetime. The closest we have is redcap_event_name. Presumably there is a mapping somewhere that says when each event is planned to take place, at least in which week of the study. I think, by default, it's only possible to get form submission timestamps (or something like that) from the audit log.