Multilingual database design pattern

Question

I'm currently working on Blog-Software which should offer support for content in multiple languages.

I'm thinking of a way to design my database (MySQL). My first thought was the following:

• Every entry is stored in a table (lets call it entries). This table holds information which doesn't change (like the unique ID, if it's published or not and the post-type).
• Another table (let's call it content) contains the strings (like the content, the headline, the date, and author of the specific language).
• They are then joined by the unique entry-id.

The idea of this is that one article can be translated into multiple other languages, but it doesn't need to be. If there is no translation in the native language of the user (determined by his IP or something), he sees the standard language (which would be English).

For me this sounds like a simple multilingual database and I'm sure there is a design pattern for this. Sadly, I didn't find any.

If there is no pattern, how would you go about realizing this? Any input is greatly appreciated.

Answer 1

Your approach is what I've seen in most applications with this kind of capability. The only changing piece is that some places will put the "default" values into the base table (Entry) while others will treat it as just another Content row.

Answer 2

That design will also give you the ability to search (or restrict search) in all languages easily. From a db design perspective, its imho the best design you can use.

Answer 3

With small amounts of text and a simple application this would work. In the large, you might be bitten by the extra joins needed, especially when your database is larger than ram. Presenting things in the right order (sorting) also might need solving