My ERD looks like this
CLIENTS -- one too many --< RESPONSES >-- one too many--< QUESTIONS >-- one to many-- INTERVIEWS
Each of my clients has multiple interviews, and each interview has A LOT of specific questions (a mix of yes and no and written responses) I need to be able to, as a therapist, look at my list of clients, click on a specific client, and click on a specific interview ( a collection of questions ) and either pick up the interview where I left off or finish/ start another interview. There are three interviews taken at the beginning to get an Idea of where my client is at during our first encounter, there are multiple instances through out our time working with each other (every couple of months) that we redo the three sets of interviews to compare and reflect on our growth.
I am hoping that this layout will allow me to
1) allow me to easily compare, side by side, two instances of the response to the same question for an individual client.
(ex: Q- How do you feel around dogs? R- I struggle (5/7/17) . . . Q- How do you feel around dogs? R- I don't struggle (7/7/17) )
2) Allow me to compare two of the same interviews, taken at different times, for the same client
3) Allow me to compare two of the same interviews taken at the same or different times, for different clients
4) Each yes or no interview question needs to allow me to attach some notes to it
(ex: Q- do you have trouble sleeping? R- Y / N NOTE: answered no but shows signs of distress when asked )
5) Each interview needs to be date sensitive, I need to be able to know when we took each interview
Does my current ERD allow me to have the functionality I am looking for? If not how can I fix it?
My Questions and Responses tables are going to have A LOT of fields can this cause potential problems?