MySQL tables USERS and FLASHCARDS - how to modify one depending on the other?

Question

I'm implementing a flashcard app, hence my FLASHCARDS table contains fields such as QUESTION and RESPONSE, and my USERS table is just that - names and personal info of my flashcard app.

Here's where I get confused. The FLASHCARDS table will hold 100s of questions and responses categorized into "groups" of flashcards (or decks). When the USERS "use" the flashcards they will also be able to decide if the question was EASY, NORMAL, or DIFFICULT - hence modifying the time till the card is next shown.

If I only had one user this wouldn't be a problem - I'd just modify the FLASHCARDS table accordingly, but I´ll also have 100s of users. How can I modify FLASHCARDS table depending on each USERS decision of EASY, NORMAL or DIFFICULT and keep record of all of this for each user (I imagine in USERS table).

Answer 1

You've got a many-to-many relationship between the "Flashcard" entity and the "User" entity.

A particular "User" makes a decision about a particular "Flashcard".

A "User" can make a decision on zero, one or more "Flashcard".

A "Flashcard" can be decided by zero, one or more "User".

That's a classic many-to-many relationship.

In the relational model, we introduce a new relationship table, that establishes the relationship between "User" and "Flashcard"

As an example of what this table might look like:

CREATE TABLE user_flashcard
( user_id      INT UNSIGNED NOT NULL COMMENT 'fk ref user'
, flashcard_id INT UNSIGNED NOT NULL COMMENT 'fk ref flashcard'
, decision     VARCHAR(30)           COMMENT 'EASY,NORMAL,DIFFICULT'
, PRIMARY KEY (user_id,flashcard)
, CONSTRAINT FK_user_flashcard_user 
     FOREIGN KEY (user_id) REFERENCES user(id)
, CONSTRAINT FK_user_flashcard_flashcard 
     FOREIGN KEY (flashcard_id) REFERENCES flashcard(id)
)

You could add additional attributes, for example: the last time the user viewed the flashcard, the number of times the user has viewed it.

You also need to consider whether this is just a pure relationship, or if this is might actually be an entity in your model.If we have repeating attributes, or any other entities might be related to this table, we'd likely want to introduce a simple primary key (id) that other tables can reference in a foreign key.

We also want to think about this, do we want a user to have more than one decision on a flashcard? Does (user_id,flashcard_id) need to be unique, or should it be non-unique.

The key to database design is data analysis. And one of the best tried-and-true techniques for "doing" data analysis is Entity Relationship Modeling.

---

FOLLOWUP EXAMPLE DEMONSTRATION

As a demonstration of how this "relationship" table works, I've created a very short SQL Fiddle, demonstrating the kind of questions that can be fairly easily answered using some fairly simple SQL.

SQL Fiddle Here http://sqlfiddle.com/#!9/090ee/5