CODEWITHSUNDEEP
mysqllocalizationdatabase-schemamultilingual
C. Ovidiu
C. Ovidiu

Reputation: 1134

MySQL multilingual table structure

I have a website with 2 items that need to be translated in different languages: articles and products.

For the articles I created two tables:

[articles]
  id PK
  posted_on

[articles_local]
  id PK
  article_id FK(articles.id)
  title
  body
  lang_code

I could have created three tables to also store the language code and id but I thought it was overkill because my application is relatively small.

The problem is with the products. The products have two information that need to be translated, the product name and the product category; all the others are neutral and equal to all languages.

Here is my table structure for the products, which I need help with:

[products]
  id PK
  name // always the same in all languages
  product_code
  weight
  image_url
  category_id FK (category.id)

[category]
  id PK
  cat_name_IT
  cat_name_EN
  cat_nameRU

[products_local]
  id PK
  product_id FK(products.id)
  prod_description
  lang_code

So basically the category table holds the translated category names for the three languages. The categories are 6 in total and won't change any time soon.

The products_local only has the translated description of the product and the product table the information that is the same for each language.

I think that my products structure is a bit messed up but I can't figure why and also how can I improve. I think this based on the articles structure which to me seems more 'right' and clean.

Is there any way I can improve this table structure ?

I don't know if it matters, but in my php I extract the articles and products based on the folder name, it, en, ru: 

$lang = // get folder name from URI
$sql = "SELECT * FROM articles_local WHERE lang_code = :lang";
// bind $lang to :lang

Upvotes: 0

Views: 710

Answers (1)

Remy
Remy

Reputation: 12713

It's ok I think.

If you only need the translations in two or three fixed languages, I would just add the translation fileds into the normal products table.

Upvotes: 1

Related Questions