0

Say I have a simple table, named content with the following fields: id, title, html, modified_date

I also have a table called language, with only the fields id and name

What would be the best way to make my content table multilingual? I thought about using 2 unique identifiers, but i'm not sure how to implement that, and will auto_increments still work when i have multiple unique ids?

(Also I don't like losing my single id-field as a unique identifier, cause i have set up a model in php that allows me to easily create objects based on the table-name and the id.)

Jules Colle
  • 11,227
  • 8
  • 60
  • 67
  • Check this one: http://stackoverflow.com/questions/3077305/how-to-use-multilanguage-database-schema-with-orm/4745863#4745863 – Maxime Pacary Mar 22 '11 at 21:01

2 Answers2

0

What about adding to your content table a FK field, language id?

Chris Walton
  • 2,513
  • 3
  • 25
  • 39
  • sure, but i need a way to find the same content in another language. so if i get content 1 with language 2, how would i find the same content in let's language 3? – Jules Colle Mar 22 '11 at 19:58
  • @Jules - how do you identify that the two contents are the same, when they are in different languages? If you are saying language and content are M:N then rather than adding a foreign key; add an associative table with columns content id, language id, both of which are FKs to your existing tables. – Chris Walton Mar 22 '11 at 20:07
  • by the contents are the same I mean, they are each other's translations. maybe i dindn't make that vary clear – Jules Colle Mar 22 '11 at 21:24
0

I would use two tables for this. Using "pages" as an example:

Table: pages
Fields:

  • id
  • title (Title in English, used for referencing)
  • last_modified

Table: page_content
Fields:

  • page_id (Foreign key)
  • language_id (Foreign key. Alternatively, language_code)
  • title
  • content
simshaun
  • 21,263
  • 1
  • 57
  • 73
  • thanks, that looks good. i based my model on your suggestion. i now have a table called `content` (with only language-independent info like `date_created`) and then i have a `contenttranslation` table with `language_id`, `content_id`, `title` and `html` – Jules Colle Mar 22 '11 at 22:17