Metody na przechowywanie tłumaczeń w bazie danych

W tym wpisie przedstawię kilka możliwych podejść do rozwiązania problemu przechowywania wielojęzycznych dokumentów w bazie danych i dostępu do nich.

Wyobraźmy sobie, że mamy aplikację internetową, przechowującą dane o produktach w kilku językach – na przykład w języku polskim i angielskim.

Pierwsze, najprostsze rozwiązanie, to utworzenie dodatkowych kolumn w tabeli dla każdego tekstu, który powinien być przetłumaczony, tak jak poniżej:

Dane z bazy możemy uzyskać przy pomocy trywialnego zapytania

SELECT * FROM products WHERE id = 1

To podejście ma niewątpliwe zalety, w postaci łatwej implementacji, prostych zapytań (brak konieczności użycia JOIN-ów), nie występuje też redundancja. Ma jednak istotne wady – zarządzanie staje się trudne, gdy języków jest więcej niż kilka, a dodanie nowego języka wymaga skomplikowanych zmian w strukturze. Trzeba ponadto pamiętać, które kolumny mają własne tłumaczenia, a które pozostają niezmienione w różnych językach.

Kolejne podejście to modyfikacja poprzedniego, jednak tutaj zamiast dodawać kolejne kolumny, informacje o tłumaczeniu umieszczamy w jednej kolumnie z zawartością w postaci JSONa.

Zapytanie pobierające pierwszy rekord z bazy z opisem w języku angielskim:

SELECT id, creationDate, createdBy, price,
  JSON_UNQUOTE(JSON_EXTRACT(translation, '$[0].translate.title')) AS title,
  JSON_UNQUOTE(JSON_EXTRACT(translation, '$[0].translate.description')) AS description
FROM products
WHERE id = 1;

Zalety tego rozwiązania pokrywają się z zaletami poprzednika – jest łatwość implementacji, proste zapytania (bez JOIN), nie trzeba duplikować zawartości i wygląda sensowniej niż pierwsze rozwiązanie. Niestety, taka baza w dalszym ciągu robi się trudna do utrzymania przy wielu językach, zapytania robią się bardziej skomplikowane (starsze bazy MySQL nie rozpoznają typu JSON), a ponadto większość edytorów WYSIWYG (np. CKEdit) nie będzie chciało łatwo współpracować przy edycji danych z takiej tabeli.

Rozwiązanie numer 3 to podejście przypominające to pierwsze, ale zamiast dodawać kolumny, dodajemy kolejne wiersze.

SELECT * FROM products WHERE lang = 'en' AND id = 1;

Ponownie, do zalet tego rozwiązania należy łatwa implementacja i proste układanie zapytań, natomiast pojawiają się nowe trudności – przy zmianie danych niezależnych od języka (np. ceny) należy zaktualizować je we wszystkich wersjach językowych rekordu, aby użytkownik wersji polskiej nie zapłacił innej ceny niż użytkownik wersji angielskiej. Skomplikowana staje się również operacja dodania nowego języka.

Czwarte rozwiązanie to najczystsze podejście, jakie do tej pory rozważaliśmy. Wszystkie teksty będziemy przechowywać w jeden tabeli z tłumaczeniami. Ułatwi nam to zarządzanie tekstami w wielu językach, zwłaszcza gdy będziemy potrzebowali dodać jeszcze kolejny.

Tabela products

Tabela translations

Zapytanie do bazy danych:

SELECT p.id, p.creationDate, p.createdBy, p.price,
  title_en.value AS title, desc_en.value AS description
FROM products p
LEFT JOIN translations title_en ON p.title_trans_id = title_en.id AND title_en.lang = 'en'
LEFT JOIN translations desc_en ON p.desc_trans_id = desc_en.id AND desc_en.lang = 'en'
WHERE p.id = 1;

Zaletą tego rozwiązania będzie z pewnością normalizacja i podejście relacyjne do problemu, dodawanie nowych języków również wydaje się proste (nie zmieniają się kolumny). Wszystkie tłumaczenia wędrują do jednej tabeli i są czytelne. Do wad należą natomiast dość skomplikowane zapytania i trudność w utrzymaniu (zwykłe operacje typu INSERT, DELETE czy UPDATE robią się nadmiernie skomplikowane).

Piąta opcja to modyfikacja poprzedniego rozwiązania, które wydaje się utrzymywać jego zalety, jak i eliminować niektóre wady. Dla każdej tabeli przechowującej tekstowe informacje, które można przetłumaczyć tworzymy dodatkową tabelę.

Tabela products

Tabela productTranslations

Zapytanie SQL:

SELECT p.id, p.creationDate, p.createdBy, p.price,
  pt.title, pt.desc AS description
FROM products p
JOIN productTranslations pt ON p.id = pt.productId AND pt.lang = 'en'
WHERE p.id = 1;

Zalety tego rozwiązania są podobne do zalet rozwiązania numer 4: dane są znormalizowane, łatwo można dodać nowe języki. Kolumny zachowują ponadto swoje oryginalne nazwy, a zapytania są dość proste (tylko jeden JOIN). Wadą jest natomiast znaczne zwiększenie liczby tabel (w najgorszym przypadku ich podwojenie).

Najlepsze rozwiązanie problemu przechowywania tłumaczeń w bazie danych MySQL zależy od czasu jaki możemy poświęcić, typu projektu, zakładanego czasu na realizację projektu i wielu różnych czynników. Projekty amatorskie i proste witryny mogą opierać się na jednym z pierwszych rozwiązań, natomiast profesjonalne aplikacje powinny raczej być projektowane w bardziej elastyczny i przyszłościowy sposób, jak na przykład w propozycji nr 5.