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?
Thanks!
Best Answer
I second @FrustratedWithFormsDesigner's suggestions for a unique constraint. See http://office.microsoft.com/en-us/access-help/create-a-constraint-adp-HP003088257.aspx for how to do this in MS Access.
What a Unique Constraint does is state that no two rows can have duplicate data on some portion of the row. So presumably you want each patient to be in a study only once, so you'd want a unique constraint on PatientsInStudy(participantID, study).
This way each individual can only be given one study id. Similarly in that design your PatientsInStudy table would have a primary key spanning all three fields, and a second unique constraint on (StudyID, study_code). This way even if two studies end up with similar semantics or overlapping spaces, the key cannot be reused in the same study.
Otherwise, good.