Storing mutliple selected values in DB

Question

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:

1. 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.
2. 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.
3. 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?

Answer 1

Option 3 by a country mile, once you've addressed the assumption about bloat in the database design...

It looks like the implementation would 'bloat' the database by creating per-language tables or additional fields per-language. This is a 'red flag' for you to look out for in the future: when adding support for another language requires table creation and having to update queries etc. then that your design is storing DATA (the language/translation target) in the database DESIGN (the table structure) when the two clearly should not mix.

Once you've spotted the error then your solution is pretty straightforward and might contain two tables:

• Foods (LanguageId, FoodId, Name) where only a combination of LanguageId and FoodId is unique.
• Selections (UserId, FoodId, Timestamp) to log who chose what and when.

That's it, you're done with minimal fuss. Processing is easy as the FoodId is per food rather than per-translation and if the user changes their language then nothing is locale specific.

Depending on your application design you could go a step further by folding the Foods table up into a wider L18N context... Something worth exploring in a multilingual application. But obviously that depends on what your real application is.