2

Does a database engine with native cross-cutting internationalization support exist, able to store data like this?

SET LOCALE='en'
SELECT * FROM BOOKS

+------+-----------------------+----------------+
|  ID  | TITLE                 | AUTHOR         |
+------+-----------------------+----------------+
|   1  | The Lord of The Rings | J.R.R. Tolkien |
+------+-----------------------+----------------+

SET LOCALE='it'
SELECT * FROM BOOKS

+------+-------------------------+----------------+
|  ID  | TITLE                   | AUTHOR         |
+------+-------------------------+----------------+
|   1  | Il Signore degli Anelli | J.R.R. Tolkien |
+------+-------------------------+----------------+

i.e. able to store values in a third dimension, such as a locale in this case?

Clarification

In the example above, just TITLE should be affected by SET LOCALE by a special definition in this kind of database (I imagine something like LVARCHAR); so, adding a discriminator column would not be feasable since all the non-localized columns will be duplicated.

  • Postgresql support inheritance. It's not exactly like your problem. But it could solve your problem. postgresql.org/docs/current/static/ddl-inherit.html – Mladen Uzelac Apr 13 '16 at 22:11
  • Not a problem, just a question. Thank you though, I'll look into Postgres inheritance for the sake of curiosity! – Giovanni Lovato Apr 13 '16 at 22:13
  • 2
    Functionally, how would this be different from a table that has an extra locale column? – Justin Cave Apr 13 '16 at 22:14
  • Using an extra locale column would need to have non-localized values duplicated. I'll make this more clear on the question! – Giovanni Lovato Apr 13 '16 at 22:16
  • No, if you have a non-localized variant you simply name that variant as exactly that. – Max Vernon Apr 14 '16 at 0:01
3

With PostgreSQL you can simulate something like using views and environment variables.

create table books_ids(
  book_id serial not null primary key,
  author text not null);

create table books_titles (
  book_id int not null references books_ids,
  lang char(2) not null,
  title text not null,
  isbn text null,
  primary key(book_id, lang));

insert into books_ids (author) VALUES ('J.R.R. Tolkien');
insert into books_titles (book_id, lang, title)
  values (1, 'en', 'The Lord of The Rings'), (1, 'it', 'Il Signore degli Anelli');

create view books as
  select b.book_id as id, t.title, b.author
  from books_ids b
  join books_titles t on (t.book_id = b.book_id and t.lang = current_setting('my.lang'));

-- in English
set my.lang='en'; select * from books;

-- in Italian
set my.lang='it'; select * from books;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.