存储多语言字符串的最佳做法 [英] Best practise for storing multilingual strings
问题描述
我需要在Postgres表中存储不同语言(2-4种语言)的不是很长的字符串。
I need to store different versions of not very long strings for different languages (2-4 languages) in a Postgres table.
最好的做法是什么?那?数组或JSON或类似的东西?
What is the best way of doing that? Array or JSON or something like that?
推荐答案
首先确保数据库区域设置可以处理不同的语言。使用UTF-8服务器编码。可选地将 LC_COLLATE ='C'
设置为中立,或者使用第一种语言的排序规则具有默认排序顺序。请阅读手册中的整理支持一章。
First make sure that the database locale can deal with different languages. Use an UTF-8 server-encoding. Optionally set LC_COLLATE = 'C'
to be on neutral ground or use a collation for your first language to have a default sort order. Start by reading the chapter Collation Support in the manual.
我强烈建议您使用最新版本的PostgreSQL(9.1写入时),因为它具有优越的排序规则支持。
I would strongly suggest that you use the latest version of PostgreSQL (9.1 at time of writing) because it has superior collation support.
至于表结构:保持简单。听起来好像有很少的固定数量的语言来处理。您可以为每种语言提供一列:
As for the table structure: keep it simple. It sounds like there is a low, fixed number of languages to deal with. You could just have a column for each language then:
CREATE TABLE txt (
txt_id serial PRIMARY KEY
,txt text NOT NULL -- master language NOT NULL?
,txt_fr text -- others can be NULL?
,txt_es text
,txt_de text
);
这是非常有效的,即使有很多语言。 NULL存储非常便宜。
如果您有不同的语言来处理,单独的表可能是更好的解决方案。该解决方案假设您有一个主语言,其中字符串始终存在:
This is pretty effective, even with many languages. NULL storage is very cheap.
If you have a varying number of languages to deal with, a separate table might be the better solution. This solution assumes that you have a "master language", where the string is always present:
CREATE TABLE txt (
txt_id serial PRIMARY KEY
,txt text NOT NULL -- master language NOT NULL?
);
CREATE TABLE lang (
lang_abbr text PRIMARY KEY -- de, es, fr, ...
,lang text NOT NULL
,note text
);
或者,如果(两个字母)的缩写就足够了,只需创建一个 枚举
类型来标识语言。
Or, if a (two-letter) abbreviation is enough, just create a enum
type to identify the language.
CREATE TABLE txt_trans (
txt_id int REFERENCES txt(txt_id) ON UPDATE CASCADE ON DELETE CASCADE
,lang_abbr text REFERENCES lang(lang_abbr) ON UPDATE CASCADE
,txt text NOT NULL -- master language NOT NULL?
,CONSTRAINT txt_trans_pkey PRIMARY KEY (txt_id, lang_abbr)
);
不处理主语言特殊,并保持所有语言变体相同表可能会使你的应用程序更容易处理。但这真的取决于你的要求。
Not treating the master language special and keeping all language variants in the same table might make handling in your app simpler. But it really depends on your requirements.
这篇关于存储多语言字符串的最佳做法的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!