0

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):

  1. get entry from database and say it
  2. get all the translations of it for chosen language
  3. wait for me saying the answer or some "I don't know" command
  4. update "scores"
  5. in case I didn't answer correctly say all of the translations (from 2.)

Alternatively (in other mode):

  1. 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.

1 Answer 1

0

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.

CREATE TABLE flashcards (
    entry_id     INT NOT NULL auto_increment,
    entry        VARCHAR(50) NOT NULL,        
    lang         VARCHAR(2) NOT NULL,
    PRIMARY KEY (entry_id),
    UNIQUE KEY `flashcard_entry` (entry, lang)
);

CREATE TABLE flashcard_translations (
    entry_id     INT  NOT NULL,
    lang         VARCHAR(2) NOT NULL,
    translation  VARCHAR(50) NOT NULL,
    displayed    INT DEFAULT 0,
    known        INT DEFAULT 0L,
    lastKnown    DATE NULL,
    PRIMARY KEY (entry_id, lang),
    FOREIGN KEY (entry_id) REFERENCES flashcards (entry_id),
);

Depending on your requirements, you may not want the unique key.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.