0

I need to design a db model for a backend module where user can translate page content into multiple languages. The things that will be translated are basic words, phrases, link names, titles, field names, field values. They should also be grouped so i can find them by group name. For example if there is a select field on page with different colors as options then i should be able to select all of them by group name.

So here is what i have at the moment:

lang
+----+---------+
| id |  name   |
+----+---------+
|  1 | english |
|  2 | german  |
+----+---------+

lang_entity
+----+------------+-------------+-------+-------+
| id |   module   |    group    | name  | order |
+----+------------+-------------+-------+-------+
|  1 | general    |             | hello |     0 |
|  2 | accounting | colorSelect | one   |     1 |
|  3 | accounting | colorSelect | two   |     2 |
|  4 | accounting | colorSelect | three |     3 |
+----+------------+-------------+-------+-------+

lang_entity_translation
+----+---------+----------------+-------------+
| id | lang_id | lang_entity_id | translation |
+----+---------+----------------+-------------+
|  1 |       1 |              1 | Hello       |
|  2 |       2 |              1 | Guten tag   |
|  3 |       1 |              2 | One         |
|  4 |       2 |              2 | Ein         |
|  5 |       1 |              3 | Two         |
|  6 |       2 |              3 | Zwei        |
|  7 |       1 |              4 | Three       |
|  8 |       2 |              4 | Drei        |
+----+---------+----------------+-------------+

So lang table holds different languages.

Table lang_entity has entities that can be translated for different languages. Module row is just to group them by page modules in the backend translating module. Also this gives me possiblity to have entities with same name for different modules. Group as mentioned is needed for selects and maybe some other places where multiple values are going to be used. This also gives me an option to allow user to add and order entities in one group.

And table lang_entity_translation holds the translations for each entity in each language.

So my question is are visible flaws in this kind of a design? Would you reccomend something different?

Also a bonus question: I really dont like the lang_entity table name, do you have a better idea of a table name that would hold all the words/phrases that are translated? :)

Edit: similar, but not a duplicate. The linked question is about translating dynamic products and having a seperate table for each translated type. Im talking about translating whole page content, including groups in a single table.

user1985273
  • 1,817
  • 15
  • 50
  • 85
  • Please tag only the RDBMS which your are using. – Wernfried Domscheit Jan 16 '18 at 10:16
  • Possible duplicate of [What are best practices for multi-language database design?](https://stackoverflow.com/questions/929410/what-are-best-practices-for-multi-language-database-design) – JGH Jan 16 '18 at 12:45

2 Answers2

1

We had similar situation. This was 7 years before. We had different column for different language. Like for name we had Name_Eng,Name_Ger,Name_Spa .We had 7-10 language.

We had common id for name for all language.

Based on the Language selection from UI we passed the language code to Back end In the Stored proc it was appended to the column Name Example, we will be passing "Eng" if English is selected and we form the column name as Name_Eng and fetch the data. we were using dynamic query.

NishanthMV
  • 11
  • 5
  • 1
    I don't think this is a smart design. You enforce dynamic SQL where it could be avoided. You have to change the table structure every time you add/remove a new language. – Wernfried Domscheit Jan 16 '18 at 10:31
  • I think its a acceptable solution if you have a set of languages that you can guarantee will never change. But for me users will be able to add/remove languages so it wouldn't work. – user1985273 Jan 16 '18 at 12:15
1

I don't understand the order column of lang_entity, but then I probably don't need to.

The setup looks sane, but make sure you add foreign key constraints from lang_entity_translation to language and lang_entity.

As for naming, I would call the table phrase or translatable.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
  • It was a bit of a bad example because i had 0,1,2,3 after each other. But the point is that each group has its own order so it gives an user possiblity to edit it or maybe add something in the middle. For example colorSelect group values will go into a dropdown select field and order determines the order of them in that field. If an element is not in a group it has either emtpy or 0 value like the first one. – user1985273 Jan 16 '18 at 12:12
  • Regarding naming i like the word translatable you suggested instead of lang_entity. But would you name lang_entity_translation just translation or translatable_translation? Im leaning toward just naming them translatable and translation. – user1985273 Jan 16 '18 at 12:13