I have carried this out a couple of various ways right now. Let me determine if there's a great pattern with this.

A method has dynamic content. So not just is the quantity of data and also the data itself dynamic (count(rows)), but the amount of fields can also be dynamic. So your blog might have 10 fields that should be converted, but a shopping cart software item has 5.

What I have been doing is placing an id for any table row which supports the language data for your area. This language table has id, defaultlanguage, plus a variety of additional languages. By doing this there's just one language table.

This really is great, however i can't update sights because when several join references exactly the same table, it isn't updatedable(MySQL). Then possibly you will find far better methods for carrying this out.

It is possible to better design? What are the common design designs used in cases like this?

  • Entity FieldTypes (ID, Title)
  • Entity Fields (ID, Title, FieldType)
  • Entity FieldValues (ID, CollectionID, Area, Value)
  • Entity Content (ID, Fields, Data)
  • Entity i18n(ID, Area, 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