Navigating One-To-Many Relationship in MySQL
Tags: webdev, mysql, programming, python
Published: 2023-08-03 18:20:44

While developing the backend for [Vim Quizzer](quiz.pelumi.tech),  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](quiz.pelumi.tech), the source code is also available [here](https://github.com/ajipelumi/vim-quizzer).

### The Challenge

![A snapshot of our database structure](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/dgnvyhcy8jcv8xt52vwt.png)

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

![One-To-Many Relationship](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/pagey5dls4eu83hakb5n.png)

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.

![Incorrect Answers Table](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/0pyte8skt2vkufomdxrd.png)

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.

```python
 # 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.
```bash
>>> 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.