New Database Design Suggestions

User avatar
Steve_in_Kent
4StarLounger
Posts: 408
Joined: 04 Feb 2010, 11:46

New Database Design Suggestions

Post by Steve_in_Kent »

Just about to start a new db. suggestions on how to implement, greatly appreciated..


This is how i have it in my head at the moment...


30 odd sets of questions, around 70 questions per set. Idea is:-

Each set of questions, stores as a table, eg: Question set 1. Question Set 2.

Data table with those questions, PLUS the responses, (Y/N,. plus text info)

Start a new question set, by copying the question set selected, into, the Data table, then gather the responses.

Ideally, i want to run this from a windows tablet, and then semi-automate the process. so that tablet runs the access program, collects the responses, plus any photographs, when the tablet plugs into a pc, OR send the data to a usb stick, i want the same access program on the tablet, to copy over that question set, along with the renumbered photographs, to a directory on a shared drive. (from the pc, or the USB stick)

The PC version of the access program, will poll a specific directory, then read the shared drive, looking for the file, to read into the database, along with the photographs.
Each photograph, will be renumbered by the tablet, to correspond with the specific Question Set, question, and Date - to link the picture(s) with the question.
----------------------------------------------------------------------------------------
Owing at LEAST 34 beers to other helpful forum members. Especially HansV!

User avatar
HansV
Administrator
Posts: 68783
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: New Database Design Suggestions

Post by HansV »

Unless the three question sets have a different structure, I'd store them in a single table:

S3381.png
S3382.png

I'd also create a table with the names of the question sets:

S3383.png
S3384.png

Depending on how you want to set up the questionnaires, you may want to add extra fields, such as response type and possible responses.

When the user selects a question set, you copy the QuestionIDs of the corresponding questions to the Data table.

Does that help?
You do not have the required permissions to view the files attached to this post.
Regards,
Hans

User avatar
StuartR
Administrator
Posts: 10907
Joined: 16 Jan 2010, 15:49
Location: London, Europe

Re: New Database Design Suggestions

Post by StuartR »

Could one question (such as "What is your name?") appear in multiple question sets? If so then you may want a more complex structure.
StuartR


User avatar
HansV
Administrator
Posts: 68783
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: New Database Design Suggestions

Post by HansV »

That's a good point, Stuart. How to handle it depends on the question sets - it might be worth making the structure more complex, or not...
Regards,
Hans

User avatar
Steve_in_Kent
4StarLounger
Posts: 408
Joined: 04 Feb 2010, 11:46

Re: New Database Design Suggestions

Post by Steve_in_Kent »

Thanks Guys.. yes, i realise i'm not doing things optimally here.. however, data optimisation is definately , not my forte....

There will be multiple questions that are the same, in different question sets. there are 30 question sets.. and the same question might appear 10 times out of the 30.
also, the person responsible for answering the question, will be different for some of the questions. how do i manage that.

What i am worried about is database size... the 70 questions are about 80-90 chars per question. i'm going to copy those questions, each time i do the 'survey' into the main data table.. then add the answers into that table.. (which will easily allow me to query the single data table with ease)

I realise for Efficiency sake, its way better to store each question, in a question table, then, create another table for each survey, which has the question to be asked as a numeric id.. then do the lookup on the form.... then add those responses to the data table.

am i making sense
----------------------------------------------------------------------------------------
Owing at LEAST 34 beers to other helpful forum members. Especially HansV!

User avatar
HansV
Administrator
Posts: 68783
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: New Database Design Suggestions

Post by HansV »

also, the person responsible for answering the question, will be different for some of the questions. how do i manage that.
Do you mean that within one questionnaire some of the questions will be answered by one person, and other questions by someone else?
Regards,
Hans

User avatar
Steve_in_Kent
4StarLounger
Posts: 408
Joined: 04 Feb 2010, 11:46

Re: New Database Design Suggestions

Post by Steve_in_Kent »

Yes, the same question, could have multiple people responsible.. but i'm sure i can find a way.

I am restructuring it, so the questions are in a single table. I am sure i will have more questions about linking,, but you never know i might figure it out.. hehe
----------------------------------------------------------------------------------------
Owing at LEAST 34 beers to other helpful forum members. Especially HansV!