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

一、單列方式 (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