Raffaele Rossi Raffaele Rossi - 19 days ago 7
MySQL Question

Primary key on isolated table

I cannot really find a solution to my problem and the question is: do I really have to create a primary key here?




Details

My website has a table in a database called
translations
in which I put each single sentence/word with different translations. I have this structure:

id | lang | text
-------------------
01 | 01 | hello
01 | 02 | ciao
01 | 03 | salut
02 | 01 | surname
02 | 02 | cognome
02 | 03 | nom


The field
id
is connected to a word, the field
lang
is a number that indicates the translation (01 = english, 02 = italian, 03 = french) and the
text
is the translation of that word.

In this case, if I need (for example) the word hello I know that it has id = 1 and then I can choose the language.

Is this a good way to create this kind of table? I am not sure but I find this easy because I can call:

SELECT text FROM tablename WHERE id = ? AND lang = ?


You can see the ? because I bind params with PDO. The 'lang' is a constant retrieved form a cookie (01,02,03).

Important note: here I have no primary keys, I have only set
id
and
lang
to unique

Answer

If you want to keep your table structure and you have just one translation per language for each word, then you can use id + lang as primary key

CREATE TABLE translations
(
   id int,
   lang int,
   text varchar(150) NOT NULL,
   PRIMARY KEY (id, lang) 
);
Comments