What would be the appropiate design for storing content in multiple languages in Google App Engine?

Question

The context of my question is an educational project where an application deployed on Google App Engine sends questions to a large number of users that submit answers. Questions are translated into a number of languages and each user can decide the language to be used.

Each question is associated to a number of answers (up to 4) and only one of them is correct. The question length is restricted to 200 characters and the answer length to 2 or 3 words.

I'm also interested in storing how many times questions are sent and how many times answers are picked by the users.

Until I was aware of the existence of Structured Properties, this was my design:

class Question (ndb.Model):
    Language = ndb.StringProperty(required = True, default = 'en')
    Text = ndb.StringProperty(required = True, indexed = False)

class QuestionTranslation (ndb.Model):
    Question = ndb.KeyProperty(Question, required = True)
    Language = ndb.StringProperty(required = True)
    Translation = ndb.StringProperty(required = True, indexed = False)

class QuestionStats (ndb.Model):
    Question = ndb.KeyProperty(Question, required = True)
    TimesUsed = ndb.IntegerProperty(default = 0)

class Answer (ndb.Model):
    Question = ndb.KeyProperty(Question, required = True)
    Language = ndb.StringProperty(required = True, default = 'en')
    Text = ndb.StringProperty(required = True, indexed = False)
    IsCorrect = ndb.BooleanProperty(required = True, default = False)

class AnswerTranslation (ndb.Model):
    Answer = ndb.KeyProperty(Answer, required = True)
    Language = ndb.StringProperty(required = True, default = 'es')
    Translation = ndb.StringProperty(required = True, indexed = False)

class AnswerStats (ndb.Model):
    Answer = ndb.KeyProperty(Answer, required = True)
    TimesPicked = ndb.IntegerProperty(default = 0)

However, this design doesn't feel right to me because I'm basically repeating the code in QuestionTranslation and AnswerTranslation and this approach doesn't scale well when including new entities whose content must be translated to multiple languages as well.

Furthermore, now I know that using a StructuredProperty in this case has an important advantage over a KeyProperty since the number of queries can be reduced when, for instance, obtaining the answers of a question.

This is my current design:

class Translation (ndb.Model):
    Language = ndb.StringProperty(required = True, default = 'en')
    Text = ndb.StringProperty(required = True, indexed = False)

class Answer (ndb.Model):
    Translations = ndb.StructuredProperty(Translation, repeated = True)
    IsCorrect = ndb.BooleanProperty(required = True, default = False)
    TimesPicked = ndb.IntegerProperty(default = 0)

class Question (ndb.Model):
    Translations = ndb.StructuredProperty(Translation, repeated = True)
    Answers = ndb.LocalStructuredProperty(Answer, repeated = True)
    TimesUsed = ndb.IntegerProperty(default = 0)

It's much simpler and Translation is the only entity required for content translation. Nevertheless, I had to adopt the work-around mentioned in the GAE documentation [1] so that the StructuredProperty Answers that is repeated in Question can contain another StructuredProperty, Translations, which is also repeated in Answer. On the other hand, the stats (e.g. number of times a question is used) are integrated in the main entities.

Am I in the right direction? Do you suggest any alternatives that scale well and help me reduce the number of queries?

Thanks a lot.

[1] https://developers.google.com/appengine/docs/python/ndb/properties#structured

Hernán Acosta Hernán Acosta · Accepted Answer · 2014-01-17T19:44:14

When you design a translation module you have to consider a few things:

There is only one translation per entity per language,
Use it for different kinds (questions, answers, pages) in your site.

I think the best solution is using expando models:

class Translation(ndb.Expando):      
  """ parent: original_entity.key
      string_id: language
  """
  pass

When you create a new translation you do:

spanish_question = Translation(id='es', parent=question.key)
spanish_question.text = "¿Te parece una buena idea?"
spanish_question.put()

With this approach you can get the translation without using queries, just building the key and doing key.get()

EDIT: Explaining a little more. With your last design with structured property:

class Translation (ndb.Expando):
    """ string_id: language, parent: question.key"""
    answers = ndb.LocalStructuredProperty(Answer, repeated = True) #Defined because GenericProperty doesn't support storing an entity.

class Answer (ndb.Model):
    text = ndb.StringProperty()
    is_correct = ndb.BooleanProperty(required = True, default = False)
    times_picked = ndb.IntegerProperty(default = 0)

class Question (ndb.Model):
    text = ndb.StringProperty()
    answers = ndb.LocalStructuredProperty(Answer, repeated = True)
    times_used = ndb.IntegerProperty(default = 0)

Example:

question = Question(
 id= 1
 text= "What is the world's most common religion?"
 answers = [Answer(text="Christianity", is_correct=True), Answer(text="Buddhism"), Answer(text="Hinduism"), Answer("Muslim")]
)

spanish_answers = [Answer(text="Cristinanismo"), Answer(text="Budismo"), Answer(text="Hinduismo"), Answer(text="Musulmana")]
spanish_translation= Translation(id="es", parent=question.key, text="Cual es la religion mas popular del mundo?")
spanish_translation.answers = spanish_anwers
spanish_tranlation.put()

If you want to get that translation (question and its answers translated):

translation = ndb.Key('Question', '1', 'Translation', 'es').get()

If you don't use structured properties, you will have 2 different kinds: Question and Answer. In this case you will also have to translate each answer in a different translation entity:

class Translation (ndb.Expando):
    """ string_id: language, parent: question.key"""
    pass

In brief: Each translation is saved in a separate entity, but that's OK because you can create and delete translations irrespective of each other (it is faster to modify one translation than all together into a single entity). You can also access a particular translation building the key, which is faster than doing a query. Or if you want you can do Projection queries. The key ensure uniqueness. Finally, this model is very simple and can translate entities with indefinite and unknown text properties.

What would be the appropiate design for storing content in multiple languages in Google App Engine?

1 Answers