设计本地化数据库架构 [英] Designing a Localized Database Schema

问题描述

可能重复：

多语言数据库的架构

我正在开发我打算以多种语言提供的Web应用程序。当我设计数据库时，我将通过两种不同的方式来回收存储本地化描述和数据库中的内容。

I'm working on a web application that I plan to make available in multiple languages. As I design the database, I'm going back and forth between two different ways to store localized descriptions and whatnot in the database.

第一个选项是众所周知的table_name ，table_name_ml类型选项：

First option is the well-known table_name, table_name_ml type option:

TABLE Category (
   ID int,
   ParentID int,
   Name varchar(50),
   Description varchar(255)
)

TABLE Category_ML (
   ID int,
   CategoryID int,
   LocaleID int,
   Name varchar(50),
   Description varchar(255)
)

第二个选项是根本不将文本存储在基表中，而是存储可用于在其他地方查找实际本地化文本的令牌，如下所示：

The second option would be to not store the text in the base tables at all, but instead store a token that could be used to lookup the actual localized text elsewhere, like this:

TABLE Category (
   ID int,
   ParentID int,
   NameToken varchar(50),
   DescriptionToken varchar(50),
)

// Tables in a separate content management type system
TABLE Content (
   ID int,
   Token varchar(50)
)

TABLE Translation (
   ID int
   ContentID int,
   LocaleID int,
   Value text
)

这里的想法是内容和翻译表将保存数据库中许多不同实体的本地化文本。服务层将只使用令牌返回基础对象，并且视图层将使用内容/翻译表查找实际的文本值 - 这将被高度缓存。内容/翻译表也将用于存储其他CMS类型的内容（网页上的静态文本等）。

The idea here is that the Content and Translation tables would hold the localized text for many different entities in the database. The service layer would return the base objects with just the tokens and the view layer would look up the actual text values using the Content/Translation tables - which would be heavily cached. The Content/Translation tables would also be used for storing other CMS type content (static text on web pages, etc.)

我喜欢第一个选项，因为它是尝试和真实的，但第二个选项似乎还有其他许多优点：

I like the first option because it's tried and true, but the second option seems to have so many other advantages:

1. 我所有的文本/本地化内容都在一个地方（使翻译更容易）


2. 服务层并不需要关心区域设置。


3. 简化了查询，不需要加入一堆ML类型的表。

由于我以前从未见过这样的设计，我认为我必须缺少一些东西。有什么好的理由不设计这样吗？或者也许还有一个更好的选择，我没有想过？

Since I've never seen a design like this before, I assume I must be missing something. Any good reasons not to design it this way? Or maybe there's a better option that I haven't thought of?

推荐答案

我会先说我没有处理本地化之前，这只是我的意见，而不是基于经验。

I will first say that I have no dealt with localization before so this is really just my opinion and not based on experience.

我喜欢你的第二个选择。就数据库而言，它的数据和访问/操纵数据的方式。在这种情况下，所有的数据都在那里，你将主要阅读它，并有一个很好的方法。您可以在两种情况下回答同样的问题。我宁愿自己选择第二个选项，因为它可以减少疯狂的桌子。你正在为了翻译的具体目的而留下一张桌子。您可以重复使用（以后不再创建更多的表进行升级），并保持完整性。你甚至可以重用名称，如果它是有意义的。喜欢，如果你有Mantequilla作为一个类别，作为一个喜爱的其他地方。

I like your 2nd option. As far as the DB goes.. its data and a way to access/manipulate the data. In this case all the data is there and you will be mostly reading it and have a good way of getting to it. You can answer the same problem in both scenarios. I would prefer the 2nd option myself because it reduces crazy tables everywhere. You are keeping a table around for the specific purpose of translation. You can reuse it (no creating more tables just for upgrades later) and it maintains integrity. You could even reuse names if it makes sense somewhere. Like if you had 'Mantequilla' as a Category and as a Favorite somewhere else.

我希望尽可能将相关数据放在一张表中，并且没有与多个地方翻译相关的数据。

I like to put related data in one table when possible and not have data related to 'translating' in multiple places.

唯一可能会失败的地方在于，如果您需要翻译的内容不仅仅是Name和Description。也许你有一个项目的名称，描述，代码，魔术词，傻昵称等。虽然你可以通过在相关表中添加更多的NameTokens并重新使用Name来解决这个问题，但这是一个黑客。

The only place this may fail is if you have more than just Name and Description for something that needs translation. Maybe you have Name, Description, Code, Magic Word, Silly Nickname, etc for an item. Although you could get around this by adding more NameTokens in that relevant table and reusing Name, but this is a bit of a hack.

只需确保模型满足您的需求每个人都应该工作正常如果需要特定的表格，您可以随时投入特殊的翻译表格。这不会与创建大量表格不同，尽管混合解决方案可能会令人困惑。最好找到一种方法，并尝试坚持下去。

Just make sure the model meets your needs everyone and it should work fine. You can always throw in a special translation table if needed later for a specific table. This wouldn't be to different from creating lots of table although a hybrid solution could be confusing. It is best to find one way and try to stick to it.

这篇关于设计本地化数据库架构的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！