SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    SitePoint Enthusiast eXile_30's Avatar
    Join Date
    Jul 2004
    Location
    Calgary, Canada
    Posts
    47
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    help/advice: best procedure for preferences/user registration form

    My apologies if these questions are answered somewhere in the vastness of this site but I'm both in a time crunch and my searches have only turned up a few minor details. Please keep in mind I'm a newb (in case you couldn't tell by my low(read: non-existant) post count.

    What I want to do
    Have a user preference/registration system where users can update a list of preferences/questions that will be stored in the database.

    With the exception of a couple text questions the form will be mostly checkboxes or radio buttons. On the edit page these need to be checked already (using arrays this should be easy, correct?).

    Questions
    What is the the best way to design the database to hold the questions/answers? Using multiple tables for different questions? One large table with all the answers? Keep the users answers in the same db as the user or separate (use another db to match the two together?)?

    Assuming I would use arrays (which I think is the best way to go about what I need, do you?), what would be the best way to set up the form?

    I'm not asking for the code or someone to do it for me, I'm just looking for advice on best practices for implementing this sort of user preference system.

    Thanks in advance.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    one database, three tables: user, question, useranswer

    the 3rd table is a many-to-many table relating the other two, and has a layout like this:

    userid
    questionid
    checkanswer
    textanswer

    the first two fields link back to their respective tables

    the first two fields together form a composite primary key

    the checkanswer and textanswer fields can both be null, and only one of them will have a value for any row
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast eXile_30's Avatar
    Join Date
    Jul 2004
    Location
    Calgary, Canada
    Posts
    47
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937
    one database, three tables: user, question, useranswer

    the 3rd table is a many-to-many table relating the other two, and has a layout like this:

    userid
    questionid
    checkanswer
    textanswer

    the first two fields link back to their respective tables

    the first two fields together form a composite primary key

    the checkanswer and textanswer fields can both be null
    That makes sense but I'm not exaclty sure what you mean by this:
    Quote Originally Posted by r937
    only one of them will have a value for any row
    Why can only one of them have any value for each row? Is it because you're thinking to have the form answers as static and then you can just put the value text in the db?

    Up until now I've been thinking to have the answers stored in a table called answers (or something like that) so that they can be looked up. What are you thoughts on that? Is that just bad db design?

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by eXile_30
    Why can only one of them have any value for each row?
    one's a yes/no field for checkboxes, the other's a text input field

    questions
    21 where do you live
    22 are you single
    23 how old are you

    users
    4 tom
    5 dick
    6 harry

    useranswers
    4 21 null new york
    4 22 y null
    4 23 null 21
    5 21 null london
    5 22 n null
    6 21 null paris
    6 22 y null
    6 23 null 37
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Enthusiast eXile_30's Avatar
    Join Date
    Jul 2004
    Location
    Calgary, Canada
    Posts
    47
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    That makes sense. Now I know what you mean. So in my case, since the questions will have multiple answers I would be using the text answer field more often.

    In the above scenario you're still suggesting to have the form hard coded though, correct? To make the form answers dynamic another table would have to be created that would have the question ID and the actual answer. Am I on the right track with my thinking for that?

    Thanks for painting the picture for me.

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    another table? yes, if the question actually has more than one answer

    and no, i made no suggestions about your form at all

    the for should be as dynamic as possible, e.g. you could generate a multiple-choice question's list of possible answers into a dropdown select
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Enthusiast eXile_30's Avatar
    Join Date
    Jul 2004
    Location
    Calgary, Canada
    Posts
    47
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    no, i made no suggestions about your form at all
    Right. I was basically guessing that based on what you had mentioned. Obviously I was wrong and my apologies for that.

    the for should be as dynamic as possible, e.g. you could generate a multiple-choice question's list of possible answers into a dropdown select
    That's what I had in mind. Thanks for your advice, it's very helpful.

  8. #8
    SitePoint Enthusiast eXile_30's Avatar
    Join Date
    Jul 2004
    Location
    Calgary, Canada
    Posts
    47
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Another quick question, to insert multiple rows I would have to use loops correct? How would I go about looping through all the selected options and inserting them into the db? (links to tutorials welcome )

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    looping is required, you are right, but exactly how to do it depends on the language -- coldfusion, asp, php, whatever

    perhaps post a new question in the appropriate forum
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SitePoint Enthusiast eXile_30's Avatar
    Join Date
    Jul 2004
    Location
    Calgary, Canada
    Posts
    47
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sorry about that, I'm using PHP.


Bookmarks

Posting Permissions

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