Hi all!

I'm currently in the midst of designing a database for a web application. I have all the logic down on paper but have trouble converting it to a database / er diagram.

So onto the logic

Users can setup many diaries
Each diary can have many questions
Each question can have one answer
Each answer can have many responses

The fourth step is where I have hit a brick wall. Responses are the value a user can enter in relation to a question, for example:

1 - 10 (on a available scale of 1 - 10)
1 - 5 (on a available scale of 1 - 5)
1 - 2 (on a available scale of 1 - 2)
etc.

Responses can only be a maximum of 10

Where I'm confused is how to setup the table for responses as I want it to be flexible for the user, so a user can decide to setup a response scale of 1 - 10 for questions 1 and 2 but for questions 3,4 and 5 use a scale of 1 - 5.

Also, (just to be make it more confusing ) users can add descriptions to each scale, so for example :

1 - Poor
2 - Good
3 - OK
4 - Alright
5 - Excellent

Can anyone help in regards to what the table for responses would look like? Would I need to add another table for scales? Or am I thinking about this design the wrong way?

I have attached an incomplete er diagram here, but I'm not sure how helpful it will be!

Thanks in advance dbforumers!

schone

First, dump the Answers table. One-to-one relationships are seldom a good idea these days. Put the Answers attributes in the question table, and link the Responses table to it.
Your Questions table can have an attribute that holds the maximum response value, and you can enforce this in severla different ways, depending upon what database platform you are using.

Blindman's method is simplest but another method might be to have a ResponseType table that holds the different sets of responses allowed (i.e. “range 1-10”) and then have a related ResponseTypeValues table to hold the allowed values for that range (i.e. “1”,”2” …). Each question would then have a field to indicate which ResponseType goes with it. When you build your screen you’d just show against each question the valid options for that response type.

The ResponseTypeValues table would hold the fields:

• the name of the ResponseType range - "range 1-10"
• a code i.e. “1”
• a longer string i.e. “1 – Poor”
• defaultYN to indicate whether this field should be the default value.

This method would still work fine if you had different meanings for each type of response range i.e. if the question was "what sex are you?" you could get the range:

1. male
2. female

rather than just:

1. Poor
2. Good

Mike

Thanks blindman and mike!

I decided it might be best to go with mikes response so I have some follow up questions! Your response was very helpful but I'm still having some trouble representing your suggestion as an ER diagram.

I have attached an image of my new ER diagram and was hoping to get your opinion on it.

I have kept the core of the design the same but have added two new tables:

responses
Contains a list of values and the descriptions for each response (inputted by the user)

lookup_response_types
Contains a list a available scale lengths to choose from (set by dba)

I have also included a snapshot of data in all the tables in an Excel file (zipped).

Thanks in advance!

You can lead a horse to water....