Hi,
I'm creating a Postgres backend for an internal tool which is essentially a very simple implementation of multi-lingual CMS.
So far my thoughts are along the lines of the below, but I would appreciate a second (or more !) pair of eyes from some Postgresql gurus. I am especially interested in feedback and suggestions in relation to the following questions:
(a) Is this going to work as expected (i.e. have I missed some obvious foot-guns ?)
(b) Is this manner of doing things reasonably efficient or are there better ways I should be thinking of ? (bear in mind the schema is not set in stone, so completely out of the box suggestions welcome !).
The basic design concept (oversimplified) is: For each page, you have one or more objects and those objects may have content in one or more languages.
create table langtest(
pageid text not null,
objectid text not null ,
objectlang text not null,
objectdata text not null);
create unique index on (pageid,objectid,objectlang);
insert into langTest(pageID,objectID,objectLang,objectData) values ('zzz','abc','en','Lorem ipsum dolor sit amet');
insert into langTest(pageID,objectID,objectLang,objectData) values ('zzz','abc','de','Amet sit dolor ipsum lorem');
insert into langTest(pageID,objectID,objectLang,objectData) values ('zzz','def','en','Dolor ipsum amet sit lorem');
select distinct on(objectid)objectid,objectlang,pageid,objectdata from langTest where pageid='zzz' and objectLang = any('{de,en}'::text[]) order by objectid,array_position('{de,en}'::text[],objectLang);
(The idea being that the select query will be wrapped into a function which the frontend will call, passing a list of elegible languages as input)
Thanks !
Laura
