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?



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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.