I need advice on table setup for dictionary tables

[Mihail]

Hello !
I need a quick start info.
Say I wish to define a dictionary.
The basic language is... Romanian language.
So, I have a table: tblRomanian(ID_ RomanianWord,Ro manianWord, ExplanationInRo manian)
Now I need the equivalent explanations in English, French, German ...
I can see this approaches:
1) I can design more fields in tblRomanian:
tblRomanian(ID_ Word,Word, ExplanationInRo manian, ExplanationInEn glish, ExplanationInFr ench, ExplanationInGe rman, ...)
2) I can design one table for each foreign language:
tblEnglish(ID_E nglish, ID_RomanianWord , ExplanationInEn glish)
tblFrench(..... ............... ............... ............... )
and so on
and also I can imagine other approaches.
All this approaches have a 1 to 1 relationship, so, all of this can be reduced (I think) to first approach I write.

The main problem is to design a query (qExplanation) to do:

1) for a certain language (other than Romanian one) (say English)
     - so I need to be able to select desired language
2) for a certain Romanian word
     - so I need to be able to select the Romanian word
3) If the [B]ExplanationInEnglish [/B]exist in database then
     SHOW [B]ExplanationInEnglish[/B]
   Else
     SHOW [B]ExplanationInRomanian[/B]

Can you point me to the right direction ? How to design the table(s) in order to be able to design the query ?

Hope I explain well my problem. Thank you !

P.S.
I am not able to join again at this forum. I can't resubscribe. And I am very sad :((

[zmbd]

My approach:
I would have one table for each language.
There would be a table that links these together... one entry per the main language word per other language related word... so if you had Romain, English, French, Spanish.... you'd have 3 entries (R1-E; R1-F; R1-S; etc...Where R1-* is the id of a word in the Romanian language to the related word) Indexed on these two fields as compound primary key... even in a large database that should work fairly fast.

[Mihail]

Thank you for answer me, ZMBD.
Note please that my skill in databases is a little bit past zero. Also my English.
From your post I think that you misunderstand (unless I misunderstand the English). My goal is not to translate from Romanian into other language (or from other language to Romanian).
Let's try to explain better (if I can using my poor English):

I try to define a kind of "natural language" for G-code (for CNC programming).
So I have a G instruction:
G00 (a word in main language)
The explanation in Romanian language is "Avans rapid"
In English is "Rapid movement" and in French is "Rapid traverse"

So, related to G00 word I need a query to do:
If Romanian language is selected the query must return "Avans rapid" (the explanation in Romanian language).
If French is selected the query must return "Rapid traverse"

Because I am a Rumanian guy, I can define the explanation in Rumanian language for every G word.
So the query will return all the time an explanation if the selected language is Rumanian.

But I can't explain every G word in English or in French (at this moment).
The G01 word means "Avans de lucru" in Rumanian language, but I don't know enough English to define G01 for English users (it means "working speed").
So, if I select the English language, and the explanation is not available (in English), the query must return the explanation in Rumanian language.

Thank you again for the answer, ZMBD.

[zmbd]

Between the two posts, what I said still stands.
-
If you use one table, then every time you need to add a language you will have to go back into the table, add the fields, modify queries, macros, vba, etc... to handle the new fields.

-
If you use two or more tables then adding an additional language should be a matter of entering the new records... if you've got everything setup correctly, no need to go into anything.

So take the following concept:
You have G-codes and explanations for all these in Romanian, yes?

I'll be nice... one method of implementation:
Tbl_romanian:
[romanian_pk]
[romanian_Gcode]
[romanian_word]
[romanian_descri ption]

Tbl_languages:
[languages_pk]
[languages_name]

Tbl_translate:
{{ [FK_romanian] 1:m w/tbl_romanian }PK 1:3}
{{ [FK_languages] 1:m w/tbl_languages } PK 2:3}
{{ [translate_word] }PK 3:3}
[translate_descr iption]

Here’s the concept:
Enter everything you have in the Romanian language into Tbl_romanian. Easy enough I hope!

Tbl_languages: so if you have English now, but French later, and Spanish yet much later then you only need to add that language to this table and then add the related stuff in Tbl_translate.

Tbl_translate: This is where you will relate the G-Code/Romanian to the other languages. Note the "{{}}" fields. Here I've opted for a compound primary key. This will prevent duplicating a Romanian word against the same word in the same language. You can also do this with an additional index against these fields and instead use a synthetic key for the primary key in this table... I've done both, depends on what I'm after. If there's alot of code or alot of queries then often I'll add the synthetic key and find it once for my builds against the other index.

One way to use this:
Query:
tbl_romanian
Tbl_translate
Left join against the romanian_pk/fk
pull fields down with information of interest
filter on G-Code
This should return all of the records from tbl_romanian that have the G-Code and any matching records from Tbl_translate.
if the language code of interest is not among the records then you would return the Romanian information... and so forth

[Mihail]

That must be the logic.
Thank you !!

I'll start with your advices in my mind.
And thank you for using Access "language" instead English.
I understand a lot better this one than English :)