0

I am creating a sql database with a table holding questionnaire answers. The questions are full sentences (about 150 characters each) and I want to know what is the best method for maintaining that information as the fields. I am till new to SQL, but I see two options:

  1. set each question as a number (1, 2, 3, 4...) and have a separate table holding the actual questions as the data that links to the number in the first table.

  2. some method in CREATE TABLE that lets you set the field as a sentence. I though quotes would work, but they do not.

EDIT:

a quick example of what i am trying to do:

CREATE TABLE survey(
    index_id INT PRIMARY KEY,
    'between 1 and 10, how do you feel about the transparency of the scientific community?' VARCHAR(5)
);

Thanks!

Dane Kania
  • 45
  • 7
  • are you going to need to do anything but store and retrieve the answer? do any analysis on it? parse it out etc? IS the 150 characters a hard limit or could a user provide several sentences/paragraphs and you would need to store it all? As far as allowing special characters in the data: use paramaterized queries to insert/update so those are safely handled. – xQbert Nov 06 '18 at 22:35
  • If you're having trouble with quotes you're having trouble with escaping. You should use **prepared statements with placeholder values** whenever inserting in your application layer. If you're inserting textual data into MySQL and you don't have any scripting language to back you up, consider using CSV as an import format as that works well with MySQL's [`LOAD DATA INFILE`](https://dev.mysql.com/doc/refman/8.0/en/load-data.html) which can [easily accommodate CSV](https://stackoverflow.com/questions/14127529/mysql-import-data-from-csv-using-load-data-infile). – tadman Nov 06 '18 at 22:36
  • 1
    Manual [Link](https://dev.mysql.com/doc/refman/8.0/en/sql-syntax-prepared-statements.html) for Prepared Statements – xQbert Nov 06 '18 at 22:37
  • thanks for the quick response, Yes, i want to be able to query the fields (the questions in the questionnaire). Ithe 150 was just an example, but specifically my question is about the field name, not any of the data inside. The data os mostly "yes/no" answers. – Dane Kania Nov 06 '18 at 22:45
  • You want the field name to be your question??? That makes no sense. I don't think you are understanding databases correctly. One field name would be question. Another field name would be answer. The questions and answers go inside the data in the table. – dmikester1 Nov 06 '18 at 22:50
  • If you are having multiple answers to the same question(like from many users), then you would likely need 2 tables. A questions table and an answers table that are linked with a foreign key. – dmikester1 Nov 06 '18 at 22:57
  • my original idea was that each field in a table would be a question and then each answer would be the data of that field. Bu two tables makes more sense it seems. Thanks! – Dane Kania Nov 06 '18 at 22:58
  • Yep, you never store data in fields. Data goes in the table under the appropriate fields. Questions, Answers, UserID, etc – dmikester1 Nov 07 '18 at 12:16
  • So your Answers table would have a QuestionID field that would link back to the Questions table. So you could have 10 answers that all have the same QuestionID because they are for the same question. And I'm guessing you might want a Users table as well. So then each of those 10 answers would have a different UserID pointing to the Users table. – dmikester1 Nov 07 '18 at 12:19

2 Answers2

1

You are mixing up the data in a table and creating the table.

When you create the table you define the structure of the table

Then you can add data to the table

Then you can query the table.

So for example create a table.

create table questionanswer (
  questionnumber integer,
  answer varchar(200)
)

add data to the table

insert into questionanswer (questionnumber, answer)
   values (1, 'election day')

query the table for values

select answer
from questionanswer
where questionnumber = 1
Hogan
  • 69,564
  • 10
  • 76
  • 117
  • @tadman - I've no idea what you mean. I was trying to explain how to use string constants to someone who was clearly lost with a prior version of the question – Hogan Nov 06 '18 at 22:53
  • Fair enough. Just seemed like you were advocating putting in quotes and everything would be fine. – tadman Nov 06 '18 at 23:45
0

Generally using VARCHAR(255) with encoding utf8mb4 is a good default. If you need long-form data, like essays, multiple paragraphs, etc. then use TEXT or LONGTEXT.

This is really a one-table problem:

CREATE TABLE questions (
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  questionnaire_id INT NOT NULL,
  num INT NOT NULL DEFAULT 0,
  question VARCHAR(255) NOT NULL
);

Where if you want you can have multiple questionnaires by adding another questionnaire table, or just use that number as-is for partitioning the questions.

tadman
  • 208,517
  • 23
  • 234
  • 262
  • Okay, so my first guess was a good method? have a table holding the questions as data and link it to the original table using a key? – Dane Kania Nov 06 '18 at 22:57
  • Yes, you're getting it. That's the basics of relational database design. – tadman Nov 06 '18 at 23:44