Posted by --CELKO-- on 01/31/08 17:36

>> "Questions" stores a question_id and the question text
"Responses" stores a response_id and the response text
"Company Question/Response" stores a company_id, question_id and
response_id (as well as a short text answer) <<

A response is an attribute of a questionnaire, not a separate thing by
itself. This is an old George Carlin joke about baseball scores
--"now for some baseball scores; 12, 5, 7 and 9"; get the point? IT
cannot exist separated from its entity.

CREATE TABLE Questions
(question_nbr INTEGER NOT NULL PRIMARY KEY,
question_txt VARCHAR(1000) NOT NULL);

CREATE TABLE Questionnaires
(company_id CHAR(9) NOT NULL
REFERENCES Companies(company_id),
question_nbr INTEGER NOT NULL
REFERENCES Questions(question_nbr),
PRIMARY KEY ((company_id, question_nbr),
response_txt VARCHAR(1000) DEFAULT '{{Not Answered}} NOT NULL);

You can now re-use the questions in various questionnaires; if you ask
the same company more than once, then add a survey_nbr to the
Questionnaires table.

>> I would like to create a view that would "flatten" all the question/
responses [Questionnaires] to a single line item for each company. <<

Read about First Normal Form and Tiered Architectures in any RDBMS
book. Formatting is done in the front end and not the database. You
are confusing SQL with a monolithic procedure & file system language
like COBOL.

• Next in forum: Re: help with update
• Prev in forum: help with update
• Thread view: Re: Create Table with Variable included in Table Name

[Reply to this message]