Confirmed users
675
edits
Update:Remora Localization: Difference between revisions
Jump to navigation
Jump to search
m
→Lars Digression
m (→cons) |
|||
| Line 75: | Line 75: | ||
Here are a couple reasons why the translations table schema bothers me. First it is brittle, changing or adding a locale means that the table schema must be altered. Does the code have to be modified to handle a changing schema? Second, the database's referential integrity checking is subverted because the foreign_id column refers to more than one table. I've never seen that kind of feature supported for referential integrity in an RDBMS. That means the referential integrity must be handled externally. The foreign key relationship seems backwards to me. | Here are a couple reasons why the translations table schema bothers me. First it is brittle, changing or adding a locale means that the table schema must be altered. Does the code have to be modified to handle a changing schema? Second, the database's referential integrity checking is subverted because the foreign_id column refers to more than one table. I've never seen that kind of feature supported for referential integrity in an RDBMS. That means the referential integrity must be handled externally. The foreign key relationship seems backwards to me. | ||
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 | 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 simply add a join to the translations table using the partial key and the locale. | ||
+------------------+------------------+------+-----+---------+-------+ | +------------------+------------------+------+-----+---------+-------+ | ||