Using PostgreSQL hstore for i18n

Question

I'm just modeling a PostgreSQL data base for a new project that needs localization-support. I wonder if hstore might be a good way to localize certain data-fields.

For example: let's take a simplified table for blog posts with the fields post_title and post_content. With the key-value type hstore one could add several translations to those fields, identified with a language-code as key:

id | post_title (hstore)   | post_content (hstore)
---|-----------------------|------------------------------
 1 | "en" => "Hello World",| "en" => "Content in english",
   | "de" => "Hallo Welt"  | "de" => "Inhalt auf deutsch"
---|-----------------------|------------------------------
 2 | ...                   | ...

Is anyone experienced in that approach? It seems to be very flexible and easy to use, but maybe I'm missing some significant drawbacks here?

Answer 1

I am using this approach in a project as well and I believe it was a good decision, because I can easily add a new language to my project without the need of writing code or updating the database schema

Also I can easily add fallback to queries like this:

select coalesce(nullif(post_title -> :lang, ''), post_title -> 'en') from posts

Plus there are plenty powerful sql functions at your disposal.