Dynamic Questionnaire php SQL Server

Question

Dynamic Questionnaire php SQL Server

Navigation

#1 by (2 votes)

0

Hello, I am building a calibration tool (given a checklist the user needs to select the fields that agree (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 deal 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 so that adm can tell what the correct answers are, but this structure does not seem appropriate.

sql sql-server

asked by anonymous 20.07.2018 / 22:30

1 answer

While ResultSet.next () returning only one result Store result of promise in a variable

score 2 · Answer 1

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
)

Response

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
)

In this way, you could create a question with as many answer options as you wanted. And then in the Resposta table, you can relate several questions to a form, and register the option checked by the user.