Raffaele Rossi Raffaele Rossi - 9 months ago 41
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

Answer Source

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)