> I am working with a contractor on a project and I have a > question I am hoping some of you all can help me with. I am > building an asp.net application that asks the users a number > of personal questions(ex: hair color, eye color, education, > occupation etc). In total I have these questions broken up > into 7 forms. It sounds to me like you want a data-driven solution to selecting and displaying the questions for each form, then recording the answers. The most "normalized" relational solution would be something like: USERS UserID Primary Key Last Name First Name ... FORMS FormID Primary Key Sequence Unique Not Null Title Not Null ... QUESTIONS QuestionID Primary Key FormID Foreign Key to FORMS Sequence Unique Not Null QuestionText Not Null ... ANSWERS QuestionID FK to QUESTIONS UserID FK to USERS Answer I would make all primary keys auto-generated sequential numbers. Also feel free to add additional information into each table. In particular, if you wanted to get tricky, you could add an answer type to each question: free-form text, select drop-down or radio button (with list of choices), checkbox, etc. This structure should give you the flexibility you will need to generate each form using identical code, record the answers, and report on them afterwards. > Can you please help explain the pros and cons of each > design, or offer a better design that will ensure > scalability. > > The person designing the DB has the following design in mind: > - 7 tables, one for each form > - Each table has a field for each of the questions on that > page, some as high as 20 field to reflect each question, > with a user id in each record as well. I see no need to have a separate table for each form. They all have pretty much the same information, so one table with a form identifier on it is more maintainable, and makes more sense conceptually. Also, you don't want the user ID and answer on the same record as the question, as there will be many answers to each question. > I was also told I should use the following: > - 1 table for all of the questions > - Each record would have a userID, a formID, a questionID, and an answer I think this person must mean a table of questions, and a separate table of answers, for the reason given above. The only difference I have with this is if you use a unique ID for each question in the Questions table, regardless of which form it is on, you don't need the FormID in the Answers table. Then if you decide to move a question to another form (maybe unlikely, but say you do), you don't need to change all the references to the form in the Answers table. Conceptually, what form the question is on isn't important to the answer. (The person suggesting this probably was thinking of the QuestionID being relative to each Form, so they form the primary key together. That would work too, but I think my way is better, because it keeps them independent. The form that a question is on is more of a property of the question than an integral part of its identity, in my opinion.) For general background reading, you might want to google for articles on some combination of terms like "Relational Database Theory", "Database Normalization", "Normal Form", or "E.F. Codd" (the recognized originator of said theory). Have fun! - John -- This message may contain information which is private, privileged or confidential and is intended solely for the use of the individual or entity named in the message. If you are not the intended recipient of this message, please notify the sender thereof and destroy / delete the message. Neither the sender nor Sappi Limited (including its subsidiaries and associated companies) shall incur any liability resulting directly or indirectly from accessing any of the attached files which may contain a virus or the like.