How best to design a bilingual dictionary (glossary) database?

[transliminal]

Hi there,

I'm a newb currently trying to design a bi-lingual dictionary/glossary database for eventual use in an iOS/Android app.

I'm inexperienced, and could use some help doing this right, from the ground up.

As follows is a description of my situation:

I have a list of thousands of words in two different languages, English and Arabic. (These are currently stored in two different Excel sheets, one in each language. Each word has its own unique ID [GUID] already assinged).

Each word in English list corresponds to one or more words in the Arabic list, and vice versa.

(Eg., the English word "tomorrow", translates both to "bukra" and "ghadaan" in Arabic. "Ghadaan" in Arabic translates to both "tomorrow" and "next day" in English... etc., etc.)

So, it's a two-way, one-to-many sort of relationship between words (records), i guess you could say.

As i migrate this rudimentary word list from Excel to Access, i also want to add numerous data about each word (record), and generally make the whole database efficient, compact, and readily searchable and updateable.

***

To give you an example of some of the complexity i'll need to account for, in addition to the core bi-directional, one-to-many relationship between English and Arabic words, i also need to store:

(1) each word's "part of speech" (e.g., verb, noun, adjective, etc.); and

(2) ... depending on what part of speech a given word has, i want to store further information about that word (e.g., *if* it's a verb, then i want to store all its conjugations... but *if* it's a noun, then there are no conjugations, but there are plurals, and (in Arabic) masculine, feminine, forms, etc.)

At this stage, i'm curious how you, as database experts, would design this database.

What basic principles should i keep in mind given my particular needs? How do i express the relationships most effectively?

What should i use as my primary vs. foreign keys? etc.

Should i have multiple tables for the different kinds of information i want to store about each word (record)? Or should i just store everything in one gigantic table per language?

... these are the kinds of questions (amongst others) i'm currently contemplating.

Thank you in advance for any help you can offer, be it in terms of general guiding principles, or more specific suggestions.

-jordan (transliminal)

 

[isladogs]

Your post was moderated which isn't unusual for new members. I approved it.
Someone will be along to offer specific advice fairly soon but , in the meantime, suggest you look at the similar threads at the bottom of this page

 

[transliminal]

Thanks isladogs, appreciated.

From what i can tell, none of the autoselected "similar threads" actually deal with this question, despite apparently related titles. Wasn't able to find anything by search, either, which i did prior to posting the thread.

Thanks in advance for any help you can offer.

 

[isladogs]

Try using advanced search. I know this topic has come up before.
I've never done anything similar but other members will have relevant ideas

EDIT Crossposted at https://www.utteraccess.com/forum/index.php?showtopic=2055257&hl=
Please read this article about the etiquette of crossposting https://www.excelguru.ca/content.php?184

 

[The_Doc_Man]

Here is the issue: You have a many-to-many relationship because you have one word in language A that translates to multiple words in language B (perhaps with different nuances) and at the same time, one word in language B translates to multiple words in language A (again with nuances). Look up the concept of the JUNCTION TABLE.

I noticed a case in your example where you render by phrase: "Tomorrow" and also "Next Day." You also mention the possibility of including conjugations or declensions. That brings in the question of whether you have comparative forms for adjectives (great, greater, greatest as an English example) and wanted to include those.

If this is going to be a bilingual dictionary, will you have a definitional phrase as part of your response to a given word? The reason I am asking this isn't that I care - but you DO care. You need to start by enumerating what you will show for a given word. You have mentioned "part of speech" and "conjugation" and "multiple matching words in opposite language" and I just asked about "definition phrase." Will you also have some indicator of pronunciation?

The thrust here is to first define the problem at sufficient depth to be able to estimate its scope. Otherwise, you will never know how big this beast will grow. Also, without a sufficient enumeration of requirements you will never be able to focus on what you need. Then, there is the problem of raw size. How many basic words are you expecting in each language? If you take the simple cases, it is said that about 1000 words (that arent' proper names) are all you need in any language to be able to ask directions and order food at restaurants. If you have 10,000 words you might be able to read a book. If you have 100,000 words you have a magnum opus that if it were in paper format would sit on a pedestal in a library.

As I said, I don't care - but you DO. So be very careful to thoroughly define what you are going to do.