mysql one translates table vs multiple translate table

Question

I am trying to design a structure translate(en -> fr) for all system.

my database :

translates(id,table,key_for_search,column_to_translate,lang,translate)

id | table | key_for_search | column_to_translate | lang | translate

1 | posts | 2 | title | en | hello

2 | posts | 2 | title | sp | hola

add translate to new table or when exists is possible.

this way is good ?

Answer 1

thanks for reply.

sorry for my bad english.

I think I talked unclear.

my tables for example:

--
-- Table structure for table `posts`
--

CREATE TABLE IF NOT EXISTS `posts` (
`id` int(11) NOT NULL,
  `title` varchar(100) NOT NULL,
  `content` mediumtext
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;

--
-- Dumping data for table `posts`
--

INSERT INTO `posts` (`id`, `title`, `content`) VALUES
(1, 'hello', 'hello how are you ?');

-- --------------------------------------------------------

--
-- Table structure for table `translates`
--

CREATE TABLE IF NOT EXISTS `translates` (
`id` int(11) NOT NULL,
  `table` varchar(255) NOT NULL,
  `key` int(11) NOT NULL,
  `column` varchar(255) NOT NULL,
  `lang` varchar(3) NOT NULL,
  `translate` text NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;

--
-- Dumping data for table `translates`
--

INSERT INTO `translates` (`id`, `table`, `key`, `column`, `lang`, `translate`) VALUES
(1, 'posts', 1, 'title', 'es', 'Hola'),
(2, 'posts', 1, 'content', 'es', 'Hola, cómo estás?');

--
-- Indexes for dumped tables
--

--
-- Indexes for table `posts`
--
ALTER TABLE `posts`
 ADD PRIMARY KEY (`id`);

--
-- Indexes for table `translates`
--
ALTER TABLE `translates`
 ADD PRIMARY KEY (`id`), ADD KEY `table` (`table`,`key`,`column`,`lang`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `posts`
--
ALTER TABLE `posts`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=2;
--
-- AUTO_INCREMENT for table `translates`
--
ALTER TABLE `translates`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=3;

and my query for get translate

select `column`,`translate` from `translates` where `table` = 'posts' && (`column` = 'title' || `column` = 'content') && `key` = 1 && `lang` = 'es' #Spanish translate

why me using this way ?

because i can in future add new table and I do not need for new design table or new programming

this is a good idea ?

Answer 2

Perhaps a simpler table:

xid -- code that is in the other table
lang -- language of 'xlation' column
xlation -- the translation in `lang` (use utf8 for this column)

xid could be an INT and you simply ask for another entry. Or it could be a short string. Or it could be the English version.

You would need

PRIMARY KEY(xid, lang)

and you might need this to avoid dups:

UNIQUE(lang, xlation)

Now... Think through the dataflow and what INSERTs and SELECTs you will need. You may have more questions.

(Update):

xid | lang | xlation
Hello | en | Hello
Hello | sp | hola
hello how are you ? | en | Hello.  How are you?
hello how are you ? | sp | Hola. ¿Coma estas?