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?


  • 2
    I'd add a constraint on the PatientInStudies table to prevent duplicate records, but otherwise it looks fine. Commented Mar 13, 2013 at 19:12
  • @FrustratedWithFormsDesigner could you elaborate on what you mean by constraint? I am still learning Access and relational database design, thanks!
    – alchuang
    Commented Mar 13, 2013 at 19:31
  • Constraints: en.wikipedia.org/wiki/Relational_database#Constraints You should make a primary composite key of all three fields in this table to avoid duplicate records. Commented Mar 13, 2013 at 20:00

1 Answer 1


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 and acknowledge you have read our privacy policy.

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