Skip to content
Advertisement

How to initialize table for storing different answers on a form with 100 questions (SQLite3 and Python)

So I have just started learning SQLite (never been working with databases before) and I have a table “users”, the table consists of “user_id”, “email”, “age”, “sex”, “password”. I want to ‘link’ (join..?) the “user_id” INT to a user specific (different) table which will contain the users answer to 100 different questions. I want to store the answer for every question. The answer of the questions then gets calculated and stored in 8 different variables. Which will be displayed in a bar-chart. So how do I do this?

As I am thinking about it now, I will create a new table that will contain all the different users answers in its user-specific row. The row containing “user_id” (which will “link” the two different tables) in column 1, in column 2-101 the answers will be stored, and in column 102-110 the calculated results. Is this the “right way”?

Also, when initializing the table storing the answers, it seems a bit tedious to do:

        CREATE TABLE IF NOT EXISTS answers(
            id INTEGER PRIMARY KEY,
            question_1 INTEGER,
            question_2 INTEGER,
            question_3 INTEGER,
            .
            .
            .
            question_99 INTEGER,
            question_100 INTEGER,
            sums_1 INTEGER,
            sums_2 INTEGER,
            .
            .
            .
            sums_8 INTEGER
        )

How do I do this the best way?

Just tried this:

    db = sqlite3.connect("tests.db")
    c = db.cursor()
    c.execute('''
        CREATE TABLE IF NOT EXISTS answers(
            id INTEGER PRIMARY KEY, 
    ''')
    for i in range(100):
        c.execute('''
        ALTER TABLE answers
        ADD ''' + 'question_' + i + '''INTEGER''')

    db.commit()

And it did not work. Why?

Advertisement

Answer

So it sounds like you already have two tables, user and question, that store data about each user and then questions that are asked each user. Now you want to store the answers for each question and each user. This is what we call a many-to-many relationship. This means that each user answers many questions and for each question there are many users that gave an answer. To store this in a answer table, you need 4 columns:

  1. The id for the relationship. This is just a typical primary key.
  2. A foreign key for a user. This specifies which user gave the answer.
  3. A foreign key for a question. This specifies which question the user answered.
  4. The answer to the question.

The CREATE TABLE statement is left as an exercise to the reader.

I assume here that you already have a table with questions. If not, then you should create one with at least two columns:

  1. The id of the question. This is a standard primary key.
  2. The question itself. I assume this is a VARCHAR to store the text of the question.

As for the “sums”, you should calculate these from the data stored in this table. This can be done with a stored procedure or view if your database supports these features (I don’t know if SQLite supports either of these, but other database engines do). If these aren’t supported, then you can calculate the sums in python code. Either way, you should only store the raw answers. Any processing to show a bar chart should be done programmatically either with SQL or Python.

This solution provides a lot of flexibility. If you decide to add questions later, you can do so easily by adding them to the questions table. Then users can answer those new questions and the answers are added to the answers table without requiring any changes to it. Your version would require adding columns to the answers table which requires more work.

If you are unfamiliar with primary keys and foreign keys, you should definitely take the time to learn about them. These are fundamental to any relational database because they define the relationships that make it “relational”.

Advertisement