關於網路那些事...

網路行銷,SEO,網路趨勢,教學文章,網頁設計,生活時事

SQL 資料庫設計 - 讓產品支援多國語系的四種方式

| Comments

一、單列方式 (Column Approach)

在單一列,設定多個語系欄位

優點:
- 簡單 - 易於實施
- 輕鬆查詢 - 無需JOIN
- 沒有重複 - 沒有重複的內容(每個記錄只有一行,只有語言列被重複)
缺點:
- 很難維護 - 如果只有2-3種語言會很容易維護,但當您有很多欄或很多語言時,它變得非常龐大且不容易維護
- 很難添加新語言 - 添加新語言時,有設定多國語言的欄位都要進行新增調整(以及db用戶的特殊訪問權限)
- 佔用空間 - 如果不是所有的項目都必需翻譯(例如在某些地方,只需使用默認語言),它可能會導致冗餘數據或空的db字段
- 需要建立檢視判斷 - 需要依照目前語系環境決定要撈出哪一列,還需額外判斷才能達成

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

透過很簡單的Query就能取出資料

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

二、多列方式(Multirow Approach)

方法與第一種很像,但不是透過欄位的方式來建立多國語系,而透過一個語系欄位且不同列的方式來處理
優點:
- 簡單 - 易於實施
- 輕鬆查詢 - 無需JOIN
缺點:
- 難以維護 - 每個語言的所有列中必須更改未翻譯的每一列。例如,改變單一產品的價格需要對所有語言重複此操作
- 很難添加新語言 - 需要針對每個產品項目進行重複插入操作(從默認的語系作為模板進行複製)

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

通過語言欄位來取得值

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

三、單一翻譯表方法(Single Translation Table Approach)

這個解決方案似乎是從數據庫結構的角度來看最乾淨的。您將所有需要翻譯的文本存儲在單個翻譯表中。它更適合於動態網站,並且有大量的語言,或者打算在將來添加新的語言,並且想要輕鬆地做到這一點。下面的MySQL數據庫模式的例子:
優點:
- 正確歸一化 - 看起來像乾淨,關係的方法
- 輕鬆添加新語言 - 不需要模式更改
- 所有翻譯在一個地方 - 可讀/可維護的數據庫
缺點:
- 複雜查詢 - 檢索正確的產品描述所需的多個連接
- 難以維護 - 對所有操作進行簡單的查詢:插入,刪除和更新
- 所有翻譯在一個地方 - 一個缺失的表導致全球性問題

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;

查詢方式

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

四、附加翻譯表方法(Additional Translation Table Approach)

這是上述方法的一個相似的方式,但似乎更容易維護和使用。我們來看看為什麼:對於存儲可能需要翻譯的資訊表都建立一個附加的表。原始表格僅存儲語言不敏感的數據,新表則負責儲存的所有翻譯的內容。
優點:
- 正確規格化 - 看起來一致且乾淨的方法
- 輕鬆添加新語言 - 不需要更改schema
- 列保留名稱 - 不需要“_lang”後綴或其他內容
- 容易查詢 - 相對簡單的查詢(只需要一個JOIN)
缺點:
- 可能會增加表的數量 - 您必須為具有需要翻譯的列的所有表創建翻譯表

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

以上四種範例,可以依照使用情境來決定你要採用哪一種方法
第一、二種方法適用於小專案
第三、四種適用於大型網站

如果是大型網站,第四種較為推薦使用

參考翻譯來源 http://www.apphp.com/tutorials/index.php?page=multilanguage-database-design-in-mysql


聲明,近期發現有平台大量盜用文章內容,若您發現內文非來自關於網路那些事 [https://adon988.logdown.com](https://adon988.logdown.com) ,希望您與我聯繫 [https://www.facebook.com/ThinkingWebsite](https://www.facebook.com/ThinkingWebsite)
(本文章源自"關於網路那些事)

最後,如果你喜歡這篇文章,請幫忙點個讚



最新文章推薦

討論

comments powered by Disqus