In my Android app, there is a question like "Which of the following foods did you eat today?" (not the actual question), with a fixed list of foods (e.g. "Apples, Banana, Pizza, Burgers") from which the user can select all that apply.
I now want to store in the DB, which answers were selected on which day. As I see it, I have several possibilities:
- Store them together in one field as delimited text. This breaks if the user changes his language setting, so it's not really an option for my app.
- Use a Bitmask to convert the selection to an integer and store that in the database. Keeps the DB schema nice and simple, and makes use of android's native ability to select the right string according to the set language, but makes it practically impossible to query for a specific answer. SQL queries are simple but additional data-mangling in the app is needed.
- Do a proper normalization. Bloats the database, since there has to be an answers-table for every supported language, but queries for specific answers are possible. SQL queries are more complex but reduces the need for data-mangling in the app itself.
Which of these should I use?
Is there a solution I haven't thought of that's probably better?
Is there some sort of best practice or guideline regarding this sort of problem in android development?