4

We're deciding on how to store translations of some descriptions of database items.

We could go the traditional way and keep a translations table (and a language table and an object_translation linking table) OR we thought it might be better to just have a Description column that contains JSON like the following:

{
     "EN": "This is the translation in English",
     "EE" : "See on kirjeldus eesti keeles"
}

Are there any serious downsides as to why we shouldn't use this? (I haven't seen it being used anywhere else)

Improve this question
3
  • Does the data need to be searchable? If so, are you performing searches directly on the database, or are you delegating that to e.g., Solr or Sphinx?
    – user34530
    Mar 12, 2012 at 19:22
  • Nope, the data doesn't need to be searched. See my comment on the accepted answer.
    – j0ntech
    Mar 12, 2012 at 21:27
  • 1
    the only problem with this (the reason i didnt do it) is if text is really long.
    – GorillaApe
    Sep 20, 2012 at 14:02

3 Answers 3

Reset to default
3

The obvious downside is that if your database doesn't support query over JSON, and especially indexing over JSON, you may have lower performance performing operations other than "fetch this blob of translations" - especially "fetch only the "EE" translations", or "tell me what doesn't have an "EE" translation.

The key advantage of storing JSON is that you can serve it much faster: key lookup, read column, dump to browser. No need to futz with encoding it to JSON on the fly - you get the same advantage that you do serving pre-compressed static content to clients rather than on-the-fly gzip compression.

You can also balance the two: use the structured model with the link table to preserve query capabilities, but store JSON data to minimize encoding and decoding costs when you send.

Ultimately, it depends on what queries are most and least common in your problem space.

(Oh, and don't forget to think about what happens if two languages get uploaded at the same time, if you pack everything into the one column.)

Improve this answer
3
  • Actually we would send the whole description column with a http request and parse it on the client side (it's a mobile app). Also, there most likely won't be more than two languages anyway, so the blob-size wouldn't be a problem.
    – j0ntech
    Mar 11, 2012 at 17:36
  • Sounds like having a JSON column would be a win, then. Like anything, denormalizing your data has performance advantages but also structural costs. Balance them for your needs. :)
    – Daniel Pittman
    Mar 11, 2012 at 17:38
  • Unless you have 20 translations. On moble connections data transfer is slow and expensive.
    – Wyatt Barnett
    Nov 14, 2012 at 14:20
3

I see no reason to store JSON directly in the database. As mentioned in a previous thread you cannot query the database directly and the performance gains are insignificant. Creating JSON programatically from the database will be insignificant compared to the time it takes to actually hit the database and query (whether query for JSON or using standard SQL)

Improve this answer
1

It’s a perfect use of JSON in a database field.

Rolled this strategy out very successfully.

Otherwise you end up contorting your logical data structure to capture languages. And have a shed load of counter tables. The queries and performance become terrible too, especially if row is not present and want to have a default language where desired one not available.

We tended to name the fields with multilingual as a suffix

nameMultiligual {“en-gb”:”Name”, …., default:”en-gb”} hobbiesMultilingual {…}

Use locale codes though.

You can then have a locale mode in your session application and the business layers can take care of pulling the value from the json with session locale, so it’s as easy to use from the presentation layer.

Improve this answer

Not the answer you're looking for? Browse other questions tagged or ask your own question.