Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2012
    Posts
    1

    DB Design for survey

    I have a problem with my database design.
    I have two options in mind but none of them really solves my problem:

    Option 1:
    FB (ID, Year, Question, Value)

    ID | Year| Quest.|Value
    1 | 2004| Q1 | hello
    1 | 2004| Q2500 | 15.2.12
    1 | 2004| Q2 | 56€
    1 | 2003| Q1 | bye
    2 | 2003| Q2 | 55€
    2 | 2003| Q1 |salut

    The problem with Option 1 is that the data type of field “Value” can be really everything! To solve that problem I thought of
    1. creating a table for each datatype or
    2. changing the table to FB (ID, Year, Question, Valueint, Valuestring,….etc.)
    Neither 1. nor 2. seems right to me.

    Option 2:

    FB (ID, Year, Q1, Q2, …., Q2500)

    ID| Year | Q1 | Q2 |,...,| Q2500
    1| 2004 | hello| 56€|,...,| 15.2.12
    1| 2003 | bye | …...|,….., |…..
    2| 2003 | salut| 55€ |, …..,|…..


    For Option 2 each column represents a Question.
    Unfortunately the number of Questions (Q1-QX ) per year may vary a lot.

    I'd be thankfull for any suggestions...

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Why not go with option 1, and use varchar() for the field type? It can store numbers, dates, etc as strings that can be converted if necessary.
    If it's not practically useful, then it's practically useless.

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

Tags for this Thread

Posting Permissions

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