Viability to create 200 columns in database [closed]

2

I am planning software, which is a test of 200 questions where each question can only have 3 choices and select only one, and each question belongs to a class type (eg question 5 belongs to class B , are classes from A to J) and I need to save the result of each question in the database, Is it possible to create 200 fields for each question ? Ex:

Q1  |  Q2 |  Q3  |  Q4  ......

Or is there an easier way?

    
asked by anonymous 28.03.2016 / 20:35

3 answers

5

Considering the Here

You can create as follows.

There is not much secret, what you need and have your table with the questions and other with the options of the questions, another with User, as a user can do more than one test so you need another table HistoricoTeste and a table for Answers, with the above model you can retrieve the information that a user like this.

select * from HistoricoTeste ht
join Respostas r
on r.IdHistoricoTeste = ht.IdHistoricoTeste
where ht.IdUsuario = 1 -- usuario
and r.IdPergunta = 1 -- pergunta 
  

This is a template made in SQL SERVER.

    
28.03.2016 / 22:18
5

You can even do this but hardly anyone would do it in their right mind. In a rough way the correct one would be:

CREATE TABLE Resultado (
    ID int PRIMARY KEY,
    Usuario int,
    Questao int,
    Resposta int,
);

I put everything int for lack of a better information on how this would be composed. Obviously it would be interesting to have a user table and one of the questions to reference.

Some people would rather not have ID and use the combination of Usuario + Questao as the primary key.

Obviously if you have a reason to create 200 columns, you can do it too, but you need to make sure it's the best option, you have to know how to justify this. I doubt it is.

    
28.03.2016 / 20:56
3

It seems kind of silly to post this, but a basic logical structure would look something like this:

# Todas as perguntas
tabela perguntas
 - id (único)
 - descricao

# Todas as respostas, independente da pergunta
tabela respostas
 - id (único)
 - descricao

# Quais respostas estarão disponíveis para cada pergunta
tabela perguntas_respostas
 - id (único)
 - perguntas_id
 - respostas_id

# Todos os usuários
tabela usuarios
 - id (único)
 - nome

# Quais respostas que o usuário escolheu relacionado com a pergunta: 
tabela usuarios_respostas
 - id (único)
 - perguntas_id
 - respostas_id
 - usuarios_id

Then you would create a CRUD to manage questions and answers

But it's not clear what you want to do. There are different ways to solve. It does not mean that the suggested example is ideal and not the best.

CRUD (Create, Read, Up, Delete)     

28.03.2016 / 21:06