Search code examples
c#.netdatabasetranslationresx

Best way to store questionnaires/answers in c#?

I'm working on a project where I need to store a lot of multilanguage questionnaires. I struggled a lot on how to store these questionnaires in my database and finally I went with the resources files. Each questionnaire has his own resource file in which you find the questions, their types and their answers.

I store the user's answers in the database with a key to the answer (this key is defined in the comment section of the answer in the resource file).

I tried to store them first in my database but it was a real mess when I added the translations.

So my question is : Is it a good way to go ? Or there is a better way to store my questionnaires ?

I can't really find any documentation on this subject online so I ask you guys :)

Thanks !

Solution

  • Well, "best" is of course very subjective. But how about a structure like this. Every distinct resource is defined in this very simple table: 

    Resource: ID

    And the respective translations are stored in a table like:

    ResourceTranslation: ID, ResourceID, LanguageCode, TextValue

    So now, whenever you need to display the text for resource 4711 in language de-DE, you get that with an SQL statement like SELECT [TextValue] FROM [ResourceTranslation] WHERE [ResourceID] = 4711 AND [LanguageCode] = 'de-DE' (of course use the appropriate parameterized queries)

    Next, you have your questions basically like:

    Question: ID, ResourceID

    the Answers like:

    Answer: ID, ResourceID

    and the (n..m) connection like:

    QuestionAnswer: ID, QuestionID, AnswerID, IsCorrect

    Now if you want to display question 1337 in US english, the query is: SELECT [TextValue] FROM [ResourceTranslation] INNER JOIN [Question] ON [ResourceTranslation].[ResourceID] = [Question].[ResourceID] WHERE [Question].[ID] = 1337 AND [ResourceTranslation].[LanguageCode] = 'en-US'

    You get all the answers to that question via: SELECT [Answer].*, [QuestionAnswer].[IsCorrect] FROM [QuestionAnswer] INNER JOIN [Answer] ON [QuestionAnswer].[AnswerID] = [Answer].[AnswerID] WHERE [QuestionAnswer].[QuestionID] = 1337

    And when you want to display the answers in whatever language, you get that similar to how you get the translated question.

    Again, not sure if this is the "best" way, but it should be both pretty flexible and still pretty simple.

    Edit: Now you can also store the Users like:

    User: ID, LanguageCode (, Name, etc.)

    And the answers like:

    UserAnswers: ID, UserID, QuestionID, AnswerID (, IsCorrect, TimeStamp)