How to make database for Vocabulary list app with multiple languages like google translate one?

Question

I am working on a vocabulary list app and let's consider that my app has 2 inputs for words in multiple languages. for example, user native language is Korean and wants to learn English, so he/she will choose (Korean/English) from app setting, learn a new word, and put the word in Korean in the first input and English meaning in the second input and save it to see his/her list of words later. I made a simple database and model for that

@Entity(tableName = "words")
data class Word(
    val first_word: String,
    val second_word: String,
    val bookmarked: Boolean,
    @PrimaryKey(autoGenerate = true)
    val id: Int
)

but my problem is how to change it for supporting multiple languages but not show it in one list. Korean/English words will show in their list. Korean/Japanese words will show in their list and... It's a little bit like google translate. you choose Korean, then English, and it will show you the word only in Korean and English. I don't know how to make this database, have I to make multiple tables for every 2 languages? I hope you understand what I want to do :) if you need more information, please tell me.

also, I am working with android/kotlin , SQLite database, and Room library.

Answer 1

You really need to consider the design before embarking on the how to proceed with code.

I would suggest that multiple table are not necessary and would probably be a hindrance. A single table, at least for the words, as opposed to multiple tables per language combination would be possible. By having a an indication of the from language and the to language (languages being specified by a table for the languages).

As an example perhaps consider the following SQLite code:-

CREATE TABLE IF NOT EXISTS language (id INTEGER PRIMARY KEY, language TEXT UNIQUE);
CREATE TABLE IF NOT EXISTS wordv1 (id INTEGER PRIMARY KEY, fromLanguage INTEGER REFERENCES language(id), toLanguage INTEGER REFERENCES language(id), fromword TEXT, toword TEXT);

INSERT OR IGNORE INTO language (language) 
    VALUES('English'),('French'),('German')
;
INSERT OR IGNORE INTO wordv1 (fromlanguage,tolanguage,fromword,toword)
    VALUES
        (1,2,'Bread','Pain'),
        (1,3,'Bread','Brot'),
        (1,3,'Milk','Milch'),
        (1,2,'Milk','Lait'),
        (1,2,'Water','Eau'),
        (1,3,'Water','Wasser'),
        (3,1,'Haus','House'),
        (2,1,'Maison','House')
;

SELECT fromword, toword FROM wordv1 WHERE (tolanguage = 1 OR fromlanguage = 1) AND (tolanguage = 3 OR fromlanguage = 3);

• The SELECT is saying select words that have an English and German component The result being :-

However, the above is not normalised, e.g. Milk is repeated for every permutation.

To normalise and have a word just have a single entry, entails a many-many relationship, which is typically achieved via a mapping table (lots of other names such as a cross ref table)

To move to such a schema, the word table itself is simpler, just the word and it's language. The mapping table would then map/associate/cross reference a word with another word in another language.

The language table would still be utilised as is.

The word (wordv2) table could be simplified to :-

 CREATE TABLE IF NOT EXISTS wordv2 (id INTEGER PRIMARY KEY, word TEXT, language INTEGER REFERENCES language(id));

At this stage words could be loaded into the table so to replicate the above (4 words in 3 languages) then :-

INSERT INTO wordv2 (word,language)
    VALUES
    ('Bread',1),('Milk',1),('Water',1),('House',1),
    ('Pain',2),('Lait',2),('Eau',2),('Maison',2),
    ('Brot',3),('Milch',3),('Wasser',3),('Haus',3)
;

The mapping table, has two columns each that references one of the words, the primary key (which implies a unique value) would consist of the two columns combined. So this could be :-

CREATE TABLE IF NOT EXISTS wordtranslatemap (fromword INTEGER REFERENCES wordv2(id),toword INTEGER REFERENCES wordv2(id), CHECK (fromword <> toword), PRIMARY KEY(toword,fromword));

Loading the data into the mapping table, (including both-way conversions (may or may not be useful) e.g. Milk->Milch as well as Milch->Milk) could be :-

INSERT INTO wordtranslatemap VALUES
    (1,5),(5,1),(1,9),(9,1),(5,9),(9,5),
    (2,6),(6,2),(2,10),(10,2),(6,10),(10,6),
    (3,7),(7,3),(3,11),(11,3),(7,11),(11,7),
    (4,8),(8,4),(4,12),(12,4),(8,12),(12,8)
;

• Note that the above numbers are the id columns, ASSUMING that they are sequential and start from 1 and are incremented by 1 for each insert. YOU WOULD/SHOULD NOT MAKE SUCH ASSUMPTIONS FOR AN APP, typically you would get the id's via user friendly data (such as the Word or the Language) presented.

• As Bread is the first row inserted it's id will very likely be 1, Milk 2 .... , as such (1 (Bread), 5 (Pain)) is the English to French for Bread 5,1 is the French to English (perhaps optional but it may make life easier).

Retrieving data is a little more complex you could for example use something like:-

SELECT f.word, fl.language, t.word AS , tl.language /* The columns that we want NOTE use of aliases for tables */
FROM wordtranslatemap  /* the main table being looked at */
JOIN wordv2 AS f ON wordtranslatemap.toword = f.id /* get the referenced fromWord. give table an alias of f */
JOIN wordv2 AS t ON wordtranslatemap.fromword = t.id /* get the referenced toWord. give table an alias of f */
JOIN language AS fl ON f.language = fl.id /* get the language of the fromWord. alias with fl */
JOIN language AS tl ON t.language = tl.id /* get the language of the toWord. alias with tl */
/*
    Alias's required otherwise column references are ambiguous.
    See how in the output (as output columns not aliased, they could be) you have word and word(1)
    i.e. both output columns are named word the utility has detected them and added a distinction
*/
/* So we want the translation from English to German of all from words that start with Br */
WHERE fl.language = 'English' AND tl.language = 'German' AND f.word Like('Br%')
ORDER BY fl.id,tl.id,f.word,t.word
;

The result being :-

When you have designed your schema (the tables and the columns within) you could then move onto implementing the schema into room.

I would suggest always having unique column names

Putting the above into Room (2nd normalised).

Step1 - Creating the Entities (the table objects and therefore the tables)

The language table :-

@Entity
data class Language(
    @PrimaryKey
    @ColumnInfo(name = "language_id")
    var id: Long?=null,
    var language: String = ""
)

The Word table :-

@Entity(
    foreignKeys = [
        ForeignKey(
            entity = Language::class,
            parentColumns = ["language_id"],
            childColumns = ["language_map"],
            onDelete = ForeignKey.CASCADE,
            onUpdate = ForeignKey.CASCADE
        )
    ],
    indices = [
        Index("language_map")
    ]
)
data class Word(
    @PrimaryKey
    @ColumnInfo(name = "word_id")
    var id: Long?=null,
    var word: String="",
    @ColumnInfo(name = "language_map")
    var language: Long
)

The wordtranslatemap table :-

@Entity(
    primaryKeys = ["from_word_map","to_word_map"],
    foreignKeys = [
        ForeignKey(
            entity = Word::class,
            parentColumns = ["word_id"],
            childColumns = ["from_word_map"],
            onDelete = ForeignKey.CASCADE,
            onUpdate = ForeignKey.CASCADE
        ),
        ForeignKey(
            entity = Word::class,
            parentColumns = ["word_id"],
            childColumns = ["to_word_map"],
            onDelete = ForeignKey.CASCADE,
            onUpdate = ForeignKey.CASCADE
        )
    ],
    indices = [
        Index("to_word_map")
    ]
)
data class WordTranslateMap(
    @ColumnInfo(name = "from_word_map")
    val fromWord: Long,
    @ColumnInfo(name = "to_word_map")
    val toWord: Long
)

Next a means of extracting data that combines data from multiple tables. These are POJO classes i.e. they dont have an @Entity as they are not tables.

Although not used in the example, words with their language via an @Embedded and an @Relation WordWithLanguage :-

data class WordWithLanguage (
    @Embedded
    val word: Word,
    @Relation(entity = Language::class,parentColumn = "word_id",entityColumn = "language_id")
    val language: Language
)

• In this case the @Relation annotation does the underlying JOIN (well actually it doesn't but it mimics the JOIN via underlying queries)

To reflect the query as above then another POJO for a TranslatedWord :-

data class TransaltedWord(
    val from_word: String,
    val from_word_id: Long,
    val from_language: String,
    val from_language_id: Long,
    val to_word: String,
    val to_word_id: Long,
    val to_language: String,
    val to_language_id: Long
)

• Note that the actual column names in the table are not used, that's so we deal with the ambiguous column names.
  • The columns names will be assigned respectively in the respective @Query

Now we need to tell Room about methods/functions that will interact with the tables in the database. These are defined in interface or abstract classes annotated with @Dao. For this example a single class WordConvertDao is :-

@Dao
abstract class WordConvertDao {
    @Insert(onConflict = OnConflictStrategy.IGNORE)
    abstract fun insert(language: Language): Long
    @Insert(onConflict = OnConflictStrategy.IGNORE)
    abstract fun insert(word: Word): Long
    @Insert(onConflict = OnConflictStrategy.IGNORE)
    abstract fun insert(wordTranslateMap: WordTranslateMap): Long
    @Query("SELECT * FROM word")
    abstract fun getAllWords(): List<Word>
    @Query("SELECT * FROM language")
    abstract fun getAllLanguages(): List<Language>
    @Transaction
    @Query("SELECT * FROM wordtranslatemap")
    abstract fun getAllWordTranslateMaps(): List<WordTranslateMap>
    @Query("SELECT * FROM word")
    abstract fun getAllWordsWithLanguage(): List<WordWithLanguage>
    @Query("SELECT " +
            "f.word AS from_word, " +
            "f.word_id as from_word_id, " +
            "fl.language AS from_language, " +
            "fl.language_id AS from_language_id, " +
            "t.word AS to_word, " +
            "t.word_id AS to_word_id, " +
            "tl.language AS to_language, " +
            "tl.language_id AS to_language_id " +
            "FROM wordtranslatemap " +
            "JOIN word AS f ON from_word_map = f.word_id " +
            "JOIN word AS t ON to_word_map = t.word_id " +
            "JOIN language AS fl ON f.language_map = fl.language_id " +
            "JOIN language AS tl ON t.language_map = tl.language_id ")
    abstract fun getAllTranslatedWords(): List<TransaltedWord>
}

• As you can see/guess the @Insert's are for inserting data, the @Query's are for extracting data. The very last @Query is the equivalent of the SQLite query above noting the use of aliasing the column names using AS to suit the TranslatedWord POJO's fields/member/variables.

Everything then has to be put together via an abstract class that is annotated with @Database, in this case TheDatabase :-

@Database(entities = [Language::class,Word::class,WordTranslateMap::class],version = 1)
abstract class TheDatabase: RoomDatabase() {
    abstract fun getWordConvertDao(): WordConvertDao

    @TypeConverters(Converters::class)

    companion object {
        @Volatile
        private var instance: TheDatabase? = null

        fun getInstance(context: Context): TheDatabase {
            if (instance == null) {
                instance = Room.databaseBuilder(context,TheDatabase::class.java,"wordconvert.db")
                    .allowMainThreadQueries()
                    .build()
            }
            return instance as TheDatabase
        }
    }
}

• NOTE .allowMainThreadQueries is used for brevity/convenience, you would be recommended to look into utilising other than the main thread.

Finally, actually doing something, loading some data and then extracting the data in an Activity MainActivity :-

class MainActivity : AppCompatActivity() {
    lateinit var db: TheDatabase
    lateinit var dao: WordConvertDao
    override fun onCreate(savedInstanceState: Bundle?) {
        super.onCreate(savedInstanceState)
        setContentView(R.layout.activity_main)

        db = TheDatabase.getInstance(this)
        dao = db.getWordConvertDao()

        val english = dao.insert(Language(language = "English"))
        val german = dao.insert(Language(language = "German"))
        val french = dao.insert(Language(language = "French"))
        val bread = dao.insert(Word(word = "Bread",language = english))
        val milk = dao.insert(Word(word = "Milk", language = english))
        val water = dao.insert(Word(word = "Water", language = english))
        val house = dao.insert(Word(word = "House", language = english))
        val brot = dao.insert(Word(word = "Brot",language = german))
        val milch = dao.insert(Word(word = "Milch", language = german))
        val wasser = dao.insert(Word(word = "Wasser",language = german))
        val haus = dao.insert(Word(word = "Hause", language = german))
        val pain = dao.insert(Word(word = "Pain", language = french))
        val lait = dao.insert(Word(word = "Lait", language = french))
        val eau = dao.insert(Word(word = "Eau", language = french))
        val maison = dao.insert(Word(word = "Maison", language = french))

        dao.insert(WordTranslateMap(bread,brot))
        dao.insert(WordTranslateMap(brot,bread))
        dao.insert(WordTranslateMap(bread,pain))
        dao.insert(WordTranslateMap(pain,bread))
        dao.insert(WordTranslateMap(pain,brot))
        dao.insert(WordTranslateMap(brot,pain))
        dao.insert(WordTranslateMap(milk,milch))
        dao.insert(WordTranslateMap(milk,lait))
        dao.insert(WordTranslateMap(milch,lait))
        dao.insert(WordTranslateMap(milch,milk))
        dao.insert(WordTranslateMap(lait,milk))
        dao.insert(WordTranslateMap(lait,milch))
        // etc

        // Extract everthing as TranslatedWord's and write the extracted data to the log.
        for(t: TransaltedWord in dao.getAllTranslatedWords()) {
            Log.d("CONVERTINFO","From=${t.from_word} (${t.from_language}) is ${t.to_word} (${t.to_language})")
        }
    }
}

The Result :-

D/CONVERTINFO: From=Bread (English) is Brot (German)
D/CONVERTINFO: From=Brot (German) is Bread (English)
D/CONVERTINFO: From=Bread (English) is Pain (French)
D/CONVERTINFO: From=Pain (French) is Bread (English)
D/CONVERTINFO: From=Pain (French) is Brot (German)
D/CONVERTINFO: From=Brot (German) is Pain (French)
D/CONVERTINFO: From=Milk (English) is Milch (German)
D/CONVERTINFO: From=Milk (English) is Lait (French)
D/CONVERTINFO: From=Milch (German) is Lait (French)
D/CONVERTINFO: From=Milch (German) is Milk (English)
D/CONVERTINFO: From=Lait (French) is Milk (English)
D/CONVERTINFO: From=Lait (French) is Milch (German)