Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
add comment

1 Answer

up vote 0 down vote accepted

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.

share|improve this answer
    
thank you very much –  messyalex Jan 5 at 22:09
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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