UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> How Best To Design A Bilingual Dictionary (glossary) Database?, Any Version    
 
   
transliminal
post Sep 27 2019, 03:23 AM
Post#1



Posts: 1
Joined: 27-September 19



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)
Go to the top of the page
 
jleach
post Sep 27 2019, 05:43 AM
Post#2


UtterAccess Editor
Posts: 10,211
Joined: 7-December 09
From: St Augustine, FL


Hi , welcome to UA.

Interesting project. Yet I think on the table design end of things you're going to be in relatively straighforward shape.

Two ways I can see to do the core setup:

First - just as it is in excel. One table, three columns: ID, EnglishWord, ArabicWord. By your description, all else is based off this, and it's not a horrible table design for simple mappings. However, if you might ever have more than a single-word mapping (and I don't know enough of foreign languages to know whether this is practical or not), this wouldn't suffice. A bit more complicated to work with initially, but much more flexible model:


Second - Two tables: one master table with three columns (ID, Language, Word) all words (regardless of origin), and another table to indicated the mapped word(s), or lack thereof. Then you can query all the English words and all the Arabic words easily from the master table. Put a unique index on the language and word composite column, and you can then support words in different languages that are spelled the same and might (or might not) have the same meaning.

Next create a child table that simply points back the the ID of the master table indicating a relationship between words. So you'd have ParentWordID, RelatedWordID (and an autonumber ID for Access as well). Each word from the master, regardless of language, could be "mapped" to any number of other words.

Then, what'd be really cool (I think!) is that for each mapping, you can potential store context information about it. I do know a (very) little bit of Tagolog (my wife being from the Philippines), and I know there's certain words that mean certain things, depending on what context they're used in. This table that denotes the mappings could potentially be used to track information about the specific word mapping (perhaps also given some sort of rating or factor: word A is usually X but sometimes Y, so let's weight X with 80% and Y with 20%, then we can rank mapping results on relevance).


(I'm having fun so far smile.gif )

Ok, your next question. Regardless of which method you choose (though I'd go with my second one as it's much more flexible and you could eventually evolve the system highly without having to worry about a table redesign: which is exactly how a good design should be), you can use the ID of that main table as the master end of a 1:1 relationship.

A 1:1 relationship is when you have one master table which could have multiple different "types", where each "type" could have different fields, and you have as many different "1" tables attaching to the master table as you do different types.

I first came across this in inventory control in manufacturing: we had one master inventory table tracking all of our parts, but our parts were made up of different types of things, each type of thing (manufactured goods, consumables, raw stock, etc) had completely different properties. So, the solution was one master inventory table, and a 1:1 relationship to a ManufacturedGoods table, a ConsumableGoods table, a RawStock table, etc. These "extension" tables didn't have their own Primary Key: they use the same value that's in the master table. So InventoryID 234 might also exist in ManufacturedGoods as ID 234, complete with all the manufactured goods data. The master holds all the common data across all goods: date entered, amount, ID number, etc.

The same 1:1 relationship can be applied to your scenario. You have a master table of all words, but some are different types (verb, noun, etc), and each type has different properties associated with them. Thus, you keep all "common" fields in the master table, and create a 1:1 relationship for each "type" table that you want, storing the type-specific information in those.

I think this would be a very robust table structure. It's a little complex and requires some brain power to think around, and the query layers will be a little more tricky than a traditional "what's the total on this invoice" type of problem, but as a foundation of a system I think it'd be great (I was a client would come to me with this: I'd like to build it out!).

Attached File  2019_09_27_06_42_Office_Lens.jpg ( 215.19K )Number of downloads: 5


Off the top of my head anyway, that's what I have.

I think you would want to avoid the approach of having one table per language (you mentioned it in your post). This is an antipattern as it's all the same type of data (e.g., language ownership and the word itself), and therefore should all be in a single table.

A little more study on 1:1 relationships should fill out a few unanswered questions there, if needed. With the concept and a rough layout, how to deal with PKs between the two and how to query them are minor details that you should be able to find info on easily, but if you need further help on that feel free to post back (I know I gave only an overview)

--------------------
Go to the top of the page
 
GroverParkGeorge
post Sep 27 2019, 07:28 AM
Post#3


UA Admin
Posts: 36,029
Joined: 20-June 02
From: Newcastle, WA


Jack’s discussion is characteristically thorough and accurate IMO. However, I wonder whether this is going to work out to your satisfaction. Access is a Windows application. It doesn’t run on either IOS or Android devices.

When it comes time to consume this data in an app for those devices you may run into some roadblocks for that reason.

I too find this an interesting project, it’s just that I’m not clear on the end goal.


--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
jleach
post Sep 27 2019, 09:35 AM
Post#4


UtterAccess Editor
Posts: 10,211
Joined: 7-December 09
From: St Augustine, FL


Hi George,

I was actually thinking SQL Server rather than Access (or Access first, then migrate to SQL after).

This part of everything is quite easily portable, and could be brought into the .NET realm for backend API for a mobile app, or even built cross-platform using Xamarin (we do this quite a bit: we have systems with SQL Server and Access FEs, web portals and mobile apps all working on the same database(s)).

In terms of database structure, no problems there. In terms of UI buildout, naturally if we go with Access first, it'd be a rewrite for mobile regardless of the Xamarin/React or native platforms, but the DB structure will stay.

Certainly worth noting though, in case it wasn't clear to OP (I didn't touch base on it at all, for sure)

Cheers,

--------------------
Go to the top of the page
 
GroverParkGeorge
post Sep 27 2019, 09:38 AM
Post#5


UA Admin
Posts: 36,029
Joined: 20-June 02
From: Newcastle, WA


I agree. I was actually just looking into db engines for Android and iOS that the OP would have to port the data into. I was mostly trying to alert the OP that Access is handy for prototyping, but not a long-term solution here.
This post has been edited by GroverParkGeorge: Sep 27 2019, 09:40 AM
Reason for edit: Addded links

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    14th November 2019 - 12:18 PM