Database Design

Hi,
I'm a newbie, how do i created a table for the survey data.
Given the questions for each user:
Do you Smoke ? Answer Yes
If Yes, How many times Answer 3
Do you take alcohol? Answer No
How will i create a table for this.
Do i do like this
UserName Do_You_Smoke How_Many_Times Alchohol
User1 yes 3 No
I want to create a report from this survey and Pull up the data for each
user on the form again for modification
Please Help
There are a lot of possible answers to this question. It really depends on
what your business needs are. I would assume you're going to want to change
the survey often, so you might want to consider something like this:
Question
======
QuestionKey
Question
BeginDate
EndDate
Answer (You might actually want to have several of these, depending on
complexity)
=====
AnswerKey
Answer
BeginDate
EndDate
QuestionAnswerMatrix (Match available answers where needed.)==============
QuestionKey
AnswerKey (AnswerKey could be blank for free text answers if you have those.)
BeginDate
EndDate
User
====
UserKey
UserName
etc, etc, etc
UserAnswers
=========
UserKey
QuestionAnswerKey
SurveyDate
This should at least give you some ideas. The most important thing in
database design is making sure the design is strong, normalized and yet meets
the needs of the business. If you haven't even asked the questions, there's
no way to design well. Based on the design above though, you should be able
to create stored procedures that easily report on and modify the data for the
surveys, as well as track historical survey "template" changes.
"Givosky" wrote:

> Hi,
> I'm a newbie, how do i created a table for the survey data.
> Given the questions for each user:
> Do you Smoke ? Answer Yes
> If Yes, How many times Answer 3
> Do you take alcohol? Answer No
> How will i create a table for this.
> Do i do like this
> UserName Do_You_Smoke How_Many_Times Alchohol
> User1 yes 3 No
> I want to create a report from this survey and Pull up the data for each
> user on the form again for modification
> Please Help
|||If your intention is actually to record information about each user then
something like this might be appropriate:
CREATE TABLE Users (username VARCHAR(30) NOT NULL, smokes_per_day INTEGER
NOT NULL CHECK (cigarettes_per_day>=0), alcohol_consumption INTEGER NOT NULL
.... etc)
I'm guessing that the Yes/No answers are redundant. They would just drive
the presentation in the GUI rather than be actual useful information (for
example cigarettes_per_day = 0 implies "I don't smoke"). On the other hand if
you need something more generic to handle many different types of survey you
might take a quite different approach.
David Portas
SQL Server MVP
|||Thanx for that useful information
"MeanOldDBA" wrote:
[vbcol=seagreen]
> There are a lot of possible answers to this question. It really depends on
> what your business needs are. I would assume you're going to want to change
> the survey often, so you might want to consider something like this:
> Question
> ======
> QuestionKey
> Question
> BeginDate
> EndDate
> Answer (You might actually want to have several of these, depending on
> complexity)
> =====
> AnswerKey
> Answer
> BeginDate
> EndDate
> QuestionAnswerMatrix (Match available answers where needed.)==============
> QuestionKey
> AnswerKey (AnswerKey could be blank for free text answers if you have those.)
> BeginDate
> EndDate
> User
> ====
> UserKey
> UserName
> etc, etc, etc
> UserAnswers
> =========
> UserKey
> QuestionAnswerKey
> SurveyDate
> This should at least give you some ideas. The most important thing in
> database design is making sure the design is strong, normalized and yet meets
> the needs of the business. If you haven't even asked the questions, there's
> no way to design well. Based on the design above though, you should be able
> to create stored procedures that easily report on and modify the data for the
> surveys, as well as track historical survey "template" changes.
>
> "Givosky" wrote:

Database design

Hi,

I need a hand with designing a database.

I am collecting results from a survey which has the following questions:

Call ref?

How did you place your support call?

Were you satisfied with the amount of time you had to wait until getting acknowledgement of the support call placed?
1 = very satisfied and 10 = very unsatisfied.

How happy were you with the customer service you received upon placing the support call?

1 = very unhappy and 10 = very happy.
How satisfied were you with the amount of time you had to wait until you heard from an engineer?
1 = very satisfied and 10 = very unsatisfied.

How satisfied were you with the time taken to get your problem/query resolved?
1 = very satisfied and 10 = very unsatisfied

Did you feel the engineer had enough knowledge to deal with your call?
1 = very good and 10 = not very good

Overall how satisfied were you with the support call placed?
1 = very satisfied and 10 = very unsatisfied
Is there anything we can do to improve the quality of the support and service you received?

I want to store this in a database. Obviously I want to use best practice for design, normalisation etc. The stumbling block I am coming accross is the fact that each question has a number and each question has a score from 1 to 10 and storing this in the database. Any help appreciated!

Thanks

Andrew

Hi Andrew,

Each question would appear as a separate column. In that column, you would store one value, which is numeric between 1 and 10. With questions like "How did you place your support call", you would provide a dropdownlist of options - each with a key value, and save the key value as an integer. If you offer questions that allow free text answers, you have a choice of text/memo in Access, depending on whether you allow people to submit more than 255 chars, or nvarchar in Sql Server. Personally, I would create a separate table for this type of response, (because most people don't provide answers in free text), and link each response to its parent in the main responses table.

|||

I am using SQL server. I was wondering if there was a way I could make it so I could use the same database table with different survey questions.

So for example, I might have a separate table for questions with a question ID and description, so I could add new questions.

Thanks for the response. Do you have any thoughts on the above?

Thanks

Andrew

|||You could re-use the answers table. No reason why not. You would obviously need a column that holds the identity of each survey. And you would need to be fairly sure that each survey will have a similar question structure. What you might end up with if you are not careful is having to add columns such as Q1a, Q1b, Q1c etc, because someone came up with a questionnaire that had a different structure. You don't want to end up with too many columns that have empty data.|||

I have created one atwww.abtecnet.com/survey.aspx . Is there any software / code / control I can use to display a bar graph of the results?

Thanks

Andrew

|||Crystal Reports is what you are seaching for|||I only want a simple thing. There seem to be a few options about using the vb drawing class?|||

ajw:

I have created one atwww.abtecnet.com/survey.aspx . Is there any software / code / control I can use to display a bar graph of the results?

I would recommend swapping the values so that 1 = poor and 10 = excellent. That's normally the way people are asked to grade things, and your reversal of this may confuse users.

With the charts, there are lots of tutorials on using GDI. I think there may be a new one on the Articles listing on the home page pf www.asp.net right now.

|||

Well spotted. I didnt actually write the questions. I have changed them now so they are a bit more logical .

Cheers

Andrew

|||

I have this code here and I want to populate the array in the page_load event with items from my database. I have tried making a dataset and various other ways but cant seem to get it to do want i want. Any ideas? Thanks Andrew

Imports System.Data
Imports System.Drawing
Imports System.Drawing.Imaging

Public Class Graphics_Barchart
Inherits System.Web.UI.Page

#Region " Web Form Designer Generated Code "

'This call is required by the Web Form Designer.
<System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()

End Sub

Private Sub Page_Init(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Init
'CODEGEN: This method call is required by the Web Form Designer
'Do not modify it using the code editor.
InitializeComponent()
End Sub

#End Region

Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
'Put user code to initialize the page here
Dim aMonths As ArrayList = New ArrayList(), aProfits As ArrayList = New ArrayList()
aMonths.Add("Question1")
aMonths.Add("Question2")
aMonths.Add("Question3")
aMonths.Add("Question4")
aMonths.Add("Question5")
aProfits.Add("1")
aProfits.Add("3")
aProfits.Add("2")
aProfits.Add("3")
aProfits.Add("4")
aProfits.Add("5")
DrawBarGraph("Survey Results", aMonths, aProfits)
End Sub

Sub DrawBarGraph(ByVal strTitle As String, ByVal aX As ArrayList, ByVal aY As ArrayList)
Const iColWidth As Integer = 60, iColSpace As Integer = 25, _
iMaxHeight As Integer = 200, iHeightSpace As Integer = 25, _
iXLegendSpace As Integer = 30, iTitleSpace As Integer = 50
Dim iMaxWidth As Integer = (iColWidth + iColSpace) * aX.Count + iColSpace, iMaxColHeight As Integer = 0, iTotalHeight As Integer = iMaxHeight + iXLegendSpace + iTitleSpace

Dim objBitmap As Bitmap = New Bitmap(iMaxWidth, iTotalHeight)
Dim objGraphics As Graphics = Graphics.FromImage(objBitmap)

objGraphics.FillRectangle(New SolidBrush(Color.White), 0, 0, iMaxWidth, iTotalHeight)
objGraphics.FillRectangle(New SolidBrush(Color.Ivory), 0, 0, iMaxWidth, iMaxHeight)

' find the maximum value
Dim iValue As Integer
For Each iValue In aY
If iValue > iMaxColHeight Then iMaxColHeight = iValue
Next

Dim iBarX As Integer = iColSpace, iCurrentHeight As Integer
Dim objBrush As SolidBrush = New SolidBrush(Color.FromArgb(70, 20, 20))
Dim fontLegend As Font = New Font("Arial", 11), fontValues As Font = New Font("Arial", 8), fontTitle As Font = New Font("Arial", 24)

' loop through and draw each bar
Dim iLoop As Integer
For iLoop = 0 To aX.Count - 1
iCurrentHeight = ((Convert.ToDouble(aY(iLoop)) / Convert.ToDouble(iMaxColHeight)) * Convert.ToDouble(iMaxHeight - iHeightSpace))
objGraphics.FillRectangle(objBrush, iBarX, _
iMaxHeight - iCurrentHeight, iColWidth, iCurrentHeight)
objGraphics.DrawString(aX(iLoop), fontLegend, objBrush, iBarX, iMaxHeight)
objGraphics.DrawString(Format(aY(iLoop), "#,###"), fontValues, objBrush, iBarX, iMaxHeight - iCurrentHeight - 15)
iBarX += (iColSpace + iColWidth)
Next
objGraphics.DrawString(strTitle, fontTitle, objBrush, (iMaxWidth / 2) - strTitle.Length * 6, iMaxHeight + iXLegendSpace)
'objBitmap.Save("C:\inetpub\wwwroot\graph.gif", ImageFormat.GIF)
objBitmap.Save(Response.OutputStream, ImageFormat.Gif)
objGraphics.Dispose()
objBitmap.Dispose()
End Sub

End Class