What’s the best database structure to keep multilingual data? [duplicate]

by

Similar to method 3:

[languages]
id (int PK)
code (varchar)

[products]
id (int PK)
neutral_fields (mixed)

[products_t]
id (int FK)
language (int FK)
translated_fields (mixed)
PRIMARY KEY: id,language

So for each table, make another table (in my case with “_t” suffix) which holds the translated fields.
When you SELECT * FROM products, simply ... LEFT JOIN products_t ON products_t.id = products.id AND products_t.language = CURRENT_LANGUAGE.

Not that hard, and keeps you free from headaches.

More Related Contents:

  1. Error Code: 2013. Lost connection to MySQL server during query
  2. Throw an error preventing a table update in a MySQL trigger
  3. Do different databases use different name quote?
  4. Binary Data in MySQL [closed]
  5. Using an Alias in SQL Calculations
  6. Can a table field contain a hyphen?
  7. Adding a leading zero to some values in column in MySQL
  8. Auto Generate Database Diagram MySQL [closed]
  9. View’s SELECT contains a subquery in the FROM clause
  10. MySQL : Multiple row as comma separated single row
  11. How to insert data to MySQL with auto-incremented column(field)?
  12. How to design a multi tenant mysql database
  13. SQL Order By Count
  14. Replace Into Query Syntax
  15. What resources exist for Database performance-tuning? [closed]
  16. How to connect to MySQL from the command line
  17. Remote connect to clearDB heroku database
  18. How to produce “phantom read” in REPEATABLE READ? (MySQL)
  19. Laravel join with 3 Tables
  20. MySQL foreign key to allow NULL?
  21. How to schedule a stored procedure in MySQL
  22. What’s the best way to migrate a Django DB from SQLite to MySQL?
  23. How to get multiple records against one record based on relation?
  24. What are the differences between the BLOB and TEXT datatypes in MySQL?
  25. What is the best way to store media files on a database?
  26. Native JSON support in MYSQL 5.7 : what are the pros and cons of JSON data type in MYSQL?
  27. Foreign key constraints: When to use ON UPDATE and ON DELETE
  28. How to use a user variables in MySQL LIKE clause?
  29. remove all numeric characters from column mysql
  30. Failed to connect to mysql at 127.0.0.1:3306 with user root access denied for user ‘root’@’localhost'(using password:YES)

Leave a Comment