Tworzenie wielojęzycznej strony w MySQL

Globe Flags White

Zbudowanie wielojęzycznej strony internetowej nie jest prostym zadaniem, po drodze możemy spotkać wiele problemów, jednym z nich jest to, w jaki sposób przechowywać zawartości strony w bazie danych dla każdego języka.

W sieci można znaleźć wiele odpowiedzi na to pytanie, niestety nie ma jednego, uniwersalnego rozwiązania. Każda metoda zależy od osobistych potrzeb, wielkości bazy danych, złożoności strony itd. Omówimy więc tylko główne techniki.

Metoda „kolumnowa”

To najprostsze rozwiązanie. Dla każdego języka tworzy się nową kolumnę w bazie danych – odpowiednio dla wszystkich tekstów, które muszą być przetłumaczone. Poniżej przykład takiej tabeli w MySQL:

CREATE TABLE app_product (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `date_created` datetime NOT NULL,
  `price` decimal(10,2) unsigned NOT NULL DEFAULT '0.00',
  `title_en` varchar(255) NOT NULL,
  `title_es` varchar(255) NOT NULL,
  `title_fr` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
);

Zapytania do bazy danych są bardzo proste. Wybierasz po prostu kolumny w danym języku:

<?php

$sql = "SELECT * FROM `app_product` WHERE 1";
if($result = mysql_query($sql)){
    if($row = mysql_fetch_assoc($result)){
        echo "English: ".$row["title_en"]."<br>";
        echo "Spanish: ".$row["title_es"]."<br>";
        echo "French: ".$row["title_fr"]."<br>";
    }
}

$sql = "SELECT `title_".$_SESSION['current_language']."` as `title`
        FROM `app_product`";
if($result = mysql_query($sql)){
    if($row = mysql_fetch_assoc($result)){
        echo "Current Language: ".$row["title"];
    }
}
?>

Plusy:

  • Prostota – łatwa implementacja
  • Proste zapytania – nie wymagane JOIN w zapytaniach
  • Bez duplikowania – nie ma żadnych zduplikowanych danych

Minusy:

  • Trudne do utrzymania – problemy zaczynają się przy większej ilości języków
  • Trudne dodawanie nowych języków – wymagane poznanie struktury tabeli
  • Utrzymuje puste miejsca  – jeśli nie wszystkie języki są potrzebne mamy puste pola w tabeli

2. Metoda „wierszowa”

Podobna do powyższej z tą różnicą że zamiast powielać kolumny powiela się wiersze.

CREATE TABLE app_product (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `date_created` datetime NOT NULL,
  `price` decimal(10,2) unsigned NOT NULL DEFAULT '0.00',
  `language_id` varchar(2) NOT NULL,
  `title` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
);

Poniżej zapytania do bazy danych. Generalnie chodzi o to, aby wybrać odpowiednie wiersze w zależności od kraju.

<?php
$sql = "SELECT * FROM `app_product` WHERE `id` = 1";
if($result = mysql_query($sql)){
    while($row = mysql_fetch_assoc($result)){
        echo "Language (".$row["language_id"]."): ".$row["title"]."<br>";
    }
}

$sql = "SELECT `title`
        FROM `app_product`
        WHERE `language_id` = '".$_SESSION['current_language']."'";
if($result = mysql_query($sql)){
    if($row = mysql_fetch_assoc($result)){
        echo "Current Language: ".$row["title"];
    }
}
?>

Plusy:

  • Prostota – łatwa implementacja
  • Proste zapytania – nie wymagane JOIN w zapytaniach

Minusy:

  • Trudna do utrzymania – każda kolumna, która nie jest tłumaczona (np „cena”) musi być zmieniona w każdym wierszu
  • Trudno dodać nowy język – należy dodać nowe wiersze do każdej tabeli kopiując zawartość domyślnego języka
  • Zduplikowana zawartość – w kolumnach, które nie są przetłumaczalne

 

3. Metoda pojedynczej tabeli tłumaczenia

Z punktu widzenia bazy danych to „najczystsze” rozwiązanie. Wszystkie teksty, które są do przetłumaczenia, przechowywane są w jednej tabeli. Szczególnie przydatne na stronach, które posiadają dużo języków z możliwością zwiększenia ich liczby w przyszłości.

CREATE TABLE IF NOT EXISTS `app_language` (
  `code` char(2) NOT NULL,
  `name` varchar(20) NOT NULL,
  PRIMARY KEY (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS `app_product` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `date_created` datetime NOT NULL,
  `price` decimal(10,2) NOT NULL DEFAULT '0.00',
  `title` int(11) NOT NULL DEFAULT '0',
  `description` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `title` (`title`),
  KEY `description` (`description`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS `app_translation` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1;

CREATE TABLE IF NOT EXISTS `app_translation_entry` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `translation_id` int(11) NOT NULL,
  `language_code` char(2) NOT NULL,
  `field_text` text NOT NULL,
  PRIMARY KEY (`id`),
  KEY `translation_id` (`translation_id`),
  KEY `language_code` (`language_code`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1;

Poniżej zapytania do bazy danych:

<?php
$sql = "SELECT p.*, l.name as language_name, te.field_text as title
        FROM `app_product` p
        INNER JOIN `app_translation_entry` te ON p.title = te.translation_id
        INNER JOIN `app_language` l ON te.language_code = l.code
        WHERE p.id = 1";
if($result = mysql_query($sql)){
    while($row = mysql_fetch_assoc($result)){
        echo "Language (".$row["language_name"]."): ".$row["title"]."<br>";
    }
}

$sql = "SELECT p.*, l.name as language_name, te.field_text as title
        FROM `app_product` p
        INNER JOIN `app_translation_entry` te ON p.title = te.translation_id
        INNER JOIN `app_language` l ON te.language_code = l.code 
        WHERE p.id = 1 AND 
              te.language_code = '".$_SESSION['current_language']."'";
if($result = mysql_query($sql)){
    if($row = mysql_fetch_assoc($result)){
        echo "Current Language: ".$row["title"];
    }
}
?>

Plusy:

  • Właściwa normalizacja – czysty „wygląd” bazy danych
  • Łatwe dodawanie nowych języków – nie wymaga znajomości struktury tabel w bazie danych
  • Wszystkie tłumaczenia w jednym miejscu – czytelna baza danych

Minusy:

  • Trudne zapytania – wielokrotne łączenie zapytań, aby odszukać właściwą treść w danym języku
  • Trudne do utrzymania – skomplikowane wszystkie zapytania do bazy danych
  • Wszystkie tłumaczenia w jednym miejscu – brak jednej tabeli uniemożliwia działanie strony

 

4. Metoda dodatkowych tabel z tłumaczeniami

Jest to odmiana powyższego podejścia i wydaje się być łatwiejsza w utrzymaniu. Dla każdej tabeli, która przechowuje informacje, które mogą muszą być tłumaczone jest tworzona dodatkowa tabela. Poniżej przykład takiego schematu bazy danych w MySQL:

CREATE TABLE IF NOT EXISTS `app_product` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `date_created` datetime NOT NULL,
  `price` decimal(10,2) NOT NULL DEFAULT '0.00',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS `app_product_translation` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `product_id` int(11) NOT NULL DEFAULT '0',
  `language_code` char(2) NOT NULL,
  `title` text CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `description` text CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`),
  KEY `translation_id` (`product_id`),
  KEY `language_code` (`language_code`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS `app_language` (
  `code` char(2) NOT NULL,
  `name` varchar(20) NOT NULL,
  PRIMARY KEY (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Poniżej zapytania do bazy danych:

<?php
$sql = "SELECT p.*, pt.title, pt.description, l.name as language_name
FROM `app_product` p
INNER JOIN `app_product_translation` pt ON p.id = pt.product_id
INNER JOIN `app_language` l ON pt.language_code = l.code
WHERE p.id = 1";
if($result = mysql_query($sql)){
while($row = mysql_fetch_assoc($result)){
echo "Language (".$row["language_name"]."): ".$row["title"]."<br>";
}
}$sql = "SELECT p.*, pt.title, pt.description
FROM `app_product` p
INNER JOIN `app_product_translation` pt ON p.id = pt.product_id
WHERE p.id = 1 AND pt.language_code = '".$_SESSION['current_language']."'";
if($result = mysql_query($sql)){
if($row = mysql_fetch_assoc($result)){
echo "Current Language: ".$row["title"];
}
}
?>

Plusy:

  • Właściwa normalizacja – czysty „wygląd” bazy danych
  • Łatwe dodawanie nowych języków – nie wymaga znajomości struktury tabel w bazie danych
  • Kolumny utrzymują swoje nazwy – nie jest wymagany żaden suffix
  • Proste zapytania – dość proste zapytania (tylko z jednym JOIN)

Minusy:

  • Może podwoić ilość tabel – Do każdej tabeli, która musi być przetłumaczona należy stworzyć nową tabelę

 

Podsumowanie

Powyższe 4 przykłady pokazują jak całkiem odmienne rozwiązania mogą zostać użyte przy rozwiązaniu tego problemu. Oczywiście jest ich jeszcze znacznie więcej.

Pamiętaj, żeby wybrać rozwiązanie dopasowane do wymagań Twojego projektu. Jeśli jest to prosta strona z małą ilością języków wybierz opcję numer 1. Jeśli potrzebujesz czegoś więcej m.in. możliwości dodawanie nowych języków wybierz opcję 3 lub 4.

W jednym z moich projektów: http://domatorsko.pl/ jest użyte podejście numer 4.

 

Ta strona używa ciasteczek (cookies), dzięki którym nasz serwis może działać lepiej. Więcej informacji

The cookie settings on this website are set to "allow cookies" to give you the best browsing experience possible. If you continue to use this website without changing your cookie settings or you click "Accept" below then you are consenting to this.

Close