Designing a Chatbot App Database - Part 6: Formatting Interview Questions and Answers

October 25,2024

images/future_kds_discovery_erd_20241010.png

In my last post, I explained how I loaded the model and interview schema data. With the interview.question table populated, the next step was to create a process to retrieve interview questions and store user answers in the database. I decided to build a simple web app prototype using Python, HTML/JavaScript, and Flask. Before diving into the app itself, I'll discuss some key data design decisions that shaped this process.

Redefining the `type_` Column and Adding `gui_type`

Originally, the type_ column in the interview.question table (and similar tables) was used to control how questions should be rendered in the user interface. However, I realized that type_ should refer to the logical structure of a question (e.g., open-ended, closed-ended, multiple-choice), not its visual representation.

To address this, I introduced a new column, gui_type, to specify the front-end control used to capture answers (e.g., radio buttons, checkboxes, dropdowns). This separation of question logic from UI logic not only clarified data organization but also offered flexibility in designing the user interface(UI) without altering the core question structure.

Here’s are some examples:

Question	type_	gui_type
What is your gender?	closed_ended	radio buttons
Which CRM systems do you use?	multiple_choice	checkboxes
Describe your job role.	open_ended	multi-line textarea
Rate your satisfaction.	rating_scale	rating control

Adding the gui_type, the interview.question table now looks like this:


  CREATE TABLE IF NOT EXISTS interview.question
	(question_id uuid NOT NULL DEFAULT gen_random_uuid(),
	 model_question_id uuid NOT NULL,
	 author_id uuid,
	 syntax_ jsonb NOT NULL,
	 help_ jsonb,
	 sort_order smallint NOT NULL,
	 create_date date NOT NULL,
	 modified_date date,
	 disable_ bit(1),
	 topic_ character varying(96) NOT NULL,
	 subtopic_ character varying(96) ,
	 weight_ numeric(10,2),
	 source_ character varying(96) NOT NULL,
	 type_ character varying(48),
	 gui_type character varying(48))

Storing Answers with JSONB

The answers are stored in the interview.answer table, where the answer_ column uses the jsonb data type.


 CREATE TABLE IF NOT EXISTS interview.answer
	(answer_id uuid NOT NULL DEFAULT gen_random_uuid(),
	question_id uuid,
	respondent_id uuid NOT NULL,
	answer_ jsonb,
	answer_date date NOT NULL,
	score_ smallint,
	location_reference character varying(48));

This allows for flexible storage of various answer types (closed-ended, open-ended, multiple-choice) and their associated UI controls (radio buttons, checkboxes, etc.). Since the structure of the answer depends on the question and UI control, JSON provides the versatility needed to store these answers in different formats like:

Closed-Ended (Single Selection): In this example, the user selects "Male" as their answer to a gender question with radio buttons.
```
{
  "answer": "Male"
}
```
Multiple Selection: Here, the user selects multiple CRM systems (Salesforce and HubSpot) using checkboxes.
```
{
  "answer": ["Salesforce", "HubSpot"]
}
```
Open-Ended: For an open-ended question about job roles, the user provides a free-form text answer.
```
{
  "answer": "I am responsible for overseeing the marketing team and managing campaigns."
}
```

Using a JSON data type in a relational database like PostgreSQL is a smart choice because it allows for flexible data storage without needing to change the database schema. This is particularly useful for surveys or forms where questions may have different answer formats, such as single selections, multiple selections, or open-ended responses. In PostgreSQL the JSONB data type enables each answer to be stored as a JSON object, accommodating various structures and making it easy to add new question types or adjust existing ones as needed. While the JSON data will likely require parsing at some point to retrieve specific data, this approach works well since most answers are relatively short. If there is a need to parse a large volume of JSON data, the process can be done in the background asynchronously, ensuring smooth application performance and efficient data management.

Developing the Web App with Flask, Python, and HTML/JavaScript

I chose to develop the web app using Python, Flask, and JavaScript for several compelling reasons. The combination of these technologies provides a flexible, user-friendly approach to building an interactive interview platform. Advantages include:

Dynamic Form Generation
The form is generated based on data retrieved from the database, making the app adaptable and maintainable. Changes to the interview structure or questions are automatically reflected without requiring hardcoding in the HTML template.
Database-Driven Content
By pulling questions, help text, and answer options from the database, the app maintains centralized and consistent content. This separation of data from presentation supports scalability and flexibility.
User Interaction and Data Submission
The app handles both GET requests (to display the form) and POST requests (to process responses), preparing the system to efficiently capture and store answers. While the response processing will be implemented in future iterations, the foundational structure is already in place.
Security Through Environment Variables
Using environment variables for database credentials (via os.getenv and load_dotenv) ensures that sensitive information, such as database access details, are not hardcoded, enhancing security and simplifying environment management.

Python, Flask, and JavaScript were excellent choices for prototyping the interview retrieval and answering process. Python's readability and efficiency make it ideal for quickly developing and iterating backend logic, including database interactions and data processing. Flask, a lightweight web framework, allows developers to create web applications with minimal boilerplate, enabling rapid prototyping and easy feature integration. Additionally, JavaScript enhances user experience by providing interactivity on the client side, facilitating dynamic form elements and real-time data handling without page refreshes. This combination empowers developers to build, test, and refine applications swiftly, ensuring that the prototype meets user needs while maintaining a clean and organized codebase.

Here is the the Python code that starts the Flask server and retrieves the questions from the database.

import os
import psycopg2
from flask import Flask, render_template, request
from dotenv import load_dotenv

load_dotenv()  # Load environment variables from .env file

app = Flask(__name__)

# Function to fetch questions from the database including help_ column
def fetch_questions():
    conn = psycopg2.connect(
        dbname=os.environ.get("DBSURVEY"),
        user=os.environ.get("DBUSER"),
        password=os.environ.get("DBPASSWORD"),
        host=os.environ.get("DBHOST"),
        port=os.environ.get("DBPORT")
    )
    cur = conn.cursor()
    
    # Adjust SQL to select from the stage.question schema
    cur.execute("""
   SELECT
    syntax_ AS question_text,
    gui_type,
    help_->>'help_text' AS help_text,
    help_->'help_list' AS help_list
    FROM
    interview.question
    ORDER BY
    sort_order;
    """)
    
    questions = cur.fetchall()
    conn.close()

    # Here we're unpacking four values per question row
    formatted_questions = []
    for row in questions:
        question_text, type_, help_text, help_list = row  # Ensure all four values are unpacked
        formatted_questions.append((question_text, type_, help_text, help_list))
    
    return formatted_questions

# Route for displaying the form
@app.route('/', methods=['GET', 'POST'])
def index():
    if request.method == 'POST':
        # Process form submission
        responses = request.form.getlist('response')
        # Code to handle saving responses to the database goes here
        return "Responses submitted successfully!"
    else:
        # Fetch questions from the database
        questions = fetch_questions()
        return render_template('form_05.html', questions=questions)

if __name__ == '__main__':
    app.run(debug=True)

The code first sets up a Flask web app that connects to a PostgreSQL database using psycopg2. It fetches questions from the interview.question table, including question text (syntax_), UI control (gui_type), and help information (help_ column). The app dynamically renders the form based on this data.

Question Rendering

This HTML/JavaScript code creates an interactive interview form that collects responses from users while allowing for various input types based on the question's format. It dynamically generates form elements such as dropdowns, text areas, and checkboxes, with options for custom responses where applicable (e.g., an "Other" choice prompts a text input for additional details). The JavaScript functions enhance user experience by enabling users to add or remove responses and toggle visibility of custom input fields based on their selections, ensuring a flexible and user-friendly interface. Here's what the prototype form looks like:

Recap

This post explains how to retrieve and format interview questions while managing user responses efficiently. By adding the gui_type column, I separated the logic and visual aspects of the questions. Using JSONB to store answers offered flexibility for different input types, streamlining data management. I also demonstrated building a Flask web app that dynamically creates forms from database content, improving user interaction. Python, Flask, and JavaScript simplify backend tasks and enable a responsive frontend. In the next post, I’ll cover saving user answers to the database.

Thanks for stopping by.