Database for Poll

Question

Database for Poll

Navigation

#1 by (2 votes)

2

I'm doing a questionnaire system and I'm having trouble modeling the database. I need my system to be "smart" to the point of asking different questions according to the answers.

For example, my first question will always be:

Are you one?

Man
Woman

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

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

Sports
Video Games
Travel

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

What sport?

Football
Race
Fight

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

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

Sports
Gastronomy
Travel
Aesthetics

If the answer is Travel I will ask question ID 4

Which of these destinations would you like to see?

Dubai
China
Paris

Notice 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, that is, I have questions that will only appear in certain situations. And the same question may contain different answers depending on the answer to the previous question.

modelagem

asked by anonymous 13.01.2016 / 12:53

1 answer

How to generate default values with JPA hibernate [closed] Save IEnumerableT inside a parent entity with LINQ

score 2 · Answer 1

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

Here is 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 Pergunta table may have a ProximaPerguntaID column that will serve as the default value if the ProximaPerguntaID column of the Resposta table is not entered.