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