How to store answers in tall and narrow MySQL table The Next CEO of Stack OverflowWhich MySQL data type to use for storing boolean valuesHow to output MySQL query results in CSV format?How do I connect to a MySQL Database in Python?How to get a list of MySQL user accountsInsert into a MySQL table or update if existsMySQL relational tables and Primary Keys data typesHow to reset AUTO_INCREMENT in MySQL?How to get the sizes of the tables of a MySQL database?How to import an SQL file using the command line in MySQL?SQL create table and set auto increment value without Alter table
Math-accent symbol over parentheses enclosing accented symbol (amsmath)
Chain wire methods together in Lightning Web Components
0 rank tensor vs 1D vector
What happened in Rome, when the western empire "fell"?
Is a distribution that is normal, but highly skewed considered Gaussian?
Example of a Mathematician/Physicist whose Other Publications during their PhD eclipsed their PhD Thesis
Yu-Gi-Oh cards in Python 3
RigExpert AA-35 - Interpreting The Information
Why doesn't UK go for the same deal Japan has with EU to resolve Brexit?
Why don't programming languages automatically manage the synchronous/asynchronous problem?
Which one is the true statement?
How to count occurrences of text in a file?
Proper way to express "He disappeared them"
Find non-case sensitive string in a mixed list of elements?
Would a completely good Muggle be able to use a wand?
Solving system of ODEs with extra parameter
Why is information "lost" when it got into a black hole?
TikZ: How to reverse arrow direction without switching start/end point?
Domestic-to-international connection at Orlando (MCO)
Running a General Election and the European Elections together
Is it professional to write unrelated content in an almost-empty email?
Help understanding this unsettling image of Titan, Epimetheus, and Saturn's rings?
Why isn't acceleration always zero whenever velocity is zero, such as the moment a ball bounces off a wall?
Axiom Schema vs Axiom
How to store answers in tall and narrow MySQL table
The Next CEO of Stack OverflowWhich MySQL data type to use for storing boolean valuesHow to output MySQL query results in CSV format?How do I connect to a MySQL Database in Python?How to get a list of MySQL user accountsInsert into a MySQL table or update if existsMySQL relational tables and Primary Keys data typesHow to reset AUTO_INCREMENT in MySQL?How to get the sizes of the tables of a MySQL database?How to import an SQL file using the command line in MySQL?SQL create table and set auto increment value without Alter table
I'm designing a database to store questions and answers. I am planning on having 5 tables: quiz, question, userAnswer and multipleChoice
quiz:
quizID (int)(primary key) Auto Increment
quizName (varchar)
question:
questionID (int)(primary key) Auto Increment
quizID (Foreign Key)
question (varchar)
questionType (enum) textbox, multiple choice, int, date, decimal, email, ...
users:
userID (int)(primary key) Auto Increment
firstname (varchar)
surname (varchar)
...
...
userAnswers:
questionID (Foreign Key)
userID (Foreign Key)
answerText
answerDec
answerDate
answerInt
PRIMARY KEY(questionID, userID)
multipleChoice
choiceID (int)(primary key) Auto Increment
questionID (Foreign Key)
choiceText (varchar)
My problem lies with the answer table, some answers will be free form text, some decimals, and some integers (foreign keys)
How do I approach this? Do I have 4 columns, 1 for each type? If so, how do I efficiently select the correct one of 3 when retrieving the answers, I would like to do as much of the "heavy lifting" in SQL as I can, without having to use PHP to generate dynamic queries.
mysql data-structures
asked Mar 7 at 16:52
mike16889mike16889
9018
add a comment |
I'm designing a database to store questions and answers. I am planning on having 5 tables: quiz, question, userAnswer and multipleChoice
quiz:
quizID (int)(primary key) Auto Increment
quizName (varchar)
question:
questionID (int)(primary key) Auto Increment
quizID (Foreign Key)
question (varchar)
questionType (enum) textbox, multiple choice, int, date, decimal, email, ...
users:
userID (int)(primary key) Auto Increment
firstname (varchar)
surname (varchar)
...
...
userAnswers:
questionID (Foreign Key)
userID (Foreign Key)
answerText
answerDec
answerDate
answerInt
PRIMARY KEY(questionID, userID)
multipleChoice
choiceID (int)(primary key) Auto Increment
questionID (Foreign Key)
choiceText (varchar)
My problem lies with the answer table, some answers will be free form text, some decimals, and some integers (foreign keys)
How do I approach this? Do I have 4 columns, 1 for each type? If so, how do I efficiently select the correct one of 3 when retrieving the answers, I would like to do as much of the "heavy lifting" in SQL as I can, without having to use PHP to generate dynamic queries.
mysql data-structures
asked Mar 7 at 16:52
mike16889mike16889
9018
Maybe my english is too bad or I'm just a noob.. But what are "firing keys" and "heavy lifting" (in this context)?
– Paul Spiegel
Mar 7 at 17:06
You have 4 tables. The first thing you do is to define the relationship between those tables. Read this. stackoverflow.com/help/how-to-ask
– Eric
Mar 7 at 17:41
Firing keys was an auto complete issue, should have been foreign keys. As for heavy lifting, it means hard work, I would like to use SQL to retrieve the data in 1 go, instead of getting the table type, then having a switch statement in PHP to choose which column for a second query. I will update my question with more information on how the tables are structured shortly.
– mike16889
Mar 7 at 23:46
add a comment |
I'm designing a database to store questions and answers. I am planning on having 5 tables: quiz, question, userAnswer and multipleChoice
quiz:
quizID (int)(primary key) Auto Increment
quizName (varchar)
question:
questionID (int)(primary key) Auto Increment
quizID (Foreign Key)
question (varchar)
questionType (enum) textbox, multiple choice, int, date, decimal, email, ...
users:
userID (int)(primary key) Auto Increment
firstname (varchar)
surname (varchar)
...
...
userAnswers:
questionID (Foreign Key)
userID (Foreign Key)
answerText
answerDec
answerDate
answerInt
PRIMARY KEY(questionID, userID)
multipleChoice
choiceID (int)(primary key) Auto Increment
questionID (Foreign Key)
choiceText (varchar)
My problem lies with the answer table, some answers will be free form text, some decimals, and some integers (foreign keys)
How do I approach this? Do I have 4 columns, 1 for each type? If so, how do I efficiently select the correct one of 3 when retrieving the answers, I would like to do as much of the "heavy lifting" in SQL as I can, without having to use PHP to generate dynamic queries.
mysql data-structures
asked Mar 7 at 16:52
mike16889mike16889
9018
I'm designing a database to store questions and answers. I am planning on having 5 tables: quiz, question, userAnswer and multipleChoice
quiz:
quizID (int)(primary key) Auto Increment
quizName (varchar)
question:
questionID (int)(primary key) Auto Increment
quizID (Foreign Key)
question (varchar)
questionType (enum) textbox, multiple choice, int, date, decimal, email, ...
users:
userID (int)(primary key) Auto Increment
firstname (varchar)
surname (varchar)
...
...
userAnswers:
questionID (Foreign Key)
userID (Foreign Key)
answerText
answerDec
answerDate
answerInt
PRIMARY KEY(questionID, userID)
multipleChoice
choiceID (int)(primary key) Auto Increment
questionID (Foreign Key)
choiceText (varchar)
My problem lies with the answer table, some answers will be free form text, some decimals, and some integers (foreign keys)
How do I approach this? Do I have 4 columns, 1 for each type? If so, how do I efficiently select the correct one of 3 when retrieving the answers, I would like to do as much of the "heavy lifting" in SQL as I can, without having to use PHP to generate dynamic queries.
mysql data-structures
mysql data-structures
asked Mar 7 at 16:52
mike16889mike16889
9018
asked Mar 7 at 16:52
mike16889mike16889
9018
edited Mar 8 at 0:28
mike16889
asked Mar 7 at 16:52
mike16889mike16889
9018
asked Mar 7 at 16:52
mike16889mike16889
9018
asked Mar 7 at 16:52
mike16889mike16889
9018
9018
Maybe my english is too bad or I'm just a noob.. But what are "firing keys" and "heavy lifting" (in this context)?
– Paul Spiegel
Mar 7 at 17:06
You have 4 tables. The first thing you do is to define the relationship between those tables. Read this. stackoverflow.com/help/how-to-ask
– Eric
Mar 7 at 17:41
Firing keys was an auto complete issue, should have been foreign keys. As for heavy lifting, it means hard work, I would like to use SQL to retrieve the data in 1 go, instead of getting the table type, then having a switch statement in PHP to choose which column for a second query. I will update my question with more information on how the tables are structured shortly.
– mike16889
Mar 7 at 23:46
add a comment |
Maybe my english is too bad or I'm just a noob.. But what are "firing keys" and "heavy lifting" (in this context)?
– Paul Spiegel
Mar 7 at 17:06
You have 4 tables. The first thing you do is to define the relationship between those tables. Read this. stackoverflow.com/help/how-to-ask
– Eric
Mar 7 at 17:41
Firing keys was an auto complete issue, should have been foreign keys. As for heavy lifting, it means hard work, I would like to use SQL to retrieve the data in 1 go, instead of getting the table type, then having a switch statement in PHP to choose which column for a second query. I will update my question with more information on how the tables are structured shortly.
– mike16889
Mar 7 at 23:46
Maybe my english is too bad or I'm just a noob.. But what are "firing keys" and "heavy lifting" (in this context)?
– Paul Spiegel
Mar 7 at 17:06
Maybe my english is too bad or I'm just a noob.. But what are "firing keys" and "heavy lifting" (in this context)?
– Paul Spiegel
Mar 7 at 17:06
You have 4 tables. The first thing you do is to define the relationship between those tables. Read this. stackoverflow.com/help/how-to-ask
– Eric
Mar 7 at 17:41
You have 4 tables. The first thing you do is to define the relationship between those tables. Read this. stackoverflow.com/help/how-to-ask
– Eric
Mar 7 at 17:41
Firing keys was an auto complete issue, should have been foreign keys. As for heavy lifting, it means hard work, I would like to use SQL to retrieve the data in 1 go, instead of getting the table type, then having a switch statement in PHP to choose which column for a second query. I will update my question with more information on how the tables are structured shortly.
– mike16889
Mar 7 at 23:46
Firing keys was an auto complete issue, should have been foreign keys. As for heavy lifting, it means hard work, I would like to use SQL to retrieve the data in 1 go, instead of getting the table type, then having a switch statement in PHP to choose which column for a second query. I will update my question with more information on how the tables are structured shortly.
– mike16889
Mar 7 at 23:46
add a comment |
1 Answer
1
active
oldest
votes
If you don’t need to search by the answers, then I’d recommend storing them in a JSON object or similar, and not bothering with seperate tables.
quiz | question | answers | multipleChoice
---- | ---------- | ----------- | --------------
1 | "Country?" | [ | true
| | "France", |
| | "Germany" |
| | ] |
answered Mar 7 at 17:43
Thomas EdwardsThomas Edwards
7,70531433
Json will not cut it.
– mike16889
Mar 8 at 10:09
add a comment |
Your Answer
StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");
StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);
else
createEditor();
);
function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);
);
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55049031%2fhow-to-store-answers-in-tall-and-narrow-mysql-table%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
If you don’t need to search by the answers, then I’d recommend storing them in a JSON object or similar, and not bothering with seperate tables.
quiz | question | answers | multipleChoice
---- | ---------- | ----------- | --------------
1 | "Country?" | [ | true
| | "France", |
| | "Germany" |
| | ] |
answered Mar 7 at 17:43
Thomas EdwardsThomas Edwards
7,70531433
Json will not cut it.
– mike16889
Mar 8 at 10:09
add a comment |
If you don’t need to search by the answers, then I’d recommend storing them in a JSON object or similar, and not bothering with seperate tables.
quiz | question | answers | multipleChoice
---- | ---------- | ----------- | --------------
1 | "Country?" | [ | true
| | "France", |
| | "Germany" |
| | ] |
answered Mar 7 at 17:43
Thomas EdwardsThomas Edwards
7,70531433
Json will not cut it.
– mike16889
Mar 8 at 10:09
add a comment |
If you don’t need to search by the answers, then I’d recommend storing them in a JSON object or similar, and not bothering with seperate tables.
quiz | question | answers | multipleChoice
---- | ---------- | ----------- | --------------
1 | "Country?" | [ | true
| | "France", |
| | "Germany" |
| | ] |
answered Mar 7 at 17:43
Thomas EdwardsThomas Edwards
7,70531433
If you don’t need to search by the answers, then I’d recommend storing them in a JSON object or similar, and not bothering with seperate tables.
quiz | question | answers | multipleChoice
---- | ---------- | ----------- | --------------
1 | "Country?" | [ | true
| | "France", |
| | "Germany" |
| | ] |
answered Mar 7 at 17:43
Thomas EdwardsThomas Edwards
7,70531433
answered Mar 7 at 17:43
Thomas EdwardsThomas Edwards
7,70531433
answered Mar 7 at 17:43
Thomas EdwardsThomas Edwards
7,70531433
answered Mar 7 at 17:43
Thomas EdwardsThomas Edwards
7,70531433
7,70531433
Json will not cut it.
– mike16889
Mar 8 at 10:09
add a comment |
Json will not cut it.
– mike16889
Mar 8 at 10:09
Json will not cut it.
– mike16889
Mar 8 at 10:09
Json will not cut it.
– mike16889
Mar 8 at 10:09
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55049031%2fhow-to-store-answers-in-tall-and-narrow-mysql-table%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Maybe my english is too bad or I'm just a noob.. But what are "firing keys" and "heavy lifting" (in this context)?
– Paul Spiegel
Mar 7 at 17:06
You have 4 tables. The first thing you do is to define the relationship between those tables. Read this. stackoverflow.com/help/how-to-ask
– Eric
Mar 7 at 17:41
Firing keys was an auto complete issue, should have been foreign keys. As for heavy lifting, it means hard work, I would like to use SQL to retrieve the data in 1 go, instead of getting the table type, then having a switch statement in PHP to choose which column for a second query. I will update my question with more information on how the tables are structured shortly.
– mike16889
Mar 7 at 23:46