Fallback in a multilingual database design-CodePudding

I'm designing a multilingual database about products – how original ;)

This is the simplified design:

If I select data for 'de', is it empty (NULL):

select 
    p.id,
    p.price,
    pi.name,
    pi.description
from public.products p
inner join public.locales loc
on loc.lang = 'de'
left join public.products_i18n pi
on p.id = pi.id and pi.lang = loc.id

I would like to have a fallback on english when the required locale is not present:

select 
    p.id,
    p.price,
    coalesce(pi.name, pi_def.name),
    coalesce(pi.description, pi_def.description)
from public.products p
inner join public.locales loc
on loc.lang = 'de'
left join public.products_i18n pi
on p.id = pi.id and pi.lang = loc.id
left join public.products_i18n pi_def
on p.id = pi_def.id and pi_def.lang = '1' -- fallback on english

However, I receive two rows: 1 row with the correct fallback data (description is expected to be empty), and 1 empty row.

Is it a display issue with pgAdmin or is my query wrong?

SQL fiddle available there: http://sqlfiddle.com/#!17/485be/1

CodePudding user response：

There is no need to join to 2 copies of public.products_i18n.
Filter public.locales so that it returns only 'de' and 'en' and use FIRST_VALUE() window function to get the details for 'de' or 'en' if 'de' does not exist:

select distinct p.id, p.price,
       first_value(pi.name) over (partition by p.id order by loc.lang = 'de' desc) "name",
       first_value(pi.description) over (partition by p.id order by loc.lang = 'de' desc) description
from public.products p
inner join public.products_i18n pi on p.id = pi.id 
inner join public.locales loc on loc.lang in ('de', 'en') and pi.lang = loc.id;

See the demo.