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 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?

share|improve this question
1  
Is this homework? –  FreshPhilOfSO Sep 19 '12 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). –  kishinmanglani Sep 19 '12 at 17:26
add comment

2 Answers

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

share|improve this answer
add comment

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.

share|improve this answer
    
what's the advantage of having a flashcard_groupings table vs having the unique group_id in the flashcard table? –  kishinmanglani Sep 20 '12 at 0:27
    
is that so a flashcard belong in multiple groups? –  kishinmanglani Sep 20 '12 at 1:10
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.