How to store word translations (one to many)?-CodePudding

I have a LocalTexts table:

id(pk, autoincrement) code |   lang | name
1                      1        ln     Aquastors
2                      1        en     Question

Also the table RateClassificator:

id (pk, autoincrement) idLocalText (fk)
1                      1

So, when I want to get all translates for row: RateClassificator.id I do:

SELECT * FROM RateClassificator 
ON RateClassificator.idLocalTex = LocalTexts.id WHERE LocalTexts.lang == 'ln' 
AND RateClassificator.code = 1;

I confues about relation, is it one to many? because one word can have some translates.

CodePudding user response：

It is one to many indeed, A RateClassification can only have one LocalText and a LocalText can be assigned to more than one RateClassification.

If for example RateClassification id 2 also has LocalText 1 then 2 RateClassifications have the same LocalText.

I believe what is missing in your querie is a NATURAL JOIN between the two tables.

CodePudding user response：

I would probably have 2 tables, one with English word and another table with translations.

tbl_en
ID(auto increment)         name
         1               Question

tbl_trans
ID(auto increment)          en_ID(foreign key)       name          lang
       1                           1               Aquastors        ln
       2                           1               Preguntas        es

This is one to many relationship.