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
Postgresqlgurus. 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
mindthe 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
contentin 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
elegiblelanguages as input)
Thanks !
Laura
