Multilingual content with fallback language

Databases
1

Hi,

I’m working on the database design of a multilingual website. I want to use the following popular method:


table products
id
price

table products_i18n
id
product_id
lang
name
description

I’ve used this in the past and know it works fine and results in a nicely normalized database.
An example query would be:


SELECT p.id, pi.name FROM products AS p
INNER JOIN products_i18n AS pi ON pi.product_id = p.id
WHERE pi.lang = 'EN'
------------------------
1. screwdriver
2. hammer
3. nailgun

The problem I’m facing now, is that I want to use a default language. In other words, if there’s no content for the product in the given language, show the English name.
If I were to query the list of products in Dutch, but “nailgun” was never translated, the above query would only give me two products:


SELECT p.id, pi.name FROM products AS p
INNER JOIN products_i18n AS pi ON pi.product_id = p.id
WHERE pi.lang = 'NL'
------------------------
1. schroevendraaier
2. hamer

I would like the English “nailgun” to show up instead, but I’m having trouble with the joins that would make this work. I know I can detect the missing content in my application code and run a second query, but I’d rather not.

Thanks very much in advance!

2 
SELECT p.id
     , COALESCE(pi_lang.name,pi_dflt.name) AS name 
  FROM products AS p
LEFT OUTER 
  JOIN products_i18n AS pi_dflt 
    ON pi_dflt.product_id = p.id
   AND pi_dflt.lang = 'EN'
LEFT OUTER 
  JOIN products_i18n AS pi_lang 
    ON pi_lang.product_id = p.id
   AND pi_lang.lang = 'NL'
3

That is perfect! Thanks so much.
If I had known it was that easy…

4

yup, the COALESCE is the key ingredient :slight_smile: