双语网站设计
我正在建立一个双语网站，想知道用于产品数据库的最佳设计。该网站将只使用两种语言，将来绝对没有修改该网站以使用两种以上语言的计划。
因此，考虑到这一点，设计产品数据库架构以支持两种语言的最简单方法是什么？我可以想到两种方法来做到这一点：
- 为每个翻译都有一个单独的记录。像这样的：
产品表：
编号
创建日期
价格
产品信息表：
编号
产品名称
描述
语言
Product_id
通过此设计，我们将为产品表中的每个条目在product_info 表中创建两个条目。产品信息表中的两个条目将由一个包含英语文本的记录和另一个包含日语文本的记录组成，语言字段充当标志来指示存储记录的语言。
- 第二种方法是简单地为两种翻译保留一条记录，但每条记录都会有一个日语和一个英语字段来存储相应的翻译。像这样的：
产品表：
编号
创建日期
价格
产品信息表：
编号
产品名称
描述_zh
产品名称_jp
描述_jp
Product_id
我认为，如果我们在网站中使用 2 种以上语言，或者我们计划将来将网站扩展到 3 种以上语言，那么第一个设计是有意义的。但在我们的例子中，该网站永远不会使用超过 2 种语言。因此，如果我们的网站上只有两种语言，我不确定这是否是最好的设计方法。第二种设计似乎是一种适用于两种语言的更简单的方法，但我担心我可能会忽略这里的一些东西，并且很难决定使用哪种方法。
对这些方法有什么想法/意见吗？我很想听。
谢谢！
I am building a bi-lingual website and am wondering about the best design to use for the product database. The site will only ever use two languages, there are absolutely no plans to modify the site to use more than two languages in the future.
So with that in mind, what would be the easiest approach to designing the schema for the product database to support two languages? I can think of two ways of doing this:
- Have a separate record for each translation. Something like this:
product table:
id
date_created
price
product_info table:
id
product_name
description
language
product_id
With this design, we would create two entries in the product_info table for every entry in the product table. The two entries in the product info table would consist of one record containing the English text and the other record containing the Japanese text, and the language field acts as a flag to indicate the language that the record is stored in.
- The second approach would be to simply keep one record for both translations, but each record will have a Japanese and an English field to store the corresponding translation. Something like this:
product table:
id
date_created
price
product_info table:
id
product_name_en
description_en
product_name_jp
description_jp
product_id
I think the first design would make sense if we are working with more than 2 languages in the site, or if we have plans to extend the site into 3+ languages in the future. But in our case, the site will never use more than 2 languages. So, I am not sure if this is the best approach for design if we will only ever have 2 languages on the site. The second design seems like a simpler approach for 2 languages, but I am afraid I may be overlooking something here and am having a hard time deciding which approach to use.
Any thoughts/opinions on these approaches? I would love to hear.
Thanks!
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
永不说永不。我会选择一个单独的产品信息记录，其中包含一组信息和一种语言（第一个选项）。对多语言代码进行必要的检查是小菜一碟（如果构建正确，只需在少量查询中添加语言 ID）。
您应该创建一个通用处理程序来确定您想要的语言。产品和将生成输出的每个其他元素应该具有一组使用该语言内部的属性。例如，如果你有一个 Product 对象，它会给你一个 getDate 和 GetPrice 方法，还有一个 getProductName 和 getDescription 方法。在内部，它将使用全局可用的语言来获取正确的信息并通过这些方法返回它们。
这样，您只需要一个位置来根据（子）域或给定的会话变量确定正确的语言，并且您只需要少数几个位置（从数据库加载数据）即可采用该语言在帐户中。
对于网站的其余部分（唯一的例外是 CMS），甚至不会有语言这样的东西。一切都将完全透明。它只会显示“ProductName”，不知道它甚至可能有不同的翻译。
通过这种方式，您几乎不需要任何额外的开发成本，并且您的网站将支持未来所需的尽可能多的语言。
Never say never. I'd choose a separate product info record that contains one set of info and a language (the first option). It's a piece of cake to do the necessary checks for multilingual code (just add a language id in a handful of queries if you build it right).
You should make a general handler that determines the languages you want. The products and each other element that will generate output, should have a set of properties that use that language internalle. E.g. if you got a Product object, it will give you a getDate and GetPrice method, but also a getProductName and getDescription method. Internally it will use the globally available language to get the right information and return them via those methods.
That way, you will only need one place to determine the right language based on the (sub) domain or a given session variable, and you will only need a handful of places, where you load the data from the database, to take that language in account.
For the rest of your website, the only exception being the CMS, there won't even be such a thing as a language. Everything will be completely transparent. It will just display 'ProductName', not knowing it could even have different translations.
It will hardly cost you any extra development to do it this way, and your site will support as many languages as will ever be needed in the future.
由于您赞成在新建项目中开发架构，因此您应该始终选择最适合您的用例的选项。您谈论的是两种语言，而不是更多，但这正是大多数数据库设计人员在接到客户指示在您的案例中添加更多功能或语言之前对他们的项目的思考。
如果有一个选项不会限制您轻松添加更多语言的可能性，至少不会增加实现它的复杂性和时间，那么您为什么要选择另一种呢？
因此，如果我是你，我的决定是第一个选择，不是因为你可以扩展它，而是因为它是一个干净的设计，你可以轻松地在其他应用程序层中轻松抽象多语言事物。
您可以使用单列来标识记录的语言，而不必为特定语言所需的每个属性处理多个列（按名称）。对于性能问题，您可以使用与您的搜索条件匹配的多列索引（id+language_id）。
As you are in favour of developing the schema at a greenfield project, you should always choose the option that's fitting your usecase best. Your talking about two languages and NOT MORE, but exactly that were most database-designers thinking of their projects, before they got instructed of their customers to add some more functionality or languages in your case.
If there's an option that doesn't limit your possibility to add a few more languages with ease, that at least DOESN'T extend the complexity and time to implement it, then why would you choose the other one?
So if i were you, my decision was the first option not because you can extend it, but because of it's a clean design you can easily where you can abstract the multi-language thing in other application layers easily.
You can identify the language of the records with a single column and don't have to address multiple colums (by name) for each attribute you need for a specific language. For performance issues you can use multi-column indices matching your searching criteria (id+language_id).