What to do without an entity attribute of the List data type? OR How do I persist a list of object references?
I’d need advice on a data modelling question using a customer survey use case as an example. I have a many customers I want to survey; ask a question, get an answer and get a comment (all my customers are in the Customer entity) I have a list of many questions that I could ask a customer…all of them or just some that I choose (all my questions are in the SurveyQuestions entity) Some customers can get the same questions as other customers and some may have different questions. There can be none or many answers (in the SurveyAnswers entity) associated with each question. Persist a list of all the questions and answers for each customer survey so I can look at them any time in the future I know about entity self-referencing, not sure of how to use it or if it’s applicable or not. What’s the best way to persist each customers list of questions and answers? I thought about having 2 strings in my Customer entity, each with the survey question and answer auto ID numbers delimited with a comma. I’m sure there’s a better way to do this. What would you suggest? Thanking you in advance!
I think you need a fourth entity – lets call it CustomerQuestion. Each SurveyQuestion object could be associated to many CustomerQuestion objects. The association between SurveyQuestions_SurveyAnswers really needs to be between SurveyAnswers and CustomerQuestion. Each Customer could be association with many CustomerQuestions.
This new entity will keep track of the answers to a certain question by a customer.
Hope that helps,
Ted, to provide some more detail regarding my suggestion
SurveyQuestion is the master list of all questions, containing one object for each of the 158 questions
CustomerQuestion contains an object for each of the questions that a given customer should answer. In other words, if I am a customer and should only answer 23 questions, then your app would create 23 CustomerQuestion objects. These would all be associated to my customer record and each would be associated with one SurveyQuestion object
Associating SurveyAnswers to CustomerQuestion means that may answer to each of the 23 questions I should answer can be recorded
With this structure you can
maintain each SurveyQuestion only once (no duplication required)
determine which questions a given customer should answer and which questions they have answered
retrieve all answers for a given SurveyQuestion (across all customers)
These are all of the things I can think of that a survey app should be able to keep track of.