Refactoring a database to add support for internationalization / multiple languages

Question

Are there any proven ways of refactoring a database into supporting multiple versions of entries?

I've got a pretty straight forward database with some tables like:

article(id, title, contents, ...) 
...

This obviously works like a charm if you're only going to store one version of each article. I remember asking my client really clearly whether the system should be able to store articles in different languages, really stressing that it would be expensive to add this support later on. You can probably guess what the client said back then..

My current approach will be to create a couple of new tables like:

language(id, code, name)
article_index(id, original_title) <- just to be able to group articles

And then add a foreign key into the original article table:

article(id, title, contents, article_index_id, ...)

I would love to hear your comments to this approach and your experiences on the topic.

Answer 1

This is an approach I've used successfully in the past. Another is to replace all text fields with an identifier (int, guid, whatever you want), and then store translations for all the text fields in a single table, keyed on this identifier plus a language id.

Personally, I have had more success with the first approach (i.e. yours), and have, for instance, found it easier to deal with via an ORM. With an NHibernate ORM on my current project, for instance, I've created what amounts to a language-aware session, that returns the correct set of translations for each object automatically. Consistency in the approach obviously helps here.