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.