Confirmed users
675
edits
Update:Remora Localization: Difference between revisions
Jump to navigation
Jump to search
→Dynamic Strings
| Line 69: | Line 69: | ||
| zh-TW | text | YES | | NULL | | | | zh-TW | text | YES | | NULL | | | ||
+----------------+------------------+------+-----+---------+-------+ | +----------------+------------------+------+-----+---------+-------+ | ||
===Lars Digression=== | |||
From the standpoint of an old SQL guy, this technique just doesn't feel right. Here are a couple reasons. First it is brittle, changing or adding a locale means that the table schema must altered. Most likely, the code that accesses the table will need some adjustment, too. Second, the database's referential integrity checking is subverted because the foreign_id column refers to more than one table. The foreign key relationship seems backwards. | |||
Consider this alternative: make the translations table a simple three column table - a non-unique id, a locale and a string. Together the non-unique id and the locale make up the primary key. For each column in another table that needs a translation, replace that column with a partial key to the translations table id column. Whenever you select a row needing translations from a table, you simple add a join to the translations table using the partial key and the locale. | |||
+------------------+------------------+------+-----+---------+-------+ | |||
| Field | Type | Null | Key | Default | Extra | | |||
+------------------+------------------+------+-----+---------+-------+ | |||
| id | int(11) unsigned | | PRI | 0 | | | |||
| locale | varchar(10) | | PRI | | | | |||
| localized_string | text | yes | | | | | |||
+------------------+------------------+------+-----+---------+-------+ | |||
example: | |||
translation table | |||
+----+--------+------------------+ | |||
| id | locale | localized_string | | |||
+----+--------+------------------+ | |||
| 1 | en-us | hello | | |||
| 1 | de | guten tag | | |||
| 2 | en-us | help | | |||
| 2 | de | halp | | |||
+----+--------+------------------+ | |||
a table | |||
+----+----------+----------+ | |||
| id | greeting | danger | | |||
+----+----------+----------+ | |||
| 1 | 1 | 2 | | |||
+----+----------+----------+ | |||
sql for english | |||
select a.id, g.localized_string as greeting, d.localized_string as danger | |||
from ((a join translation as g on a.greeting = g.id) | |||
join translation as d on a.greeting = d.id) | |||
where | |||
g.locale = 'en-us' | |||
and d.locale = 'en-us'; | |||
+----+----------+----------+ | |||
| id | greeting | danger | | |||
+----+----------+----------+ | |||
| 1 | hello | help | | |||
+----+----------+----------+ | |||
== Updating locales == | == Updating locales == | ||