i am planning to build a cakephp application that can be flexibly translated to many languages(web interface and table content are need to be translated). my problem is when a new languages is added to the system , all the table must provide a translation for each field in the form.
here is my database schema.
CREATE TABLE fields (
id int(11) NOT NULL AUTO_INCREMENT,
table_id int(11) DEFAULT NULL,
name varchar(50) DEFAULT NULL,
language_id int(11) DEFAULT '1',
label varchar(255) DEFAULT NULL,
type varchar(20) DEFAULT NULL,
required tinyint(1) DEFAULT NULL,
properties varchar(100) DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=MyISAM AUTO_INCREMENT=13 DEFAULT CHARSET=latin1;
#
Structure for the languages table :
#
CREATE TABLE languages (
id int(11) NOT NULL AUTO_INCREMENT,
name varchar(255) NOT NULL,
state tinyint(1) NOT NULL,
label varchar(100) DEFAULT NULL,
default tinyint(1) DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=MyISAM AUTO_INCREMENT=14 DEFAULT CHARSET=latin1;
#
Structure for the tables table :
#
CREATE TABLE tables (
id int(11) NOT NULL AUTO_INCREMENT,
name varchar(50) DEFAULT NULL,
description varchar(100) DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;
#
Structure for the translations table :
#
CREATE TABLE translations (
id int(11) NOT NULL AUTO_INCREMENT,
field_id varchar(50) NOT NULL,
value varchar(255) NOT NULL,
record_id int(11) DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
#
Data for the fields table (LIMIT 0,500)
#
INSERT INTO fields (id, table_id, name, language_id, label, type, required, properties) VALUES
(1,1,'name',1,'Nombre','text',NULL,NULL),
(2,1,'name',2,'Name','text',NULL,NULL),
(3,2,'name',1,'Name','text',NULL,NULL),
(4,2,'country_id',1,'Country','select',NULL,NULL),
(5,2,'address',1,'Address','rich',NULL,NULL),
(6,2,'tel',1,'Telephone','text',NULL,NULL),
(7,2,'fax',1,'Fax','text',NULL,NULL),
(8,2,'website',1,'Web site','text',NULL,NULL),
(9,2,'email',1,'Email','text',NULL,NULL),
(10,2,'picture',1,'Picture','upload',NULL,NULL),
(11,2,'to',1,'To country','select',NULL,NULL),
(12,2,'latlng',1,'Latitude & Longitud','text',NULL,NULL);
COMMIT;
#
Data for the languages table (LIMIT 0,500)
#
INSERT INTO languages (id, name, state, label, default) VALUES
(1,'En',0,'English',NULL),
(3,'Fr',0,'France',NULL),
(2,'Es',1,'Espanol',NULL);
COMMIT;
#
Data for the tables table (LIMIT 0,500)
#
INSERT INTO tables (id, name, description) VALUES
(1,'countries','Country'),
(2,'embassies','Embassy');
when i wanna add a new record to the for instance Embassy table then the formhelper need to have many field named "Name" according to the number of languages activated in the language table.
please help .
thanks you