On 6/1/21 2:09 PM, Laura Smith wrote:
> 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
>
What is your notion of "object". I first assumed it was akin to
"document" but then pages have objects.