0

I've been researching on how to create a schema for a database that contains multiple languages. So I decided to separate the translations on a different table with a column as the language and another column(s) with the translated text in that language. So I decided in the following schema:

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

And I can query the data I need this way:

-- 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'

While the data sample I'm currently working with isn't large at all and when I tried the code above it worked without any issues, so: is this the best approach for a multi language database schema and querying? Can it be improved on?

closed as too broad by MDCCL, Gaius, Marcello Miorelli, mustaccio, kevinsky Aug 12 at 13:02

Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

0

Your data model is good.

The only addition I'd make is to add a primary key to the “language” tables that consists of (*_id, language). Every table should have a primary key, and there should be an index on the foreign key column.

Another thing you could do is to use an enum type or a lookup table for the language rather than using text. That avoids typos.

  • I see. Would this still be the way to go even if I had more tables which translations to JOIN on? (Presumably 2 - 3 more JOINs) – Dimbreath Aug 12 at 11:03
  • One translation table per table with columns that need to be translated should be fine. The key point is always the resulting query. If that is too complicated and slow, that would be an indication to look for another design. – Laurenz Albe Aug 12 at 11:09

Not the answer you're looking for? Browse other questions tagged or ask your own question.