I am planning a flashcards app with the following schema:

user(id, name, email)
flashcard_group(id, user_id, group_name, group_tag) 
flashcard(id, group_id, question, solution)
guess(id, user_id, flashcard_id, is_correct)

Some of the things I would like to ultimately calculate are:

  1. The number of times a user guesses a flash card incorrectly and correctly (or a correctness percentage)
  2. All of a user's flashcards
  3. All of the user's flashcard groups
  4. Probably more information about guesses.

Does this seem reasonable? How else could I go about capturing guesses in a logical fashion?

  • 1
    Is this homework?
    – Philᵀᴹ
    Sep 19, 2012 at 17:21
  • no, it's actually a side project I'm doing to learn more about database schemas. I dropped out of my databases class because all we learn about is oracle and relational theory. I figured I'd learn more out of a real project (albeit a lame one).
    – mergesort
    Sep 19, 2012 at 17:26

2 Answers 2

  • Flashcards are used to test knowledge in a topic.
  • Each card has a question and a correct answer.
  • One topic has many cards.
  • Each card may belong to more then one topic.
  • User chooses a topic to test in.
  • User may attempt the same test many times.
  • Each test has many cards (questions) from the chosen topic.
  • Each card may be part of many test from the chosen topic.

enter image description here

Note: UserTopicAttempt is as sequence number (1,2,3 ...) for each (USerID,TopicID).


Try the following tables

user(user_id(pk), name, email)

flashcard_group(group_name(pk), group_tag)
flashcard(flashcard_id(pk), question, solution)
flashcard_groupings(group_name(fk), flashcard_id(fk)) <- to join flashcards to groups

guess(guess_id, user_id(fk), flashcard_id(fk), is_correct) <- user_id and flashcard_id to be unique key.

The above assumes that there is 1 user to 1 set of guesses, and that the flash card grouping is grouping the flashcards together (ie cards a, b and c and in group green, etc). If a user can have multiple go's at this then you need to add another table to hold the session information between the guess and user, a bit like what the table flashcard_groupings is there for.

To get the queries you are after, join the tables as needed to get the groupings you need. For guess percentage, just join tables user and guess. To get the flash cards join user, guess and flashcard. And so on.

  • what's the advantage of having a flashcard_groupings table vs having the unique group_id in the flashcard table?
    – mergesort
    Sep 20, 2012 at 0:27
  • is that so a flashcard belong in multiple groups?
    – mergesort
    Sep 20, 2012 at 1:10

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.