Android DB With Some Multi-Language Data

Question

I have a SQLite Database in my Android app with a "Question" table, among others. That table contains "default" or "pre-loaded" data (in this case the questions asked by the app).

Some of its columns are "technical" or "internal" data to be used behind the scenes - like the number of points the question is worth, the type of user that may answer it, etc. - while others are textual data that are presented to the user (e.g. Title, Description, etc.).

Now, I intend to let my app be internationalization-friendly (translatable) so I'm thinking that:

• Instead of following what seems to be the 'standard approaches' for multi-language DB design - either creating different columns for the translatable attributes (e.g. name_en, name_es, ...) or a separate table where each row represents a translation of the base object;

• ...I could simply keep the values for those translatable attributes in my R.Strings resource and have my DatabaseHelper class fetch the text from there, by building the resource name based, for instance, in the question's ID (e.g. q1_name, q2_name, q1_description, ...). This way I could have several R.strings and the DB helper would read from the right one.

I'm a bit unsure about this approach and I'm curious if this is a bad practice and/or I'm missing any potential drawback.

Answer 1

I don't know much about Android standards but I am sure that creating different columns for the translatable attributes is cleary a very bad solution. You would have to alter your table each time you add a new language and if your application get translated in many languages, you will have to manage that.

The two other solutions are acceptable for me but I would chose the database option if you plan to create an "admin" interface where people could for example add question and solutions...