I want to use a data warehouse to store questions and answers from a survey of multiple choice questions, so my proposal is to design a star schema. For this I have done the following:
I build a fact table with the next fields: userID, surveyID, questionID, answerID and date. On the other hand I build the dimension tables for users, surverys, questions and answers.
The purpose of this is to build some reports that allow us to analyze the percentages of correct answers of the users in the questions, which are the questions with the highest error rate and which are the most usual answers in the questions with the highest error rate.
However, in order to have this I would need a table that would relate the dimensions of questions and answers and this would break the star scheme. I am new in the design of this type of models, could someone guide me about it?
