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)

  • 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 '12 at 19:22
  • Nope, the data doesn't need to be searched. See my comment on the accepted answer. – j0ntech Mar 12 '12 at 21:27
  • 1
    the only problem with this (the reason i didnt do it) is if text is really long. – GorillaApe Sep 20 '12 at 14:02

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.)

  • 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 '12 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 '12 at 17:38
  • Unless you have 20 translations. On moble connections data transfer is slow and expensive. – Wyatt Barnett Nov 14 '12 at 14:20

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)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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