Dynamic php SQL Server Quiz

Asked

Viewed 232 times

0

Hello, I am building a calibration tool (given a Checklist the user needs to select the fields that are in agreement (or not) with a link or script.

Ex.:
1)Colaborador inicia o atendimento conforme diretrizes.
a) conforme
b) não conforme
c) não se aplica.

At first the tool will allow you to select the field type ( radio,checkbox,select and text).
Currently I have a good part ready, the question is: what is the best way to structure my bank? How should I relate the answers and the questions? currently my tables are like this:

Form Table

CREATE TABLE [dbo].[formulario](
    [id] [varchar](13) NULL,
    [nome] [varchar](150) NULL,
    [descricao] [varchar](max) NULL,
    [pergunta] [varchar](150) NULL,
    [resposta] [varchar](150) NULL,
    [tipo] [varchar](100) NULL,
    [iPergunta] [int] NULL,//indice da pergunta
    [iResposta] [int] NULL,//indice da resposta
    [qtdPerguntas] [int] NULL,//total de perguntas
    [qtdRespostas] [int] NULL,
    [responsavel] [varchar](150) NULL,
    [criacao] [datetime] NULL,
    [atualizacao] [datetime] NULL
)

Response Table:

CREATE TABLE [dbo].[resposta](
    [formulario] [varchar](13) NULL,
    [pergunta] [varchar](150) NULL,
    [resposta] [varchar](150) NULL,
    [respondente] [varchar](150) NULL,
    [data] [datetime] NULL
)

I need another table for the Adm can tell which are the correct answers, but this structure does not seem appropriate.

sql sql-server

  • What are the fields for iPergunta and iResposta? By what to understand they serve to tell which question and answer the form. It is important to understand this so that the community can give a more appropriate response to you.

  • Good Thiago, they are the answer and the question, kind of an accountant.

  • From what I understand iPergunta would receive the id of the form question. But if the form has several questions, by the modeling that showed, I believe that it would not work.

  • No, the iPergunta is the index. see, suppose I entered 2 questions, the first with 3 answers and the second with two, the structure would be: Question 1: iPergunta 0 Answer to iResposta 0 Answer b iResposta 1 Answer c iResposta 2 Question 2 iPergunta 2 Answer to iResposta 3 Reply b iResposta 4

1 answer

2

I would create the model more or less as follows:

Form

CREATE TABLE [dbo].[formulario](
    [id] [int] NOT NULL, // chave primaria
    [nome] [varchar](150) NOT NULL,
    [descricao] [varchar](max) NOT NULL,
    [tipo] [varchar](100) NOT NULL,
    [responsavel] [varchar](150) NOT NULL,
    [criacao] [datetime] NOT NULL,
    [atualizacao] [datetime] NOT NULL
)

Answer

CREATE TABLE [dbo].[resposta](
    [id] [int] NOT NULL, // chave primaria
    [id_formulario] [int] NOT NULL, // chave extrangeira
    [id_pergunta] [int] NOT NULL, // chave extrangeira
    [id_opcao] [int] NOT NULL // chave extrangeira
)

Question

CREATE TABLE [dbo].[pergunta](
    [id] [int] NOT NULL, // chave primaria
    [pergunta] [varchar](150) NOT NULL
)

Option

CREATE TABLE [dbo].[opcao](
    [id] [int] NOT NULL, // chave primaria
    [id_pergunta] [int] NOT NULL, // chave extrangeira
    [opcao] [varchar](150) NOT NULL
)

By doing so, you could create a question with as many answer options as you want. And then in the table Resposta, you can relate several questions to a form, and record the option marked by the user.

  • So it looks much better, I need to think of a way to do this in the backend, because for my Adm that will insert the bank, will do the insertion as if it were the template creating form google Forms. I imagine with this structure, if each item was created separately, it would be much more laborious.

  • You can put the items together with the questions, but you will have a fixed amount of items and if you want a variety of items according to the question, you will have null fields. The way I did the modeling, it wasn’t gonna happen.

Browser other questions tagged sql sql-server

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