Hi all:

I'm trying to create a multitenant and multilanguage database but I'm not sure which would be the proper method to relate languages with the main​/root entity.

The following examples are the two methods that I have raised, but both have advantages and disadvantages.

1. Languages are relate to main entity ("company") through a table ("company_language") from which hang translations of all the translatable contents (including main entity and "child" entities).

Click image for larger version. 

Name:	multitenant_trans_subordinated.jpg 
Views:	3 
Size:	36.3 KB 
ID:	15225

  • Cons:

    • Maybe a slightly confusing maintainability considering that each translatable content is not directly related to language.
    • This complicates the logic of validation/creation, especially to create representations of the main entity ("company"), since to create a company and its translations is needed a language that has been previously assigned to the company, and in turn, to assign a language to a company it must exist.
  • Pros:

    • Best integrity, I think. No translatable content may exist if not assign previously a language to the main entity.

2. The translatable contents hang from "language" table, leaving the table that relates the languages ​​with the main entity ("company_language") only as a collector/summary table.

Click image for larger version. 

Name:	multitenant_trans_support.jpg 
Views:	3 
Size:	36.2 KB 
ID:	15226

  • Cons:

    • Worst integrity. It may be the case there of a translatable content that does not correspond with any of the languages ​​configured for the main entity, since "company_language" only acts as a summary table.
  • Pros:

    • Better maintainability/readability and less queries.
    • Treat "{entitiy}" and "{entity}_translation" as a single entity in all cases, simplifying interactions.

What would be the ideal choice to work with? Sure I leave some other pros and cons.

Thank you very much for your time!