How localizе an entity in a MySQL table?

Question

Suppose I’ve got a MySQL table product like this:

+-------------+--------------+------+-----+---------+-------+
| Field       | Type         | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+-------+
| id          | varchar(36)  | NO   | PRI | NULL    |       |
| lang        | varchar(255) | YES  |     | NULL    |       |
| description | varchar(255) | YES  |     | NULL    |       |
+-------------+--------------+------+-----+---------+-------+

I want to translate this product information to a few languages. Currently only the description field is translatable, but we will add more translatable fields in the future.

In order to enable translations I create a new table product_translation like this:

CREATE TABLE product_translation (
  id            VARCHAR(36) PRIMARY KEY,
  language_code VARCHAR(10),
  product_id    VARCHAR(36),
  description   VARCHAR(255),
  FOREIGN KEY (product_id) REFERENCES product(id)
);

Now if the requested language is English or absent I use the description field of the product table. Otherwise, I join the product and product_translation tables like this:

SELECT product.*, 
       product_translation.language_code,
       product_translation.description 
FROM product INNER JOIN product_translation
ON product_translation.product_id = product.id 
WHERE product_translation.language_code = 'fr';

Does it make sense ?
Can I use one SQL statement to retrieve products for any language ?
Would it be better to drop the description field from the product table, copy the data, and always use the join ?

Answer 1

if the requested language is English or absent I use the description field of the product table. Otherwise, I join the product and product_translation tables

You can achieve this using COALESCE function in mysql

The MySQL COALESCE() function is used for returning the first non-null value in a list of expressions. If all the values in the list evaluate to NULL, then the COALESCE() function returns NULL.

Your query can be written as :

SELECT product.*, 
       COALESCE(product_translation.language_code, 'en') AS language_code,
       COALESCE(product_translation.description, product.description) AS description
FROM product LEFT JOIN product_translation
ON product_translation.product_id = product.id 
AND product_translation.language_code = 'fr';

Would it be better to drop the description field from the product table, copy the data, and always use the join ?

If you are going to rarely translate the product_information; then you can retain the description field else you can drop it from product table and copy data to product_translation table.