From: | Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at> |
---|---|
To: | Laura Smith <n5d9xq3ti233xiyif2vp(at)protonmail(dot)ch>, postgre <pgsql-general(at)lists(dot)postgresql(dot)org> |
Subject: | Re: Internationalisation (i18n) with Postgres as backend |
Date: | 2021-06-02 08:43:42 |
Message-ID: | a882f82d55fccfd060aad00dc02f58168e443a92.camel@cybertec.at |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Tue, 2021-06-01 at 20:09 +0000, Laura Smith wrote:
> I'm creating a Postgres backend for an internal tool which is essentially a very simple implementation of multi-lingual CMS.
>
> 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);
>
> 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);
That looks ok, except you should remove "objectid" from the index.
That column makes that the index cannot be used for "objectlang" effectively.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
From | Date | Subject | |
---|---|---|---|
Next Message | Philip Semanchuk | 2021-06-02 12:39:33 | Re: CREATE/REFRESH MATERIALIZED VIEW planner difference? |
Previous Message | Laurenz Albe | 2021-06-02 08:38:26 | Re: Internationalisation (i18n) with Postgres as backend |