Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2007
    Posts
    6

    Red face Logic to Database - Confused!

    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
    Attached Thumbnails Attached Thumbnails er_sample.png  

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    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.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    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

  4. #4
    Join Date
    Oct 2007
    Posts
    6

    Red face

    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!
    Attached Thumbnails Attached Thumbnails er_sample.png  
    Attached Files Attached Files

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    You can lead a horse to water....
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •