Raffaele Rossi Raffaele Rossi - 7 months ago 36
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?


My website has a table in a database called
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
is connected to a word, the field
is a number that indicates the translation (01 = english, 02 = italian, 03 = french) and the
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
to unique


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)