Multiple languages terms tables , how should i organize them?

[mindsoul]

Multiple languages terms tables , how should i organize them?

 

So i must build an application for a restaurant,

where they can add new terms (like "spaghetti in bolognese style") in multiple languages 5-6-7 , where they can build the daily menu by selecting the plates, when they are ready with the selection of the plates 1 one particular language, the menu with 7-10 plates must be automaticly ready in other languages too.

so i have the category "meats" for example and the plate "duck breast" how do i organize that? "duck breast" translation must be available in 5-6 or more languages.

 

I must be able also to add later other languages too. I have some ideea but i think i'm wrong.

Thanks

 

 

[br0ken]

You would have a category table and then an item table. The item table would contain price and an id and but then you would have another table with the items in different language. Each record in this table would reference the ID in the first item table that it belongs to.

 

This way you would select an item from the item table and then join that with all the entries in the language table.

 

Does this make sense?

[mindsoul]

i did not think like that. i thought:

1 table for categories (id, name, (values:meat, fish, sweets and so on) lang_id(related to id from table lang,unique_id) categories shoud be also in multiple languages);

1 table for languages like (id, lang (values: en/it/de....))

1 table terms (id, category_unique_id, name, lang_id)

 

what about that?

 

 

[br0ken]

If that way works in your head then do it like that. To me the way I described is logically correct and would therefore allow me to understand the system the best I could. If you find your way easier and more logical then I would go for yours as using a method that you don't fully understand or agree with will just slow you down.

[mindsoul]

i think the way you see it is better with terms in one language in one table and the translations of the other in another table

[fenway]

i think the way you see it is better with terms in one language in one table and the translations of the other in another table

This will get out of control VERY fast if you have 20 tables with 20 fields each... easier to dupe the records within the table.

[mindsoul]

so i have:

• 1 table categories (category_id, category_name,category_unique_id, category_lang)
  like:
  1....................meat..............1...............en
  2....................carne..............1...............it
  3....................fleisch..............1...............de
  4....................fish..................2................en
  5.....................pesce...............2................it
  ..................................................................
   
  
• 2 table terms in one language (term_id, term_name, cat_id)
  1...................Saltmibocca alla Romana...........2
  ..................................................................
   
   
  
• 3 table terms_translations(term_translation_id, translation_name,term_lang, term_id)
   
  1...................Schntzel mit schinken u.Salbei....de.............1
  ....................................................................................
   
   
  

 

where do i am wrong?

so in one table i have the categories in diferent languages, in other table i have all the terms in one language let's say italian, in the last table i have all the terms translated related to the default table term

[fenway]

I suppose if you dupe every table, that would work too... but now you need joins.

[mindsoul]

so i came with the fallowing solution but still not ok, i've read a lot about multilingual sistem etc.. db design

this are my tables

CREATE TABLE IF NOT EXISTS `menu_category` (
  `category_id` int(2) unsigned NOT NULL auto_increment,
  `category_name` varchar(250) NOT NULL,
  PRIMARY KEY  (`category_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=20 ;


CREATE TABLE IF NOT EXISTS `menu_ingredients` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `lang_id` tinyint(2) NOT NULL,
  `name` varchar(250) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;


CREATE TABLE IF NOT EXISTS `menu_lang` (
  `lang_id` tinyint(2) unsigned NOT NULL auto_increment,
  `lang_name` varchar(2) NOT NULL,
  PRIMARY KEY  (`lang_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=9 ;


CREATE TABLE IF NOT EXISTS `menu_terms` (
  `term_id` int(10) unsigned NOT NULL auto_increment,
  `term_category` tinyint(3) NOT NULL,
  `term_name` varchar(250) NOT NULL,
  PRIMARY KEY  (`term_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=10 ;


CREATE TABLE IF NOT EXISTS `menu_terms_translation` (
  `term_translation_id` int(10) NOT NULL auto_increment,
  `term_translation_name` varchar(250) NOT NULL,
  `term_translation_lang` int(2) NOT NULL,
  `term_translation_terms_id` int(10) NOT NULL,
  PRIMARY KEY  (`term_translation_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

but still not sure if i am right, lets say the menu_terms is the default language term list where i put the menu plates in 1 particular language, for example italian and in the table menu_terms_translation i'll put the translations, the problem in my design it rise if my default language app change from italian to some other language, so how can i design the db, somehow that i can change the default language of the app ?

i thought that is better that i have one table of terms for every language or new language that I'll add in the future ...what do you think?

or i can have one big table with terms in 10 languages where i must have a column (term_unique_id)- that uniquely  mark one particular term-row which help me to make the relation between 1 row and the translation of that row like:

CREATE TABLE IF NOT EXISTS `menu_terms` (
  `term_id` int(10) unsigned NOT NULL auto_increment,
  `term_category` tinyint(3) NOT NULL,
  `term_name` varchar(250) NOT NULL,
  `term_unique_id` int(10) unsigned NOT NULL auto_increment,
  `term_lang`int(2) NOT NULL,
  PRIMARY KEY  (`term_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=10 ;

so i can have line like:

1...  1..... caviale... 1... 1

2...  1.... insalata di mare... 2... 1

3...  1....caviar....1....3

 

so from here the value of my field term_unique_id on the third line "1" tells me  that's the same term on the first line only that it is in another language(english). Is this approach ok?

 

[fenway]

Yes... the really the only "valid" approach, IMHO.