Posted by Rik on 02/06/07 15:50

Michael Fesser <netizen@gmx.de> wrote:

>> Thanks. Outside of language thing, I would like to know, how this kinf
>> of
>> sister tables are declared in mysql. Or do you just manually keep their
>> indexing in harmony, removing and inserting records at the same time to
>> both
>> tables.
>
> Removing and updating records can be handled by the DB itself, using
> FOREIGN KEY constraints (I can post an example if necessary). Inserting
> new records requires a bit more action, but is usually done with a
> script, not by hand.

Foreign keys indeed rock. And as translations will always have to be
entered by a human having a seperate code for this is no bother.

I usually define a 'fallback' (usually english) in the normal table, with
a simple LEFT JOIN on the translations, and
IFNULL(translations.field,normal_table.field) statement. Can seem ugly for
foreign users, a portion of untranslated text, but this way I can leave it
up to the client to decide to either have an empty text (in which case it
won't be NULL) or the fallback.
--
Rik Wasmus

[Back to original message]