I'm making a simple flashcards app for personal use.
I've thought about having such table:
CREATE TABLE flashcards (
translation VARCHAR(50),
entry VARCHAR(50),
lang VARCHAR(2),
displayed INT DEFAULT 0,
known INT DEFAULT 0,
lastKnown DATE NULL,
PRIMARY KEY (translation)
)
Sample data would be:
'périlleux' , 'dangerous' , 'fr' , ...
'hasardeux' , 'dangerous' , 'fr' , ...
'mauvais' , 'dangerous' , 'fr' , ...
'pericoloso' , 'dangerous' , 'it' , ...
'malfido' , 'dangerous' , 'it' , ...
'niebezpieczny' , 'dangerous' , 'pl' , ...
'gefährlich' , 'dangerous' , 'de' , ...
'paisible' , 'calm' , 'fr' , ...
'tranquille' , 'calm' , 'fr' , ...
However, I have a feeling that it's not the best way to design this database as it is not aware of the relation between the translations of the same word.
The app will work like this (using speech recognition and speech synthesis):
- get entry from database and say it
- get all the translations of it for chosen language
- wait for me saying the answer or some "I don't know" command
- update "scores"
- in case I didn't answer correctly say all of the translations (from 2.)
Alternatively (in other mode):
- get entry from database and one of its translation in another language and say it (the translation)
I can make it work just fine with multiple query's to the db. But I'm writing here to ask for more of an elegant way to deal with the design. For which I'll be grateful.
Best Answer
This is a good use for a parent-child model. This can be easily accessed with a join query. Include the language(s) you want to work with in the
where
clause of the query.This model below handles multiple translations between multiple languages. If you want flashcards for one language as in your example above, remove the lang column from the flashcards table.
Depending on your requirements, you may not want the unique key.