Database Design Help

Hi All,

I would assume something like this already exists, so if someone could point me in the right direction I would appreciate it.

I am looking at creating a dynamic form for a small questionnaire that a user will fill in when they complete an installation.

Now we want to be able to potentially ammend and change the forms as time goes on but maintain any existing competed forms.

We also want the flexibility of building different types of questions, so examples would be;

- single text answer (text)
- single multiple choice (radio)
- multiple selection multiple choice (checkbox)
- slider (values)
- image (possibly multiple) uploads (to be stored as blobs in the db)

So far I have the following idea drafted

Form Table -
ID - PK Int
Title- String
Active - Bit

Section
FormID - Int PK FK to Form -> ID
SectionID - Int PK
Title - String
Active - Bit

SectionQuestion
SectionID - Int
QuestionID - Int

Question
ID - Int
Type - Int FK to QuestionType -> ID
Title - String

QuestionType
ID - int
Type - String

QuestionOption
QuestionID - int
OptionID - int

Option
ID - Int
Option - String

QuestionAnswer
QuestionID
AnswerID
InstallationID

Installation
ID - Int
Status - Text

I maybe over complicating this though? any ideas?
flynnyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

pcelbaCommented:
Don't reinvent the wheel... Open source questionnaire software exists already.
You may look e.g. here: https://survey.codeplex.com/ or here: https://opinahq.com/
or at several similar project status here: http://www.gesis.org/en/services/study-planning/online-surveys/a-guide-to-survey-software/open-source-software/

If you still plan to do everything yourself then do it as simple as possible and extend it later.

Your data model contains several problematic parts and you should improve it.
1. Be consistent in column names - some FKs use ID postfix others don't
2. It is better to use descriptive PKs instead of simple "ID"
3. Some models recommend to use table name prefix (3 chars) in all column names
4. SectionQuestion table allows to use one question several times on one form. Is it OK?
5. How do you plan to use Option table?
6. Do you have Answer table?
etc.

And one important question: Do you think users will like questionnaires after software installation?
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ste5anSenior DeveloperCommented:
From the database design perspective:

Your four question types (single text, single choice,  multiple multiple choice,  slider (single numeric value)) are subtypes of the entity Question. This is modelled correctly with imho only this schema:

Questions: QuestionID(PK),  TypeCode (CHECK [TypeCode] IN ('C',  'S')), TitleText, QuestionText
QuestionChoices: QuestionID (PK), TypeCode (CHECK [TypeCode]  = 'C')), QuestionNumber (PK), QuestionOption with FK(QuestionID, TypeCode)->Questions(QuestionID, TypeCode)

Where your single/multiple choice question are storing there option texts in QuestionChoice and your single text and slider stores the question text already in Questions.

The answers are also stored in such sub-classed tables.
0
PatHartmanCommented:
I believe if you search for "at your survey", you'll come up with an Access sample that will probably get you started.  

I have created an application similar to what you are requesting but I am not at liberty to publish it and even if I were, it is not something I would simply give away for free.  The application was for a company that offered relocation services to corporations.  The surveys were customized to the client and to the destination.  US cities had different options from foreign cities.  The surveys also differed depending on whether a spouse and/or children, pets, nannies, etc were accompanying the transferee.

You seem to be on your way with the schema you posted but please try to be consistent in your naming.  Primary keys should be SomeEntityID and the foreign key that references them should also be named SomeEntityID unless you need two references to the same PK in a single table as you would for EmpID and SupervisorID in tblEmployee.

Creating a form to capture answers is a bit of a challenge if you really want to support multiple formatting options.  I did it with a subform but it means that your form can only show one question at a time which can be awkward.  In a different application, I settled on plain text and a listbox which could be done using a form in continuous view so if you can get by without the more sophisticated visual options, this would work better.  Also, printing the survey could use more visual options.  It is only the data entry form where it is clumsy.

Please try to focus on specific questions so we don't feel as though we are being asked to design the application for you.

I found a very old copy of the app and here are a picture of the form used to define a check list and what the check list looks like when it is printed.  I don't have a copy of the data entry form handy.
FormPrint
0
flynnyAuthor Commented:
Thanks for all the help guys!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Network Architecture

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.