db design pattern for a dynamic multilingual data structure

I've done this a few different ways by now. I'd like to know if there i开发者_如何学JAVAs a good pattern for this.

A system has dynamic content. So not only is the amount of data and the data itself dynamic (count(rows)), but the number of fields is also dynamic. So a blog may have 10 fields that need to be translated, but a shopping cart item has 5.

What I've been doing is inserting an id for a table row which holds the language data for that field. This language table has id, defaultlanguage, plus any number of additional languages. This way there is only one language table.

This is nifty, but I can't update views because when more than one join references the same table, it's not updatedable(MySQL). Then perhaps there are much better ways of doing this.

Is there a better design? What are some of the common design patterns used in this situation?

---

• Entity FieldTypes (ID, Name)
• Entity Fields (ID, Name, FieldType)
• Entity FieldValues (ID, CollectionID, Field, Value)
• Entity Content (ID, Fields, Data)
• Entity i18n(ID, Field, LanguageID, Value)

Example:

insert into FieldTypes('S', 'string');
insert into FieldTypes('DT', 'date/time');

insert into Fields(1, 'Author', 'S');
insert into Fields(2, 'Created', 'D');

insert into i18n(1, 1, 'en', 'Author');
insert into i18n(2, 1, 'ru', 'Автор');
insert into i18n(3, 2, 'en', 'Created');
insert into i18n(4, 2, 'ru', 'Создано');

insert into Content(1, 2, 'Test data');
insert into FieldValues(3, 2, 1, 'Tester');
insert into FieldValues(4, 2, 2, '2011-03-20T12:20:00');

/* Display content fields in the user language */ 
select c.ID, f.ID, i.Value as FieldName, fv.Value as FieldValue
from Content c, FieldValues fv, Fields f, i18n i
where c.Fields = fv.CollectionID
  and fv.Field = i.Field
  and i.LanguageID = :UserLanguage
  and c.ID = :ContentID