0

Assuming few fields (say under 6/7) whats the best strategy to store multiple language translation for DB records, consider the following two cases.

Say we have a table with the following fields:

id, title, description

Every record will have an English and Arabic translation. So, consider the two cases I am using:

Case 1:

Add two more fields: title_ar and description_ar.

Case 2:

Add a field: language and make a composite key out of id and language.

Considering the complexity of dealing with the composite key (I am using Sequel ORM), I am rethinking whether going with the first approach might be better. Please explain if there are any major drawbacks or advantages for either of the approaches.

Jikku Jose
  • 18,306
  • 11
  • 41
  • 61
  • You might want to ask on [dba.se] or [su] for DB schema advice, as Stack Overflow isn't a "Please explain if there are any major drawbacks or advantages for either of the approaches." site. If you were having problems coding a solution then SO would be a better site to use. – the Tin Man Oct 11 '14 at 23:08

1 Answers1

1

Translator DB Model

This approach would scale even if it's planned to accommodate multiple language translations.

Associations

Sentence has many language translations
Translation belongs to Sentence

Drawbacks of the first approach,

If we were to extend the application to multiple languages then we have to add the corresponding fields to the table

Advantage of the second approach,

As we have id reference specific to the sentence, we can fetch the corresponding description in desired language quicker.

Ananth Srinivasan
  • 136
  • 4
  • Yes, thought about the same. But sadly avoided it as it was emergency and the auto generated admin code would need significant recoding. – Jikku Jose Oct 13 '14 at 01:50