Hi,

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

Code:
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:

Code:
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:

Code:
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!