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
