Baze de date multilanguage în MySQL

Baze de date multilanguage în MySQL

De: Tree Web Solutions | Etichete: php, baza de date, implementare baza de date, baza de date multilanguage, dezvoltatori web, scheme baza de date

Construirea unui site multilingv nu este o sarcină trivială și veți întâlni numeroase probleme în acest fel și unul dintre ele este modul în care intenționați să stocați conținutul site-ului în baza de date pentru fiecare limbă.

Puteți efectua o cercetare minuțioasă pe Web și găsiți suficiente resurse pentru aceasta, dar nu există o soluție magică, trebuie să înțelegeți acest lucru - fiecare soluție depinde de cerințele dvs. personale, mărimea bazei de date, complexitatea site-ului dvs. etc. Așa că vom discuta doar tehnicile majore. Dacă doriți să aflați mai multe, puteți găsi informații suplimentare cu o căutare Google.

Ok, deci ... există mai mult sau mai puțin 4 scheme de baze de date populare pentru site-ul web multilingv.

• Abordarea coloanei
• Abordarea pe mai multe rânduri
• Abordarea tabelului de traducere unică
  
• Abordarea cu tabel suplimentar pentru traducere

1. Abordarea coloanei

Această soluție este cea mai simplă și în esență creează o coloană suplimentară pentru fiecare text (fiecare limbă) care trebuie tradus (poate exista un număr de astfel de coloane în tabel, cum ar fi: titlu, nume, descriere etc.). Mai jos exemplul pentru astfel de tabel în MySQL:

Exemplul 1-1. Creați tabelul:

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`)
);

Acum, modul în care ați interoga este de asemenea suficient de simplu. Puteți face acest lucru selectând automat coloanele din dreapta în funcție de limba aleasă:

Exemplul 1-2. Utilizarea tabelului:

<?php
// Retrieve titles for all languages
$sql = "SELECT * FROM `app_product` WHERE 1";
if($result = mysqli_query($link, $sql)){
    if($row = mysqli_fetch_assoc($result)){
        echo "English: ".$row["title_en"]."<br>";
        echo "Spanish: ".$row["title_es"]."<br>";
        echo "French: ".$row["title_fr"]."<br>";
    }
}

// Retrieve appropriate title according to the chosen language in the system
$sql = "SELECT `title_".$_SESSION['current_language']."` as `title`
        FROM `app_product`";
if($result = mysqli_query($link, $sql)){
    if($row = mysqli_fetch_assoc($result)){
        echo "Current Language: ".$row["title"];
    }
}
?>

Avantaje:

• Simplitate - ușor de implementat;
• Interogare ușoară - nu sunt necesare JOIN-uri;
• Nu există duplicate - nu are conținut dublu (există doar un rând pentru fiecare înregistrare și numai coloanele lingvistice sunt duplicate).

Dezavantaje:

• Greu de întreținut - funcționează ușor pentru 2-3 limbi, dar devine foarte greu atunci când ai multe coloane sau multe limbi;
• Greu de adăugat o limbă nouă - adăugarea unei limbi noi necesită schimbări de schemă (și drepturi speciale de acces pentru utilizatorul db) pentru fiecare tabel cu conținut multilingv;
• Depozitați spațiu liber - dacă nu sunt necesare toate traducerile (de ex., În anumite locuri, limba implicită ar trebui să fie întotdeauna utilizată) poate provoca date redundante sau câmpuri db goale;
• Necesitatea de a determina cu ce coloană lucrați în funcție de limbă.

2. Abordarea pe mai multe rânduri

Această soluție este similară celei de mai sus, dar în loc de duplicarea conținutului în coloane, aceasta se face în rânduri. Mai jos exemplul pentru astfel de tabel în MySQL:

Exemplul 2-1. Creați tabelul:

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`)
);

Să verificăm acum, cum putem interoga. În general, ideea este de a face acest lucru prin selectarea automată a rândurilor corecte în funcție de limba aleasă:

Exemplul 2-2. Utilizarea tabelului:

<?php
// Retrieve titles for all languages
$sql = "SELECT * FROM `app_product` WHERE `id` = 1";
if($result = mysqli_query($link, $sql)){
    while($row = mysqli_fetch_assoc($result)){
        echo "Language (".$row["language_id"]."): ".$row["title"]."<br>";
    }
}

// Retrieve appropriate title according to the chosen language in the system
$sql = "SELECT `title`
        FROM `app_product`
        WHERE `language_id` = '".$_SESSION['current_language']."'";
if($result = mysqli_query($link, $sql)){
    if($row = mysqli_fetch_assoc($result)){
        echo "Current Language: ".$row["title"];
    }
}
?>

Avantaje:

• Simplitate - ușor de implementat;
• Interogare ușoară - nu sunt necesare JOIN-uri.

Dezavantaje:

• Greu de întreținut - fiecare coloană care nu este tradusă trebuie schimbată în toate rândurile pentru fiecare limbă. De exemplu, schimbarea prețului pentru un singur produs necesită repetarea acestei operații pentru toate limbile;
• Greu de adăugat o limbă nouă - necesită repetarea operației de inserare pentru fiecare limbă (clonarea înregistrării pentru limba implicită);
• Conținutul duplicat - veți avea o mulțime de conținut duplicat pentru toate coloanele care nu sunt traduse.

3. Abordarea tabelului de traducere unică

Această soluție pare a fi cea mai curată din perspectiva structurii bazelor de date. Stocați toate textele care trebuie traduse într-un singur tabel de traducere. Este mai potrivit pentru site-urile dinamice și care au un număr mare de limbi sau care intenționează să adauge o nouă limbă în viitor și doresc să o facă cu ușurință. Mai jos exemplul unei astfel de scheme de baze de date în MySQL:

Exemplul 3-1. Creați tabelul:

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;

Acum ne permite să verificăm modul în care am putea interoga.

Exemplul 3-2. Utilizarea metodei tabelului:

<?php
// Retrieve titles for all languages
$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 = mysqli_query($link, $sql)){
    while($row = mysqli_fetch_assoc($result)){
        echo "Language (".$row["language_name"]."): ".$row["title"]."<br>";
    }
}

// Retrieve appropriate title according to the chosen language in the system
$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 = mysqli_query($link, $sql)){
    if($row = mysqli_fetch_assoc($result)){
        echo "Current Language: ".$row["title"];
    }
}
?>

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;

<?php
// Retrieve titles for all languages
$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 = mysqli_query($link, $sql)){
    while($row = mysqli_fetch_assoc($result)){
        echo "Language (".$row["language_name"]."): ".$row["title"]."<br>";
    }
}

// Retrieve appropriate title according to the chosen language in the system
$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 = mysqli_query($link, $sql)){
    if($row = mysqli_fetch_assoc($result)){
        echo "Current Language: ".$row["title"];
    }
}
?>