Need help for multilingual

[dinor]

Hello,

 

This is my 1st post here so excuse me if I brake any rule...

I am new in php developing and need some help.

 

So... I have these tables:

CREATE TABLE languages (
  code Char(2)NOT NULL,
  name Varchar(20) NOT NULL,
  name_local Varchar(20) NOT NULL,
  PRIMARY KEY (code)
);

INSERT INTO `languages` VALUES 
('en', 'English', 'English'),
('el', 'Greek', 'Ελληνικά'),
('ar', 'Arabic', 'العربية'),
('fr', 'French', 'français'),
('ru', 'Russian', 'русский язык'),
('es', 'Spanish', 'español'),
('hi', 'Hindi', 'हिन्दी, हिंदी'),
('de', 'German', 'Deutsch'),
('it', 'Italian', 'Italiano'),
('sq', 'Albanian', 'gjuha shqipe'),
('nl', 'Dutch', 'Nederlands'),
('pt', 'Portuguese', 'português'),
('zh', 'Chinese', '中文'),
('tr', 'Turkish', 'Türkçe');

CREATE TABLE db_category (
catid Int NOT NULL AUTO_INCREMENT,
PRIMARY KEY (catid)
);

CREATE TABLE db_category_translation (
catid Int NOT NULL,
code Char(2) NOT NULL,
name Text NOT NULL,
PRIMARY KEY (catid, code),
FOREIGN KEY (catid) REFERENCES db_category(catid),
FOREIGN KEY (code) REFERENCES languages(code)
);

 

What I want to do is when i insert a category via php, to be automatic translated (by google translate) and saved when submit.

Each category should have a unique id and that is why I created the db_category table to link with eachother.

 

example of db_category_translation table:

('1', 'en', 'Category1 in English'),

('1', 'gr', 'Κατηγορία1 στα Ελληνικά'),

('2', 'en', 'Category2 in English'),

('2', 'gr', 'Κατηγορία2 στα Ελληνικά')... etc but for more languages (I only speak these 2).

 

My questions are:

is this an acceptable way to design my database for that purpose?

Since I am new in php dev, can someone tell me how to start in order to do what i want?

[The Little Guy]

That is the way I would do it, but I do see that you are duplicating data, you already have "en" and "gr" in the languages table, so you don't need them in the translation table.

 

Languages should have an auto_number associated with it, then you can join that number on the proper language.