Well, that fixed all my problems
Error with SELECT.....WHERE... Statement
Moderator: General Moderators
-
Ken Jacobs
- Forum Newbie
- Posts: 1
- Joined: Wed Nov 29, 2006 5:24 pm
... Table (schedma) design comments
In response to feyd's question, "Are there always going to be twelve questions with eight answers for each?" and in response to your comment, d3chapma, that the previous post "fixed all [your] problems ...", let me offer a different perspective.
Since this is a database forum here, it is appropriate to make some comments about the schema (table) design in this case. For one thing, I'd suggest using slightly more descriptive column names, too! (My personal convention is to name tables in the plural, like SURVEY_FORMS, and think of each row as corresponding to a single instance of the collection, e.g., a single SURVEY_FORM. But choose your own convention!)
More importantly, the design of your table looks like you are begging for trouble. SQL is great for manipulating ROWS, not so great for manipulating (sets of) columns within a row. You might think about the merits of a different design ... one where you have "normalized" the design. Without getting into formal relational database theory (or even the syntax for specifically creating an alternative) let me suggest that a design that uses multiple tables, with foreign key references would be more usable in the long run.
You might have a single table (let's call it SURVEY_FORMS) with one row for each survey form. Then a second table (call it SURVEY_QUESTIONS) that references the first, and has one row for each question in the survey. Each row in the SURVEY_QUESTION table would include the SURVEY_ID (which would be a foreign key reference to the primary key of the SURVEY_FORMS table). This way, you could have as many questions per form as you liked, and a different number of questions per form.
You could take it another step too, and move the possible answers to a third table (SURVEY_ANSWERS), where you would have one row per possible answer, with columns to identify the survey form, the question and the specific answer number within a particular question for a specific form. This would allow each question to have a different (and unlimited) number of possible answers.
Something like this conceptually ...
SURVEY_FORMS
PK: FORM_ID -- identifies a specific form
(other information that is unique to a survey form)
SURVEY_QUESTIONS
PK: FORM_ID, QUESTION_ID -- together they identify a row for a specific question for a specific form
FORM_ID would be a foreign key, pointing to the SURVEY_FORMS table
(plus other information that is unique to a question, but not to a specific answer to a specific question)
SURVEY_ANSWERS
PK: FORM_ID, QUESTION_ID, ANSWER_ID -- together they identify a row for a specific answer to a specific question for a specific form
FORM_ID+QUESTION_ID would be a foreign key pointing to the SURVEY_QUESTIONS table
(the information that is specific to a given answer for a given question on a given form)
Now, of course, as with any design decision, there will be trade offs in terms of performance and usability (and ability to support future, perhaps as yet unseen requirements or new applications). Depending on how you plan to use this data and the nature of your application, you might find the above structure is more flexible (and it may perform better too).
Just something to think about ...
Since this is a database forum here, it is appropriate to make some comments about the schema (table) design in this case. For one thing, I'd suggest using slightly more descriptive column names, too! (My personal convention is to name tables in the plural, like SURVEY_FORMS, and think of each row as corresponding to a single instance of the collection, e.g., a single SURVEY_FORM. But choose your own convention!)
More importantly, the design of your table looks like you are begging for trouble. SQL is great for manipulating ROWS, not so great for manipulating (sets of) columns within a row. You might think about the merits of a different design ... one where you have "normalized" the design. Without getting into formal relational database theory (or even the syntax for specifically creating an alternative) let me suggest that a design that uses multiple tables, with foreign key references would be more usable in the long run.
You might have a single table (let's call it SURVEY_FORMS) with one row for each survey form. Then a second table (call it SURVEY_QUESTIONS) that references the first, and has one row for each question in the survey. Each row in the SURVEY_QUESTION table would include the SURVEY_ID (which would be a foreign key reference to the primary key of the SURVEY_FORMS table). This way, you could have as many questions per form as you liked, and a different number of questions per form.
You could take it another step too, and move the possible answers to a third table (SURVEY_ANSWERS), where you would have one row per possible answer, with columns to identify the survey form, the question and the specific answer number within a particular question for a specific form. This would allow each question to have a different (and unlimited) number of possible answers.
Something like this conceptually ...
SURVEY_FORMS
PK: FORM_ID -- identifies a specific form
(other information that is unique to a survey form)
SURVEY_QUESTIONS
PK: FORM_ID, QUESTION_ID -- together they identify a row for a specific question for a specific form
FORM_ID would be a foreign key, pointing to the SURVEY_FORMS table
(plus other information that is unique to a question, but not to a specific answer to a specific question)
SURVEY_ANSWERS
PK: FORM_ID, QUESTION_ID, ANSWER_ID -- together they identify a row for a specific answer to a specific question for a specific form
FORM_ID+QUESTION_ID would be a foreign key pointing to the SURVEY_QUESTIONS table
(the information that is specific to a given answer for a given question on a given form)
Now, of course, as with any design decision, there will be trade offs in terms of performance and usability (and ability to support future, perhaps as yet unseen requirements or new applications). Depending on how you plan to use this data and the nature of your application, you might find the above structure is more flexible (and it may perform better too).
Just something to think about ...