国际化的 MySQL 数据库设计
我将开始开发一个中型应用程序，并计划它的数据库设计。 我不确定的一件事是这一点。 我将有许多需要国际化的表，例如：“membership_options、gender_options、language_options 等”，
每个表都将共享通用的 i18n 字段，例如： “标题，替代标题，简短描述，描述”
您认为最好的方法是什么？ 是否有一个 i18n 表，其中每个需要它们的表都具有相同的字段？
或者做类似的事情：
Membership table Gender table
---------------- --------------
id | created_at id | created_at
1 - 22.03.2001 1 - 14.08.2002
2 - 22.03.2001 2 - 14.08.2002
General translation table
-------------------------
record_id | table_name | string_name | alternative_title| .... |id_language
1 - membership regular null 1 (english)
1 - membership normale null 2 (italian)
1 - gender man null 1(english)
1 -gender uomo null 2(italian)
这将避免我重复类似的事情：
membership_translation table
-----------------------------
membership_id | name | alternative_title | id_lang
1 regular null 1
1 normale null 2
gender_translation table
-----------------------------
gender_id | name | alternative_title | id_lang
1 man null 1
1 uomo null 2
等等，所以我可能会减少数据库表的数量，但我不确定性能。我不是一个数据库设计师，所以请让我知道。
I'm going to start work on a medium sized application, and i'm planning it's db design.
One thing that I'm not sure about is this.
I will have many tables which will need internationalization, such as: "membership_options, gender_options, language_options etc"
Each of these tables will share common i18n fields, like:
"title, alternative_title, short_description, description"
In your opinion which is the best way to do it?
Have an i18n table with the same fields for each of the tables that will need them?
or do something like:
Membership table Gender table
---------------- --------------
id | created_at id | created_at
1 - 22.03.2001 1 - 14.08.2002
2 - 22.03.2001 2 - 14.08.2002
General translation table
-------------------------
record_id | table_name | string_name | alternative_title| .... |id_language
1 - membership regular null 1 (english)
1 - membership normale null 2 (italian)
1 - gender man null 1(english)
1 -gender uomo null 2(italian)
This would avoid me repeating something like:
membership_translation table
-----------------------------
membership_id | name | alternative_title | id_lang
1 regular null 1
1 normale null 2
gender_translation table
-----------------------------
gender_id | name | alternative_title | id_lang
1 man null 1
1 uomo null 2
and so on, so i would probably reduce the number of db tables, but i'm not sure about performance.I'm not much of a DB designer, so please let me know.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
我见过的最常见的方法是使用两个表，
membership和membership_ml，其中一个存储基值，而 ml 表存储本地化字符串。这与您的第二个选项类似。我看到的大多数系统都是这样制作的，因为它们从一开始就没有考虑国际化，所以额外的 _ml 表是后来“添加”的。我认为更好的选择与您的第一个选择类似，但略有不同。您将有一个用于存储所有翻译的中央表，但您将使用令牌和中央“内容”表来存储所有翻译，而不是将表名称和字段名称放在那里。这样，如果您愿意，您也可以在基表中的标记和内容表中的翻译之间强制执行某种 RI。
实际上，我不久前就这件事问了一个问题，所以你可以有一个查看该内容以获取更多信息（而不是在此处重新粘贴架构示例）。
The most common way I've seen this done is with two tables,
membershipandmembership_ml, with one storing the base values and the ml table storing the localized strings. This is similar to your second option. Most of the systems I see like this are made that way because they weren't designed with internationalization in mind from the get go, so the extra _ml tables were "tacked on" later.What I think is a better option is similar to your first option, but a little bit different. You would have a central table for storing all the translations, but instead of putting the table name and field name in there, you would use tokens and a central "Content" table to store all the translations. That way you can enforce some kind of RI between the tokens in the base table and the translations in the Content table if you want as well.
I actually asked a question about this very thing a while back, so you can have a look at that for some more info (rather than repasting the schema examples here).
我还认为最好的解决方案是将翻译保留在不同的表上。这种方法使用了Open Cart，它是开源的，你可以看看它处理问题的方式。另一个信息来源是这里“http://www.gsdesign.ro/blog /multilanguage-database-design-approach/”，特别是在评论部分
I also think the best solution is to keep translations on different table. This approach use Open Cart which is open source and you can take a look the way it deals with the problem. Another source of information is here "http://www.gsdesign.ro/blog/multilanguage-database-design-approach/" especially on the comments sections