Survey database design

[xili]

Hi, every guru,

I was asked to design a medical/mental questionaire database with total more than 1500 questions by using MS Access. each subject should go through all questions, and all answers should be stored in db for later review. what I am doing is to create a different form for each section of questionaire(total is 7 sections). and since one table only have max 255 fields, so I create many tables to hold these questions(columns) and answers(rows) and links them by 1 to 1 relationship.

After I read the forum posts, I think maybe I have a bad survey db strucure design. since I am new to Access, it is a simple way for me to create a form and stored user entry into tables. But I am worried about future queries if needed. and also the DB has a lot of tables. but I have a very friendly user interface form to let interviewer to enter data into tables.

what is the best way to desigh the structure and tables? many thanks if someone can help or direct me!

attach is my db screen shot(form and relationship)

 

[ecniv]

Hi,

ok so you want a questionaire plus login (to store user who completed) and probably some on screen stats reports for upper managment (outputs you can deal with later).

First up you will need a login/register form to get peoples details. These are the usual suggested fields.

• UserID (autonumber)
• Logon
• Forename
• Surname
• Password
• IsDeveloper (Y/N)

Then on to the next bit. If you think ahead a bit you'll soon realise that a carefully crafted design will allow you to have loads of questionaires, but with the one design, and loads of responses too.

It depends whether you need company etc.. but this bit should definately have:
tblQuestionaire, tblQGroup, tblQuestions, tblOptions, tblAnswers


How about posting up the fields you know of and how you'd group them together. And think how to make it flexible. As to 1500 questions.. are they text, or multichoice?


Vince

 

[Mile-O]

Do a search on Survey or questionairre as this is a popular topic. I even answered a post on it today in the General forum.

 

[xili]

Thanks Vince and SJ for your reply.

Actually, I am not using the tblQuestins, tblSubject, and tblAnswers etc. as you told me. I hate myself not to join this forums early to get some good ideas from others for my survey db design. The big problem is I already typed out all question texts on the form and linked each answer box to the tables by using query.

Anyhow, here is my table design:
Table: Table1
--------------------------------------
FieldName: SubjectID [Primary Key]
FieldName: Question1
FieldName: Question2
FieldName: Question3
.
.
.
FieldName: QuestionN (less then 255)

Table: Table2
--------------------------------------
FieldName: SubjectID [Primary Key]
FieldName: Question1
FieldName: Question2
FieldName: Question3
.
.
.
FieldName: QuestionN;

.......

Here I will have one record in all tables for each person who fills out the questionnaire. and all tables will hold the answers. The type of answers will be Yes/No/UK, multiple choice, fill-in-the-blank, comments etc.

Since I'v done all questions typing(more than 1500 questions) and link them to the table. If I change the sturcture of db design, I will have to retype it again that is what I hate to do. Is there anyway I can continue to use this design and will not make any painful work for db management or data retrival in the future?

Any HELP would be highly appreciated. Thanks.

A desperate guy.

 

[monkeytunes]

Is there anyway I can continue to use this design and will not make any painful work for db management or data retrival in the future?

The short answer = No.

The long answer = You need to look up "normalization", both on these boards and on Google. Regrettably, MS Access Help doesn't even MENTION normalization. If you doubt how important it is, check this thread and pay special-super-duper attention to the comments from Pat Hartman and The Doc Man, especially Pat's dressing down of yours truly. That thread grew as I started asking more and more questions about surveys and the Real World (i.e. What users see and use) versus the Relational Database World (i.e. What you, as administrator, have to do, have to show, have to write and maintain, etc.). When you start giving favors to the Real World, eventually you have to pay the piper.

The snarky answer = Yes, if you ask Mike375. (ZZZING!)

 

[Pat Hartman]

xili, it looks like you're looking for someone to validate your present design and you may actually find someone but not likely a senior member. Your best bet is to bite the bullet now and say "wow! if I had found this out next week, look how much more work I would need to redo". Don't put off the inevitable. Read the many survey designs posted here. One of them is probably close to what you need. Redesign you db so that the tables are normalized. You haven't a clue how difficult your present structure will be to get any information out of. For starters, you'll be writing 1500 queries to analyze the results for each question (I know that Mike would think this was fine, but the rest of us don't). I'm sorry that monkeytunes regards my post as a dressing down because it was meant to give him some idea of what was in store for him if he elected to stick with his "flat" structure. I wanted to frighten him and apparently I did. You are in for much more of a problem than monkeytunes was.

 

[monkeytunes]

I'm sorry that monkeytunes regards my post as a dressing down because it was meant to give him some idea of what was in store for him if he elected to stick with his "flat" structure. I wanted to frighten him and apparently I did.

No, Sarge, it was a good dressing down. Exactly the sort of wake-up call pig-headed newbies like me need sometimes. In fact, based on tons of suggestions from you, Doc Man, KenHigg and Kevin_S, this weekend I've restructured the entire contractual DB we'd been using, and while I was at it, put in an entire new navigational scheme which is looking really slick. As usual, I owe it all to these forums. I put the first beta-version in front of the users tomorrow morning...

 

[xili]

Thank you, Pat Hartman. you are definitely an experienced db expert. since I search this forum with survey or questionaire database and read more threads discussing survey design. I decide to redo my job(although it's painful right now ), it will be paid off in the future.

Pat, I saw you said you have a sample survey db in a repsonse of one thread. Would you mind sending me a copy so I can have a better understanding with tables design. I will send you an email with the words "Send Survey Sample DB" in the subject.

Thanks for you help. I appreciate it.

xili

 

[Pat Hartman]

Sorry, I never finished removing the client specific stuff. The db is not in any shape to send.

 

[xili]

Anyway, thank you Pat. I found a db structure at this forum, will use it to design my survey. but I still got many questions to ask for the form design. I'll post it under FORM. If you don't mind to give me further guidance, I would appreciate it.