多语言数据库(Multi

多语言数据库(Multi-language database)

我正在开发一个支持阿拉伯语和英语的Android应用程序，但我仍然对数据库设计感到困惑：

我遵循的方法是

创建一个包含两个记录的语言表（阿拉伯语和英语） 创建一个包含普通数据的表（不是特定于语言的） 创建一个包含特定语言内容的转换表

例如，为了实现（帮助），我创建了以下表：

Language (int id, text Code) Help (int id) Help_translation(int id, int Help_id, int Language_id, text Question, text Answer)

我已经阅读了很多次这个解决方案，但我仍然不知道为什么我们为（帮助）设置一个单独的表，什么都不做！

我的方法有问题吗？

I am developing an Android application that supports Arabic and English, but I am still confused about the database design:

The approach I am following is

creating a language table that holds two records (Arabic and English) creating a table that holds normal data (not language specific) creating a translation table that holds language specific content

For example to implement (Help) I created the following tables:

Language (int id, text Code) Help (int id) Help_translation(int id, int Help_id, int Language_id, text Question, text Answer)

I have read this solution so many time, but I still don't know why do we have a separate table for (Help) that does nothing!

Is there something wrong about my approach?

最满意答案

从表面上看，语言表将在系统中出现一次。 我建议使用基于ISO国家代码（ISO 3166）和ISO语言代码（ISO 639-2）的语言和区域的'll_tt'表示法。 因此，你可能有英国英语en_gb ，美国英语en_us ，沙特阿拉伯阿拉伯语ar_eg和埃及阿拉伯语ar_eg 。 这可能比您目前需要的更强大，但为您提供了未来的方向。

因此，Language表可能包含以下列：

id integer（自动增量）主键。 code char（5） - 独特。

设计中的“帮助”表用于“文档”目的。 它记录了应用程序可以使用的有效帮助号码。 表的更完整版本可能包含有关消息的信息：在应用程序中使用它的位置; 引入了哪个版本; 哪个版本已经过时（或者可能不是 - 我们有国际化的消息文件，必须在大约10年的时间内用于发布）; 与版本相关的日期 - 或日期而不是版本; 和“翻译笔记”（如果需要任何特殊指导，如何翻译信息的指导原则）。

帮助表的简约版本只包含帮助ID号暂时可以执行：

id整数（自动增量）。 首要的关键。

Help_Translation表存储应用程序将显示的字符串。 id列在这里是最小的值; 我会省略它（但如果你愿意，你可以保留它）。 Help_ID列是Help表的外键引用; Language_ID列是Language表的外键引用。 对于您选择的应用程序，每个帮助项目似乎都有问题和答案。

因此，Help_Translation表包含以下列：

id整数（自动增量，可选，不确定何时使用它）。 Help_ID integer - 外键引用帮助（ID）。 Language_ID integer - 外键引用语言（ID）。 Question文本 - 给定Help_ID的适当语言的问题。 Answer文本 - 给定Help_ID的适当语言的问题答案。 主键： Help_ID和Language_ID 。

另一种设计只有一个带有“消息ID”和“语言ID”的翻译表以及翻译后的字符串（消息ID和语言ID上的主键）。 将有一个消息表，标识有效的消息ID以及先前类型大纲的支持数据。 这可能包括在没有特定语言/领域的消息的翻译版本时使用（未翻译）的默认消息（或者您可以组成更复杂的方案来处理丢失的消息，以便在请求阿拉伯语时进行翻译因为，利比亚（LY）是不完整的，它会回到ar_sa ，然后再回到ar_sa上。 然后，您的帮助表可能包含一个帮助ID，以及两个消息ID值，一个用于问题，另一个用于答案。 此方案的优点是所有已翻译的消息都在一个表中。

毫无疑问，可以设计出其他方案。

On the face of it, the Language table will appear once in the system. I suggest using the 'll_tt' notation for language and territory based on the ISO country codes (ISO 3166) and ISO language codes (ISO 639-2). Thus, you might have en_gb for British English, en_us for US English, and ar_sa for Saudi Arabian Arabic, and ar_eg for Egyptian Arabic. This may be more powerful than you currently need, but gives you a direction for the future.

Thus, the Language table might have columns:

id integer (autoincrement) Primary key. code char(5) — Unique.

The Help table in your design is for 'documentation' purposes. It records the valid help numbers that the application can use. A fuller version of the table might include information about the message: where it is used in the application; which version it was introduced in; which version it became obsolete with (or maybe not — we have internationalized message files that have to be good for use with releases over a period of about 10 years); dates associated with the version — or dates instead of the version; and 'notes to the translator' (guidelines for how to translate the message, if any special guidelines are needed).

The minimalistic version of the Help table with just the Help ID number will do for the time being:

id integer (autoincrement). Primary key.

The Help_Translation table stores the strings that the application will display. The id column is of minimal value here; I would omit it (but you can keep it if you wish). The Help_ID column is a foreign key reference to the Help table; the Language_ID column is a foreign key reference to the Language table. For your chosen application, there appears to be both a question and an answer for each help item.

Therefore, the Help_Translation table has the columns:

id integer (autoincrement, optional, not sure when you'll use it). Help_ID integer — foreign key references Help(ID). Language_ID integer — foreign key references Language(ID). Question text — the question in the appropriate language for the given Help_ID. Answer text — the answer to the question in the appropriate language for the given Help_ID. Primary key: Help_ID and Language_ID.

An alternative design would have simply a translation table with a 'Message ID' and a 'Language ID' and the translated string (primary key on Message ID and Language ID). There would be a table of Messages, identifying the valid Message IDs plus supporting data of the type outline previously. This might include the default message to be used (untranslated) when there is no translated version of the message for a particular language/territory (or you can make up more complex schemes for handling missing messages, so that when Arabic is requested but the translation for, say, Libya (LY) is incomplete, it falls back onto ar_sa and only then onto en_gb). Your Help table might then contain a Help ID, plus two Message ID values, one for the Question and one for the Answer. The advantage of this scheme is that all translated messages are in a single table.

There are undoubtedly other schemes that could be devised.