多语言数据库:哪种方法更好?

I have a Website in 3 languages.

我有一个三种语言的网站。

Which is the best way to structure the DB?

构建DB的最佳方式是什么?

1) Create 3 table, one for every language (e.g. Product_en, Product_es, Product_de) and retrieve data from the table with an identifier:

1)创建3个表，每一种语言一个表(例如Product_en、Product_es、Product_de)，用标识符从表中检索数据:

e.g. on the php page I have a string:

例如，在php页面上，我有一个字符串:

$language = 'en'

so I get the data only

我只得到数据

SELECT FROM Product_$language

2) Create 1 table with:

2)创建1个表:

ID  LANGUAGE   NAME    DESCR

and post on the page only

只贴在纸上

WHERE LANGUAGE = '$language'

3) Create 1 table with:

3)创建1个表:

ID  NAME_EN   DESCR_EN   NAME_ES   DESCR_ES   NAME_DE   DESCR_DE

Thank you!

谢谢你！

3 个解决方案

#1

I'd rather go for the second option.

我宁愿选择第二个选择。

The first option for me seems not flexible enough for searching of records. What if you need to search for two languages? The best way you can do on that is to UNION the result of two SELECT statement. The third one seems to have data redundancy. It feels like you need to have a language on every names.

对我来说，第一个选项似乎不够灵活，无法搜索记录。如果需要搜索两种语言呢?最好的方法是将两个SELECT语句的结果联合起来。第三个似乎有数据冗余。感觉你需要在每个名字上都有一种语言。

The second one very flexible and handy. You can do whatever operations you want without adding some special methods unless you want to pivot the records.

第二个非常灵活和方便。您可以执行任何您想要的操作，而无需添加一些特殊的方法，除非您想要对记录进行透视。

#2

I would opt for option one or two. Which one really depends on your application and how you plan to access your data. When I have done similar localization in the past, I have used the single table approach.

我会选择一两个选项。哪一个取决于你的应用程序以及你计划如何访问你的数据。当我以前做过类似的本地化时，我使用了单表方法。

My preference to this approach is that you don't need to change the DB schema at all should you add additional localizations. You also should not need to change your related code in this case either, as language identifier just becomes another value that is used in the query.

我对这种方法的偏好是，您不需要更改DB模式，而应该添加额外的本地化。在这种情况下，您也不需要更改相关代码，因为语言标识符只是在查询中使用的另一个值。

#3

That way you would be killing the database in no-time.

这样你就会在没有时间的情况下杀死数据库。

Just do a table like:

就做一张桌子，比如:

TABLE languages with fields:

-- product name
-- product description
-- two-letter language code

This will allow you, not only to have a better structured database, but you could even have products that only have one translation. If you want you can even want to show the default language if no other is specified. That you'll do programmatically of course, but I think you get the idea.

这将允许您不仅拥有一个更好的结构化数据库，而且您甚至可以拥有只有一个转换的产品。如果需要，甚至可以在没有指定其他语言的情况下显示默认语言。当然你会用编程的方式来做，但我想你已经明白了。

#1