Possibility to create 200 columns in a database

Asked

Viewed 218 times

2

I am planning a software, which is a test of 200 questions where each question can only have 3 choices and select only one of them, and each question belongs to a class type ( for example 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, it is feasible to create 200 fields for each question? Ex:

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

Or is there some easier way?

mysql sql database postgresql oracle
  • 2

    Why do you think you need 200 fields for each question only has 3 options? I think you’re confusing things. I don’t understand this class business. What result? Will you have another table with the answers from whom? What are its requirements? It’s all too weird to tell you what to do. I find little or nothing relevant, but which database will actually use? Either choose one or leave without specifying.

  • The classes don’t matter, for example: each question or you can select integer or real or null, right ? but there are 200 questions , and I need to save the result of each question and then show it on another system , show all the user’s answers ,so I thought about creating 200 fields like : Tbl_answer :Usergroup | Q1 | Q2 | Q3 | Q4... Q200 <- Fields of a table , I wonder if this is a feasible possibility to save user responses

  • 1

    you want to know how to model in the bank? I think you don’t need 200 fields for each question.. a field (column) already solves..

  • Daniel, I would like to do something like this : http://imgur.com/gobzOMY because I need to save the answers of several users , got it ? so I wonder if making a field for each question ,up to 200 is feasible

  • well, the name of it is "data modeling".. As I mentioned above, partocularmente, from what I understand, I see no reason to create 200 columns. A table with a column only already solves to register the questions and relate them to other tables. I posted answer describing a summary.

  • 1

    I remembered this comic http://vidadeprogramador.com.br/uploads/2016/03/tirinha1545.png. , nothing personal.

  • @Marconciliosouza again saying, the tag added is irrelevant to the question

Show 2 more comments

3 answers

6

You can do this but hardly anyone would do it in their right mind. Roughly speaking, the right thing would be:

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

I put in the Github for future reference.

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

Some people would rather not have the ID and use the combination of Usuario+Questao as a 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 be able to justify it. I doubt it’s.

5


Given the issue Here

You can create as follows.

inserir a descrição da imagem aqui

There is not much secret, what you need and have your table with the questions and others with the question options, another with User, as a user can make more than one test so you need another table Historicoteste and a table for the Answers, with the above model you can recover the information 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 model made in SQL SERVER.

  • Only one call was missing from Questions for Optionsperguntas 1:N

3

It seems kind of silly to post this, but a basic logical structure would be 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

inserir a descrição da imagem aqui

Then you would create a CRUD to manage the questions and answers

But it is not clear what you intend to do. There are various ways to solve it. It does not mean that the suggested example is ideal, nor is it the best.

CRUD (Create, Read, Updade, Delete)

Browser other questions tagged mysql sql database postgresql oracle

You are not signed in. Login or sign up in order to post.