Schema for a multilanguage database

by

What do you think about having a related translation table for each translatable table?

CREATE TABLE T_PRODUCT (pr_id int, PRICE NUMBER(18, 2))

CREATE TABLE T_PRODUCT_tr (pr_id INT FK, languagecode varchar, pr_name text, pr_descr text)

This way if you have multiple translatable column it would only require a single join to get it + since you are not autogenerating a translationid it may be easier to import items together with their related translations.

The negative side of this is that if you have a complex language fallback mechanism you may need to implement that for each translation table – if you are relying on some stored procedure to do that. If you do that from the app this will probably not be a problem.

Let me know what you think – I am also about to make a decision on this for our next application.
So far we have used your 3rd type.

More Related Contents:

  1. How to design a product table for many kinds of product where each product has many parameters
  2. What is Normalisation (or Normalization)? [closed]
  3. Database design – articles, blog posts, photos, stories
  4. How to version control a record in a database [closed]
  5. Sql – Indirect Foreign Key
  6. Defining multiple foreign keys in one table to many tables
  7. Best practice multi language website
  8. What are the lengths of Location Coordinates, latitude and longitude? [closed]
  9. Best way to implement multi-language/globalization in large .NET project
  10. Are nulls in a relational database okay? [closed]
  11. How should I implement this schema in MongoDB?
  12. Why use multiple columns as primary keys (composite primary key)
  13. Database schema which can support specialized properties
  14. How do you deal with polymorphism in a database?
  15. What’s the best database structure to keep multilingual data? [duplicate]
  16. Nullable Foreign Key bad practice?
  17. How do I design a database to store properties, selecting attributes by synonyms
  18. Composite primary key or not?
  19. Hash Collision – what are the chances? [closed]
  20. How do you determine how far to normalize a database?
  21. Should I have a dedicated primary key field?
  22. What is the best database schema to support values that are only appropriate to specific rows?
  23. Database design for audit logging [closed]
  24. Should I use a single or multiple database setup for a multi-client application? [closed]
  25. Database Design Best Practices [closed]
  26. A column as primary key or two foreign keys as primary key
  27. How to Store Historical Data [closed]
  28. Bill of materials database model
  29. What are design patterns to support custom fields in an application?
  30. mongodb schema design for blogs

Leave a Comment