title: localization of dynamic data
body: <p>I'm looking for advice on best practices for localizing data stored in the database.  I'm working on a web application in which all of the static text is localized using files.  We have several options the administrator can configure using the UI which are stored in the database and need to localized these values.</p>
  
  <p>We have come up with a couple of possible ideas.  What are your thoughts on these solutions?  Is there a better option altogether or even a standard best-practice?</p>
  
  <p><strong>Per Field Specialized Localization</strong></p>
  
  <p>This is the solution proposed for <a href="https://stackoverflow.com/questions/929410/what-is-best-practices-for-multilanguage-database-design">best practices for multilanguage database design</a>.  We would create a separate table for each localized field.  For example, suppose we had the table <code>colors</code> with <code>color_id</code>, <code>color_name</code> and <code>color_description</code> columns, we might break it out into a <code>color</code> table with the non-localized data and a <code>color_translations</code> table <code>color_id</code>, <code>locale</code>, <code>color_name</code> and <code>color_description</code> fields.</p>
  
  <p>However, our customers often send the localization files to a third party to do the translation which becomes tricky.</p>
  
  <p><strong>Single Table Localization</strong></p>
  
  <p>Another option would be to create a single table to represent all of the database localization:</p>
  
  <pre><code>CREATE TABLE localized_text
  (
      key          VARCHAR(256) NOT NULL,
      locale       CHAR(5) NOT NULL,
      value        VARCHAR(256),
      PRIMARY KEY (key, locale)
  );
  </code></pre>
  
  <p>This would be easier to export for off-site localization but adds a level of indirection.</p>