Db for dynamic multilingual data structure

Question

I have done this in several different ways. I would like to know if there is a good sample for this.

The system has dynamic content. Thus, not only the amount of data and the data itself is dynamic (count (rows)), but the number of fields is also dynamic. Thus, a blog can contain 10 fields that need to be translated, but the cart item has 5.

What I did was insert an identifier for a row in a table that contains language data for this field. This language table has id, defaultlanguage, plus any number of additional languages. Thus, there is only one language table.

This is great, but I can’t update the views because when multiple links reference the same table, it is not updated (MySQL). Then perhaps there are much better ways to do this.

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

Answer 1

• Entity FieldTypes (identifier, name)
• Entity Fields (ID, Name, FieldType)
• Entity FieldValues (ID, CollectionID, field, value)
• Content object (identifier, fields, data)
• Entity i18n (, , , )

:

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