Need help with database structure for online survey.

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
sillywilly
Forum Newbie
Posts: 19
Joined: Thu May 02, 2002 5:11 pm

Need help with database structure for online survey.

Post by sillywilly »

I would be very thankful if someone could assist me in designing a database structure that will be used for my online questionnaire, found at: http://www.gamemore.co.uk/questionnaire/survey.php

Below is an idea of what I had in mind.

questionnaire_section_1
S1Q1
S1Q2_1
S1Q2_2
S1Q2_3
userid

questionniare_section_2
S2Q1
S2Q2
S2Q3
userid

The problem is that not all sections of the questionnaire will necessarily be filled in, thus when a write occurs not all rows will be updated. Thus will this affect the auto increment?

Also I wish to assign the person filling in the questionnaire a unique id that they will use to identify themselves at our gaming events. Thus does anyone have a suggestion of how I might create this, for example I could used letters form their name and the auto increment the end: so andrew would be and01 or something like that.

If you could take a look at the questionnaire itself: http://www.gamemore.co.uk/questionnaire/survey.php and give your opinions on a database structure I would be forever in your debt.

Kindest Regards

Andi
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post by volka »

i would limit it to simply

Code: Select all

CREATE TABLE `answers` (
  `questionId` tinyint(3) unsigned NOT NULL default '0',
  `userId` mediumint(8) unsigned NOT NULL default '0',
  `answer` tinyint(3) unsigned NOT NULL default '0',
  PRIMARY KEY  (`questionId`)
)
MattF
Forum Contributor
Posts: 225
Joined: Sun May 19, 2002 9:58 am
Location: Sussex, UK

Post by MattF »

Surely that code would only mean that each question could be asked only once.
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post by twigletmac »

Why not have everything in one table with userid as the primary key?

Mac
sillywilly
Forum Newbie
Posts: 19
Joined: Thu May 02, 2002 5:11 pm

Post by sillywilly »

Thank you very much for your reply.

Working off your suggested structure i would have:

Table questionnaire

questid answer userid date
S1Q1 0 1 date
S1Q2 1 1 date
S3Q3 2 1 date
S1Q4 3 1 date
S2Q6 and so on...

I gather my understanding is correct? I understand how the answer value is inserted but not how would retrieve and insert the questid.

My understanding is you would have something like:

("insert into questionnaire (questid, answer, userid, date) values ('$notsure', '$S1Q2, and so on'...)

This kind of insert obviously wouldn't work. How do i go about getting the questid value as the questid's are just name fields within the forms of the questionnaire, they are not values. Thus $value wouldn't work.

Also the answer field will have both numerical an a string values. Most will be either 0, 1, 2 and and so on but also answers will store email, firstname, and surname. Thus what should i identify answer as...INT???

I will not be forcing people to sign up to fill in the questionnaire, but they will be required to enter their firstname, surname and email, once they have completed the questionnaire, this is so that every entry into the database can be validated as being unique. The data collected by the questionnaire will be used to show to potential investors thus they will require proof that every entry is unique. Investor people tend to be a bit concerned when primary research is gathered via the internet.

To claim discounts off our events and to get free entry we will need to give people a unique id of some sort. This id will be emailed to them once they have completed the questionnaire.

Thus some sort of auto-generated alphanumeric number would be ideal. How would i go about this? Thus the structure would have to change i imagine as i would need to only store this id once and not many time, as would happen in the structure already present.

Do you see what i'm going on about.

I think i have now totally confused myself.
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post by twigletmac »

I think I'd be more confused if I hadn't read this same post at the devshed forum.

Mac
sillywilly
Forum Newbie
Posts: 19
Joined: Thu May 02, 2002 5:11 pm

Post by sillywilly »

Well i didn't want you guys missing out on all the excitment....hehe Its just this is driving me nuts. This project is my first real programming experience using PHP and mysql and i'm kinda on a deadline to get it finished so that i can start collecting data.

My business partner is a hardware guy thus no help at all...hehe
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post by volka »

MattF: why can the questions be asked only once?
Post Reply