Table Relationships

[ledbyrain]

Hello,



For a research study, participants (PID) will undergo interviews (INT) five times and complete 9 questionnaires at each time. Currently, I have the following tables set up with the variables below as primary keys:

tblMain: PID

tblInterview: PID and INT

tblQuestionnaireX (9 questionnaire tables): PID and INT

My question is: Should I set up relationships? If so, what are the appropriate relationships to set up? Here are the options I've thought of, but I would appreciate feedback:

Option A

tblMain (one) - tblInterview (many)
tblMain (one) - tblQuestionnaireX (many; 9 questionnaire tables)

Option B

tblMain (one) - tblInterview (many);
tblInterview (one) - tblQuestionnaireX (many)

Option C (this doesn't appear to work, by the way)

tblMain (one) - tblInterview (many)
tblMain (one) - tblQuestionnaireX (many)
tblInterview (one; using PID and INT as primary keys) - tblQuestionnaireX (one, using PID and INT as primary keys)

My knowledge of Access is pretty superficial so I apologize if my logic is unclear or deeply flawed! Ultimately, each questionnaire table will be exported to SPSS and then transformed for analysis. The reason for creating relationships here is to be able to see, for example, if a participant has completed all questionnaires at Time 1.

Thank you for your time!

[NTC]

if the 9 questionaires revolve around a unique Interview then your B example is correct.

one has 1 primary key per table for your 3 tables they will be respectively:
PID
INT
QST (I made this up)

Then parent/child relationships must have cross referencing "Foreign Keys" - so you will have in those 2 child tables respectively:
PID
INT-pid
QST-int

QST is a child table to INT and a grandchild table to PID.

All of this advice is premised on my understanding of your post that there is a linear relationship from PID to INT to QST. Meaning that if there are 3 interviews to 1 person then that could involve up to 27 questionaires (9 per interview). If I misunderstood - and the 9 questionaires is the max involving one person then both INT and QST are child records in parallel to each other and your version A was correct.