What are the pros and cons of this schema for flashcards app?

Question

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?

Answer 1

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

---

---

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

Answer 2

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.