What are best practices for multi-language database design? [closed]

by

What we do, is to create two tables for each multilingual object.

E.g. the first table contains only language-neutral data (primary key, etc.) and the second table contains one record per language, containing the localized data plus the ISO code of the language.

In some cases we add a DefaultLanguage field, so that we can fall-back to that language if no localized data is available for a specified language.

Example:

Table "Product":
----------------
ID                 : int
<any other language-neutral fields>


Table "ProductTranslations"
---------------------------
ID                 : int      (foreign key referencing the Product)
Language           : varchar  (e.g. "en-US", "de-CH")
IsDefault          : bit
ProductDescription : nvarchar
<any other localized data>

With this approach, you can handle as many languages as needed (without having to add additional fields for each new language).

Update (2014-12-14): please have a look at this answer, for some additional information about the implementation used to load multilingual data into an application.

More Related Contents:

  1. How to implement a many-to-many relationship in PostgreSQL?
  2. How to create multiple one to one’s
  3. Strings as Primary Keys in SQL Database [closed]
  4. Are soft deletes a good idea? [duplicate]
  5. Database Design for Revisions?
  6. A beginner’s guide to SQL database design [closed]
  7. Physical vs. logical (hard vs. soft) delete of database record? [closed]
  8. Storing money in a decimal column – what precision and scale? [closed]
  9. single fixed table with multiple columns vs flexible abstract tables
  10. Primary key or Unique index?
  11. How to design a database for User Defined Fields?
  12. Composite Primary key vs additional “ID” column?
  13. Database EAV Pros/Cons and Alternatives
  14. Max length for client ip address [duplicate]
  15. Name database design notation you prefer and why?
  16. MySQL – how to front pad zip code with “0”?
  17. Use email address as primary key?
  18. Composite Primary Keys : Good or Bad?
  19. SQL ON DELETE CASCADE, Which Way Does the Deletion Occur?
  20. SQL: Using NULL values vs. default values
  21. What does ON [PRIMARY] mean?
  22. What is the best way to implement soft deletion?
  23. Relational Data Model for Double-Entry Accounting
  24. Temporal database design, with a twist (live vs draft rows)
  25. Historical / auditable database
  26. Single or multiple databases
  27. Insert into … values ( SELECT … FROM … )
  28. What is the difference between Left, Right, Outer and Inner Joins? [duplicate]
  29. Serial numbers per group of rows for compound key
  30. How do you interpret a query’s explain plan?

Leave a Comment