Thread: Database table design

In SQL Server Management Studio, create a database called SurveyDatabase. If you don't want to use that name, change the first line in the below code to say "Use [yourdatabasename]. Then, click "New Query" and execute the following in that window:

Code:

Use [SurveyDatabase]
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Surveys]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Surveys](
 [SurveyID] [int] IDENTITY(1,1) NOT NULL,
 [Title] [varchar](100) NOT NULL,
 [Description] [varchar](255) NOT NULL,
 [DateCreated] [datetime] NOT NULL,
 CONSTRAINT [PK_Surveys] PRIMARY KEY CLUSTERED 
(
 [SurveyID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Questions]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Questions](
 [QuestionID] [int] IDENTITY(1,1) NOT NULL,
 [SurveyID] [int] NOT NULL,
 [Question] [varchar](255) NOT NULL,
 CONSTRAINT [PK_Questions] PRIMARY KEY CLUSTERED 
(
 [QuestionID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ClientAnswers]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[ClientAnswers](
 [QuestionID] [int] NOT NULL,
 [ClientID] [int] NOT NULL,
 [Answer] [varchar](255) NOT NULL,
 [EntryDate] [datetime] NOT NULL
) ON [PRIMARY]
END
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Questions_Surveys]') AND parent_object_id = OBJECT_ID(N'[dbo].[Questions]'))
ALTER TABLE [dbo].[Questions]  WITH CHECK ADD  CONSTRAINT [FK_Questions_Surveys] FOREIGN KEY([SurveyID])
REFERENCES [dbo].[Surveys] ([SurveyID])
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_ClientAnswers_Questions]') AND parent_object_id = OBJECT_ID(N'[dbo].[ClientAnswers]'))
ALTER TABLE [dbo].[ClientAnswers]  WITH CHECK ADD  CONSTRAINT [FK_ClientAnswers_Questions] FOREIGN KEY([QuestionID])
REFERENCES [dbo].[Questions] ([QuestionID])

This should create the database schema I showed you.

Originally Posted by Zaheir

The database schema earlier also consisted of an answers tables to store the answers can you incorporate a table answers into the script? Also I would like to know like for access you can see an entity diagram. Is there a way I can see in SQL server how the tables are linked? I think you created some linkage in the script that you helped me with.

Im new to SQL server. I appreciate your help

Thanks.

The ClientAnswers table is included in the above script.

To see the linking between tables, in SQL Server Management Studio, click the "Database Diagrams" node for the database. It will prompt you to create them, so click "Yes". After this you should be able to see all the table details and how they are linked.

If you're only ever going to need a multiple-choice style survey, then that will be easy. If you have other requirements, then it will be a little more difficult. Do you only need multiple-choice?

As for the database diagram, you need to click the folder and then you will get a prompt to create the diagram. Click "Yes" on this prompt.

Originally Posted by Zaheir

it will be all multiple choice but just many different surveys in which each survey has diff ques and many questions have different answer choices.

Hi, I don't mean to be rude, but allot of ideas have been given to help you achieve what you want?

Others here may disagree but i believe your employer may find it more beneficial paying a professional to do this.

It will certainly cost more by time in the long run for you to take weeks / months over. I assure you it will most likely end up a inferior application which may not even work.

In the mean time i sugest you practice your skills with something more simple.

So create a table called "Answers" with the following fields:

AnswerID int - PK
QuestonID int (related to Questions table)
Option varchar(100) (answer text to be displayed to user)

After you save this table, click the relationships icon and relate it to the AnswerID field in the ClientAnswers table.

I do agree with AOG, though. You really need to practice this. We aren't going to keep giving you the answers. You will have to learn it eventually.