How to structure database with localization?

I don't know anything about Flask-Babel, but the way I handle it is this:

I move all language-dependent data into a separate table with a language attribute. For example, if my one-language solution would be to have one table with, say:

product (product_id, name, description, price)

For multiple languages I create two tables:

product (product_id, price)product_language (product_id, language, name, description)

Then when I want to retrieve the data for a particular language

select pl.name, pl.description, p.pricefrom product pjoin product_language pl on pl.product_id=p.product_idwhere p.product_id=@id and pl.language=@language

Now it's easy to add new languages. There is zero code change: just add new records to the product_language table.

You have to consider what happens if there is no record for a particular language. If you simply do an inner join like my example, then you'll get a not found, which may or may not be acceptable. In some cases this may be an error. In other cases you may want to fall back to a default language. For example, I was just working on a program where we have a table of messages in various languages. I recently added code to say that if we don't have a message with a specified name-country, fall back to the language code with no country. Like if we don't have "fr-ca", try "fr". And if we don't have that, fall back to English. (Because everybody in the world speaks English, right? But seriously, because we're an American company and I know we always have English.)

I don't know flask-babel, but as long as you stick to using the same locale names for the extension on the columns as defined in g.locale, then you could do this

{{ getattr(model, 'name_{}'.format(g.locale)) }}

and

{{ getattr(model, 'description_{}'.format(g.locale)) }}

Obviously, you could use other ways to build the variable name of the column you want, but getattr should allow you to dynamically get the value you want.

Alternatively, and this might be a nicer solution is to change what you are returning to the page. Instead of returning the SQLAlchemy object and having code in your templates, maybe get your view handler to do the conversion beforehand and simple always return the correct language specific values in an object. Again I don't know Flask so this might not be possible, but I don't see why it wouldn't be.

Hope that helps.

I used Jay answered to create my models like this:

from flask import gfrom project import dbclass Model(db.Model):    __tablename__ = 'models'    id = db.Column(db.Integer, primary_key=True, autoincrement=True)    languages = db.relationship('ModelLanguage', backref='model', lazy='dynamic')    [...]    def __init__(self, url):        [...]    def __repr__(self):        return '<Model {}>'.format(self.local_name)    @property    def local_name(self):        if hasattr(g, 'locale'):            if self.languages.filter_by(locale=g.locale).first() is not None:                return self.languages.filter_by(locale=g.locale).first().name        else:            return self.languages.first().name    @property    def local_description(self):        if hasattr(g, 'locale'):            if self.languages.filter_by(locale=g.locale).first() is not None:                return self.languages.filter_by(locale=g.locale).first().description        else:            return self.languages.first().descriptionclass ModelLanguage(db.Model):    __tablename__ = 'models_languages'    id = db.Column(db.Integer, primary_key=True, autoincrement=True)    model_id = db.Column(db.Integer, db.ForeignKey('models.id'))    locale = db.Column(db.String)    name = db.Column(db.String)    description = db.Column(db.String)    def __init__(self, model_id, locale, name, description):        self.model_id = model_id        self.locale = locale        self.name = name        self.description = description    def __repr__(self):        return '<Language element for model {0} in {1} >'.format(self.model_id, self.locale)

Therefore in my template, I can directly call {{ model.local_name }} to display the element in the correct language.