While developing the backend for Vim Quizzer, I encountered a challenge in handling a list of incorrect questions for our question class due to MySQL's lack of direct support for list structures. To address this, I created an Incorrect Question class and established a One-to-Many relationship between the Question class and the Incorrect Question class, successfully implementing a list structure. In this post, I will explain how I achieved that and also shed light on how the One-to-Many relationship works.
Before we dive into the technicalities, let me give a brief overview of the project that kick started this adventure. Vim Quizzer is an engaging platform designed to help users learn Vim commands through a series of multiple-choice questions that test their knowledge of this command-line text editor, commonly used on Linux machines. If you're eager to put your Vim skills to the test, you can try the quiz here, the source code is also available here.
The Challenge
MySQL serves as the relational database management system, where we store the questions for our web application. As depicted above, our database structure includes a table named questions with 4 columns: question_id, question, correct_answer, and incorrect_answers. The question_id column holds the question id while the question column accepts a string to store the question itself, the correct_answer column also takes a string to store the accurate response for the question. On the other hand, the incorrect_answers column requires a list of strings to store the various incorrect responses for the question.
However, MySQL lacks built-in support for list structures so we can't have an incorrect_answers column. To address this, we had to devise an alternative approach, and that's where One-to-Many relationship came into play.
One-To-Many Relationship
As illustrated above, we have established an incorrect answers table, which comprises the incorrect_answer_id column, the question_id column, and the incorrect_answer itself. It is evident that the question_id serves as a shared column between the question table and the incorrect_answer table. The incorrect answers to the question in our first illustration are now stored accordingly.
Here, all 3 incorrect answers possess the same question_id, signifying that they correspond to one particular question in our database. This linkage ensures that each incorrect answer is appropriately associated with its corresponding question.
To enable the incorrect_answers attribute to return the list of all incorrect answers associated with a question, we implemented the following code in our Question class.
# Create list of incorrect answers incorrect_answers = relationship("IncorrectAnswer", backref="question", cascade="all, delete-orphan") In our implementation, we take advantage of SQLAlchemy's relationship method to establish a seamless link between incorrect_answers and the Incorrect Answer class. Thanks to the backref parameter, accessing incorrect answers from a given question becomes a breeze, eliminating the need for complex queries or tangled joins. See snippet below.
>>> type(question1) <class '__main__.Question'> >>> question1.question 'What is the Vim command to save and exit a file?' >>> question1.correct_answer ':wq' >>> question1.incorrect_answers [":w", ":q!", ":s"] Furthermore, the cascade parameter works wonders in ensuring data integrity. When we remove a question, all its associated incorrect answers are gracefully deleted as well. This intelligent handling of cascading deletes prevents any orphaned records from cluttering up the database, maintaining a tidy and organized data structure. This solution brings simplicity and efficiency to the management of question and incorrect answer relationship, streamlining the entire process for optimal performance.
As mentioned earlier, this implementation is at the core of our backend, making it the backbone of our entire application. Witnessing this implementation work in real-time was truly refreshing. The One-To-Many relationship unlocks the true power and practicality in our application, allowing us to seamlessly manage questions and their corresponding incorrect answers with utmost efficiency.
I hope this explanation provided a clear understanding of the One-To-Many relationship. Feel free to share your thoughts and experiences in the comments. Whether you have explored this concept before or are eager to delve into it, your insights and feedback are appreciated.