Postgres 14

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table languages (
  id serial primary key,
  tag text unique not null
);

-- This table might seem a bit silly, but it allows tables to refer to
-- a single message and enforce referential integrity.
create table messages (
  id bigserial primary key
);

create table message_localizations (
  -- If a message or language is deleted, so will its localization.
  message_id bigint not null references messages on delete cascade,
  language_id int not null references languages on delete cascade,

  localization text not null,

  -- This serves as both a primary key and enforces one localization
  -- per message and language.
  primary key(message_id, language_id)
);

create table events (
  id bigserial primary key,
  name_id bigint not null references messages,
  description_id bigint not null references messages
);

CREATE TABLE



CREATE TABLE



CREATE TABLE



CREATE TABLE


insert into languages (tag) values ('en-US'), ('es');

INSERT 0 2


insert into messages values (DEFAULT), (DEFAULT), (DEFAULT);

INSERT 0 3


insert into message_localizations (message_id, language_id, localization) values
  (1, 1, 'Howdy!'), (1, 2, '¡Hola!'),
  (2, 1, 'Goodbye!'), (2, 2, '¡Adios!'),
  (3, 1, 'What?'), (3, 2, '¿Qué?');

INSERT 0 6


insert into events (name_id, description_id) values
  (1, 2), (3, 3);

INSERT 0 2


select events.id, ml1.localization as name, ml2.localization as description
from events
left join languages lt on lt.tag = 'en-US'
left join message_localizations ml1
  on ml1.message_id = name_id and ml1.language_id = lt.id
left join message_localizations ml2
  on ml2.message_id = description_id and ml2.language_id = lt.id


id
name
description


1
Howdy!
Goodbye!

2
What?
What?



SELECT 2