I am building a database for participants who are grouped into studies. There's a total of 5 different studies - A, B, C, D, and E. The participants may be in more than one studies, and participants within each study has a unique "study code" with different formats depending on the study is in. (i.e. Study A's code may be 10 characters with digits and alphabets, Study B's code may be only 6 characters digits only...etc)
I know that I need a many-to-many link table, and a look-up table for the studies, but other than that I am having a hard time figuring out where to incorporate the "study code" within the tables.
Here are the fields I have so far for my tables.
- Participants: participantID (primary key), name, date-of-birth...etc (background demographics)
- PatientInStudies: participantID, study, study_code
- LookupStudies: study(listing all the studies A, B, C..etc.)
Basically, the PatientInStudies table will look something like:
1, A, A123456789
1, B, 123456
1, C, BB23-1-52
2, A, A295890100
2, B, 123908
3, C, KG19-7-23....etc
Will the PatientInStudies Table work? Is there a better way to organize the study code within the database?