How to link a table to a field a in MySQL server

Question

I have this data from a xml file:

<?xml version="1.0" encoding="utf-8" ?>
<words>
    <id>...</id>
    <word>...</word>
    <meaning>...</meaning>
    <translation>
        <ES>...</ES>
        <PT>...</PT>
    </translation>
</words>

This forms the table named "words", which has four fields ("id","word","meaning" and "translation"). On the other hand, the "translation" field can hold several languages like ES,PT,EN,JA,KO,etc... So I create a table ("words.translation", one field is "id" and the others ones are languages ids like "ES","PT",...).

I'm sorry for this newby question, but I'd like to know a couple of things about this one-to-many relationship.

1. How to join (or link?) this two tables in MySQL?
2. What information does the "translation" field in the "words" table has to store?
3. How is the sql query to get all the word information (JOIN syntax used?)

Thanks for your patience.

Answer 1

Hi Nek, I'll try to answer ...

1. How to join (or link?) this two tables in MySQL?

You can link the two tables by declaring a foreign key constraint between the 'word_id' in the TRANSLATION table and the 'id' field in in the WORD table.

1. What information does the "translation" field in the "words" table has to store?

since there are many translations and only one 'original' word in the design you propose, the link will be from translation (many) to word (one), the link is the 'word_id' I mentioned under 1.
Please note that a different design with only one single table that contains 'word,meaning,language' and a meaning_id to self-join translations, might be better for words with multiple translations/meanings.

1. How is the sql query to get all the word information (JOIN syntax used?)

assuming two tables, word and translation, the syntax would be

select w.word, w.meaning, t.language, t.word 
  from WORD as w
  join TRANSLATION as t 
    on (w.id = t.word_id)

---

caveat: your design assumes that there will always be a on to one translation from one language to another. Un?-fortunately that is not true.

Each word has a set of co-meanings that differ from language to language, your meaning field implies that you are aware of this.

---

Table WORD: id | word | meaning
    PK on word_id

Table TRANSLATION word_id | language | word
    PK on (word_id,language), FK word_id -> WORD

Answer 2

an alternate Design, based on our discussion in the comments to my previous answer:

• Table WORD: (WORD_ID | WORD | LANGUAGE)
        PK on (WORD_ID)

• Table MEANING: (MEANING_ID | MEANING)
        PK on MEANING_ID

• Table LINK: (WORD_ID | MEANING_ID)
        PK on (WORD_ID,MEANING_ID)
  Desciption This table is a many-to-many relationship. One word con have multiple meanings (in the same language) and one meaning can have multiple words (in one or more languages)

---

Please note that the 'same' word in different languages no longer carries the same word_id