多語言表的模式和查詢設計

我一直在研究如何為包含多種語言的數據庫創建模式。因此，我決定將不同表上的翻譯分開，其中一列作為語言，另一列與該語言的翻譯文本分開。所以我決定採用以下模式：

CREATE TABLE IF NOT EXISTS Meowficer (
   meowficer_id SMALLINT PRIMARY KEY,
   skill_id SMALLINT UNIQUE NOT NULL,
   -- Cut out irrelevant columns.
);

CREATE TABLE IF NOT EXISTS MeowficerName (
   meowficer_id SMALLINT NOT NULL,
   language TEXT NOT NULL,
   name TEXT NOT NULL,

   FOREIGN KEY(meowficer_id) REFERENCES Meowficer(meowficer_id) 
);

CREATE TABLE IF NOT EXISTS MeowficerSkill (
   skill_id SMALLINT PRIMARY KEY,
   next_id SMALLINT NOT NULL,
   level SMALLINT NOT NULL,
   experience SMALLINT NOT NULL,
   icon TEXT NOT NULL,

   -- Only one Skill for every Mewoficer.
   FOREIGN KEY(skill_id) REFERENCES Meowficer(skill_id)
);

CREATE TABLE IF NOT EXISTS MeowficerSkillNameDesc (
   skill_id SMALLINT NOT NULL,
   language TEXT NOT NULL,
   skill_name TEXT NOT NULL,
   skill_1 TEXT NOT NULL,
   skill_2 TEXT NOT NULL,
   skill_3 TEXT NOT NULL,

   FOREIGN KEY (skill_id) REFERENCES MeowficerSkill(skill_id)
);

我可以這樣查詢我需要的數據：

-- Cut out part of the column selecting from the Meowficer table.
SELECT
   Meowficer.id, Meowficer.skill_id, MeowficerName.name, ..., MeowficerSkill.icon, MeowficerSkillNameDesc.skill_name, MeowficerSkillNameDesc.skill_1, MeowficerSkillNameDesc.skill_2, MeowficerSkillNameDesc.skill_3
FROM 
   Meowficer 
JOIN 
   MeowficerName ON Meowficer.id = MeowficerName.id AND MeowficerName.language = 'ja' AND MeowficerName.name = 'ジャスティス'
JOIN 
   MeowficerSkill ON Meowficer.skill_id = MeowficerSkill.skill_id AND MeowficerSkill.level = 2
JOIN
   MeowficerSkillNameDesc ON MeowficerSkill.skill_id = MeowficerSkillNameDesc.skill_id AND MeowficerSkillNameDesc.language = 'ja'

雖然我目前使用的數據樣本並不大，並且當我嘗試上面的程式碼時它沒有任何問題，所以：這是多語言數據庫模式和查詢的最佳方法嗎？可以改進嗎？

你的數據模型很好。

我要做的唯一補充是向由(*_id, language). 每個表都應該有一個主鍵，並且在外鍵列上應該有一個索引。

您可以做的另一件事是對語言使用列舉類型或查找表，而不是使用text. 這樣可以避免錯別字。

引用自：https://dba.stackexchange.com/questions/245074