Database for the Poll

Asked

Viewed 880 times

2

I’m doing a questionnaire system and I’m having a hard time modeling the database. I need my system to be "smart" enough to ask different questions according to the answers.

For example, my first question will always be:

You are a?

  • Man
  • Female

If the answer to question ID 1 is: Man. I will ask question ID 2:

Of the items below, which you are most interested in?

  • Sports
  • Video Games
  • Travels

When answering question ID 2 with Sports, I will ask question ID 3.

What sport?

  • Football
  • Racing
  • Struggle

Now back to question ID 1 in another scenario. If the answer is Woman. I will ask question ID 2:

Of the items below, which you are most interested in?

  • Sports
  • Gastronomy
  • Travels
  • Aesthetic

If the answer is Travel I will ask the question ID 4

Which of these destinations would you like to know?

  • Dubai
  • China
  • Paris

Understand that I have equal questions with different answers. And the combination of a question with an answer should determine what the next question will be, I have questions that will be displayed only in certain situations. And the same question may contain different answers depending on the answer to the previous question.

database modeling
  • 1

    Do you have anything you’ve already done? It shows there. You essentially need to have one or more columns that determine(m) dependency. This column(s) needs(s) to indicate what is the question and the answer that needs to have been answered for that question to be asked. It is possible to do this in the answers as well, but I find it unlikely that it is useful to modify the possible answers, it is easier to choose only which question is conditional. Just like you’re doing. If you have more complex situations to determine which questions will be activated, then it complicates well and only with more details to answer.

1 answer

2

Alberto, although two questions have exactly the same text and from the point of view of the user they are the same, at the level of database they do not need to be the same question.

Schema Quiz

Follow the table creation script for SQL-Server:

CREATE SCHEMA [quiz]
GO
/****** Object:  Table [quiz].[Pergunta]    Script Date: 1/13/2016 9:06:19 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [quiz].[Pergunta](
    [PerguntaID] [int] IDENTITY(1,1) NOT NULL,
    [Descricao] [varchar](255) NOT NULL,
 CONSTRAINT [PK_Pergunta] PRIMARY KEY CLUSTERED 
(
    [PerguntaID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [quiz].[Resposta]    Script Date: 1/13/2016 9:06:19 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [quiz].[Resposta](
    [RespostaID] [int] NOT NULL,
    [PerguntaID] [int] NOT NULL,
    [Descricao] [varchar](255) NOT NULL,
    [PerguntaProximaID] [int] NULL,
 CONSTRAINT [PK_Resposta] PRIMARY KEY CLUSTERED 
(
    [RespostaID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
/****** Object:  Index [IX_Resposta_PerguntaID]    Script Date: 1/13/2016 9:06:19 AM ******/
CREATE NONCLUSTERED INDEX [IX_Resposta_PerguntaID] ON [quiz].[Resposta]
(
    [PerguntaID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
/****** Object:  Index [IX_Resposta_PerguntaProximaID]    Script Date: 1/13/2016 9:06:19 AM ******/
CREATE NONCLUSTERED INDEX [IX_Resposta_PerguntaProximaID] ON [quiz].[Resposta]
(
    [PerguntaProximaID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
ALTER TABLE [quiz].[Resposta]  WITH CHECK ADD  CONSTRAINT [FK_Resposta_Pergunta] FOREIGN KEY([PerguntaID])
REFERENCES [quiz].[Pergunta] ([PerguntaID])
GO
ALTER TABLE [quiz].[Resposta] CHECK CONSTRAINT [FK_Resposta_Pergunta]
GO
ALTER TABLE [quiz].[Resposta]  WITH CHECK ADD  CONSTRAINT [FK_Resposta_Pergunta_Proxima] FOREIGN KEY([PerguntaProximaID])
REFERENCES [quiz].[Pergunta] ([PerguntaID])
GO
ALTER TABLE [quiz].[Resposta] CHECK CONSTRAINT [FK_Resposta_Pergunta_Proxima]
GO

If you prefer, the Table Pergunta may have a column ProximaPerguntaID which will serve as default value for the column ProximaPerguntaID table Resposta is not informed.

Browser other questions tagged database modeling

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