I am creating a site that does a survey and scores the results. It has a number of topics with related questions. I plan to setup tables for questions and another for answers and use FKs to link them. Radio buttons will be used to select the answer on a scale.
All the text is in tables so it’s easy to change without going to the code.
I am looking at 2 possible schemas:
One row per user with all the answers in columns with the column headers simply Q1. - Qn. The FK would be the Respondent
One answer per row with FKs of Question and Respondent
Choice 1 is more elegant to me but then again I come from a VBA/Excel background so I tend to think unique is row and column is data. I just think setting up radio buttons may be a challenge, plus changing the number of questions requires changing the whole table.
Choice 2 made it say to setup the radio buttons and loop through to render teh page; so no issues with having to know how many questions in advance. It just seems odd to have many rows and I’m not sure if the FKs will work. I have it woking without the Respondent table so I know it works.
I guess I’m wondering if option 2 is the better choice or am I missing something taht would allow me to loop through the questions with it?
<opinion>
From a data-model perspective, option 2 is clearly better. <opinion>
Given that your Question Answers table consists of two foreign keys (Question and User), what you’re effectively creating here is a many-to-many relationship between Question and User, with extra data (Answer). There’s nothing “odd” or unusual about this at all.
Creating the table as one answer per row gives you a lot more flexibility regarding changes you may wish to make to these questions over time. This structure facilitates things like being able to add/remove questions, or even define different orderings. Those types of changes can be accommodated without affecting your historical data.
Thanks, Those were my thoughts as well, it was just the “Excel way” nagging in my ear.
Do I need to use a related_name? Some digging I did suggested I would.
There also appears to be a db_column= that allows selecting a specific column as the relationship.
A related name is only required when you have two foreign keys from a model to the same model. Otherwise, you can optionally define one if you decide that it’s beneficial to do so for readability.
I’m not sure I would describe that as an accurate statement of the intent. The model fields are associated with columns in the table of the same name. If you’re using an existing table and are creating a Django model to use that existing table, then you might need to use db_column to define how that mapping exists. Beyond that, you might want to use it if you have other reasons for the table column name to be different from the model field name.
