Please Tell me if i am making any mistake while designing this database Using MYSQL

Question

my basic requirement is I have some languages in the languages table. then I am adding my words and their translations in different languages. So to store that data I hope I am right the situation is one-to-many relationships. please have a look at the image.

am I using a correct design? Or it could be better than this? any suggestions?

What do you plan on storing in the words.word field? Is that where the original term (English, I presume) is to be stored? — chabzjo, May 11 '18 at 22:45
Actually, a "word:" will be related to a single language, so a language id should be in the word table. Then a table that links that word to it's equivalent in another language, called "translations". It would have two keys pointing to the "words" table. — Sloan Thrasher, May 11 '18 at 23:04

score 1 · Answer 1 · answered May 11 '18 at 22:35

1

The following tables could be used:

language
- id (key)
- short text
term
- language id (key)
- id (key)
- text
translation
- language_id1 (key)
- term_id1 (key)
- language_id2 (key)
- term_id2 (key)

answered May 11 '18 at 22:35

Xypron

2,215
1
12
24

Is it really better solution? – Rakesh Kohali May 11 '18 at 22:47
@rakeshkohali no – Strawberry May 11 '18 at 23:08
@Strawberry please explain me how I can make this better. Thanks – Rakesh Kohali May 11 '18 at 23:13

chabzjo · Answer 2 · 2018-05-12T16:26:57.817

Here's my suggestion. I normally prepend my IDs by the table name to make it easier to read. Obviously, you can use the naming standards you prefer.

Language
- LanguageID
- Description

Term
- TermID

Translation
- LanguageID
- TermID
- Text

As for the key for the Translation table, it depends on how many translations you will allow per language.

If you allow multiple, then add a TranslationID.

If you want to force just one, use a concatenated key of LanguageID and TermID. See How to properly create composite primary keys - MYSQL

I know it looks odd to have a table with just a key in it, but this structure has multiple advantages.

It will allow you to query only one field when doing a search, regardless of whether you're translating FROM English or TO English.
If one day, you need to translate between languages other than English, you're already set.
You can translate the same word more than once, depending on its different meanings.

Here is an example of how to use it

Language
1 English
2 French
3 Spanish

Term
1

Translation
1 1 Dog
1 1 Hound  -- (if this is allowed, add a TranslationID)
2 1 Chien
3 1 Perro

Every translation a -> b is indicates also a translation b -> a. So why would you want translation texts in a different table than source texts? — Xypron, May 12 '18 at 08:28
@Xypron: The translation texts are all in the Translation table, just like for your suggestion. — chabzjo, May 12 '18 at 16:22

Please Tell me if i am making any mistake while designing this database Using MYSQL

2 Answers2