Baze de date multilanguage în MySQL

05

martie 2018

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.

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"];
    }
}
?>

Avantaje:

  • Normalizare adecvată- pare a fi o abordare curată, relațională;
  • Ușor în adăugarea unei noi limbi - nu necesită schimbări de schemă;
  • Toate traducerile dintr-un singur loc - baza de date lizibilă și întreținută.

Dezavantaje:

  • Interogarea complexă - multiple conexiuni necesare pentru a obține descrierea corectă a produsului;
  • Greu de întreținut - interogarea suprapusă a tuturor operațiunilor: inserarea, eliminarea și actualizarea;
  • Toate traducerile într-un singur loc - o tabelă lipsă duce la probleme globale.

4. Abordarea cu tabel suplimentar pentru traducere

Aceasta este o variantă a abordării de mai sus și se pare că este mai ușor să se mențină și să se lucreze cu aceasta. Să verificăm de ce: pentru fiecare tabel care stochează informațiile care ar putea fi traduse, se creează un tabel suplimentar. Tabelul original stochează numai datele insensibile în funcție de limbă, iar cel nou toate informațiile traduse. Mai jos exemplul unei astfel de scheme de baze de date în MySQL:

Exemplul 4-1. Creați tabelul:

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;

Aici exemplul modului în care am putea să-l interogăm.

Exemplul 4-2. Utilizarea metodei suplimentare de tabel:

<?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"];
    }
}
?>

Avantaje:

  • Normalizarea adecvată - pare a fi o abordare curată, relațională;
  • Ușor în adăugarea unei noi limbi - nu necesită schimbări de schemă;
  • Coloanele mențin acolo nume - nu necesită sufixe "_lang" sau altceva;
  • Ușor de interogat - interogare relativ simplă (este necesară doar o singură conectare).

Dezavantaje:

  • Poate dubla cantitatea de tabele - Trebuie să creați tabele de traducere pentru toate tabelele care au coloane care trebuie traduse.

Concluzii

Aceste 4 exemple prezentate mai sus ne dau o idee despre modul în care pot fi utilizate diferite abordări aici. Acestea sunt, desigur, nu toate opțiunile posibile, doar cele mai populare. Puteți să le modificați întotdeauna, de ex. prin introducerea unor opinii suplimentare care vă vor ajuta să vă scrieți conexiuni complexe direct din codul dvs.

Amintiți-vă că o soluție pe care o alegeți depinde mai mult de cerințele dvs. de proiect. Dacă aveți nevoie de simplitate și sunteți sigur că numărul de limbi acceptate este mic și fix, puteți merge cu opțiunea 1. Dacă aveți nevoie de un pic mai multă flexibilitate și vă puteți permite o simplă aderare la interogarea opțiunilor de date multilingve 3 sau 4 ar fi o soluție posibilă.

În ApPHP folosim în mod obișnuit cea de-a 4-a soluție, adică abordarea suplimentară a tabelului de traducere.

 

Sursă: https://www.apphp.com/tutorials/index.php?page=multilanguage-database-design-in-mysql

Distribuie această postare