Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 1 down vote favorite

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!

share|improve this question
2  
I'd add a constraint on the PatientInStudies table to prevent duplicate records, but otherwise it looks fine. – FrustratedWithFormsDesigner Mar 13 at 19:12
@FrustratedWithFormsDesigner could you elaborate on what you mean by constraint? I am still learning Access and relational database design, thanks! – alchuang Mar 13 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. – FrustratedWithFormsDesigner Mar 13 at 20:00

1 Answer

up vote 2 down vote accepted

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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