database design question

[e-mid]

i keep questions in a table, each question has a different answer type. eg
answer could be a number then there will be limits for the answer or it
could be multiple choice question, then choices must be kept or it could be
a yes/no question ,
how should i design the tables in this situation?
thnkz in advance.

 

[e-mid]

thnx Uri for your answer.
some more explanation:
i will not store the answers to questions in the database,only the
properties of questions. and my problem is: there several question types
with different properties, and i dont know how to store them. all propeties
in one table(looks bad), or properties of each question in a diffrent table
(how can i reach them then? )
maybe a different approach to whole thing?

this is first time i design a database, i need some help

 

[Uri Dimant]

Hi
I gave an idea ,so you can modify it for your needs
CREATE TABLE Questions
(
QuestionId INT NOT NULL PRIMARY KEY,
QuestionnName VARCHAR(100) NOT NULL
)
GO
CREATE TABLE Answers
(
AnswerID INT NOT NULL PRIMARY KEY,
AnswerType CHAR(1) NOT NULL ,
CONSTRAINT MY_NAME CHECK (AnswerType IN ('Y','N'))
)
GO
CREATE TABLE Answers_Questions
(
AnswerID INT NOT NULL REFERENCES Answers(AnswerID) ,
QuestionId INT NOT NULL REFERENCES Questions(QuestionId),
PRIMARY KEY (QuestionId,AnswerID)
)
GO

 

[e-mid]

eg:
*if a question has a numeric answer, then there is an upper and lower limit
for this answer.this values would be stored
*if a question is a multiple choice question then these choices would be
stored..
*if a question is yes/no question, then this info (that it is a yes/no
question) would be stored.

tblQuestions tblAnswerTypes
------------- -----------------
question answerTypeId
answerTypeId answerTypeName(eg: numeric,yes/no )
properties (of this kind of answer)
.....
-------------- ----------------

i cant figure out where to keep properties for the answer.

is it clear now?

 

[Duane Hookom]

Check out the At Your Survey demo application at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.

 

[Uri Dimant]

What is a property?
How does it belong to the question?
How many properties the question has?
CREATE TABLE Properties
(
PropertyId INT NOT NULL
PropertynameVARCHAR(100) NOT NULL
)
GO

 

[Uri Dimant]

ok,here is goes
CREATE TABLE Questions
(
QuestionId INT NOT NULL PRIMARY KEY,
QuestionnName VARCHAR(100) NOT NULL
)
GO
CREATE TABLE Answers_Types
(
AnswerTypeID INT NOT NULL PRIMARY KEY,
AnswerType VARCHAR(100) NOT NULL ,
)
GO
CREATE TABLE Properties
(
PropertyID INT NOT NULL PRIMARY KEY,
PropertyName VARCHAR(100) NOT NULL ,
)
GO
CREATE TABLE Answers
(
AnswerID INT NOT NULL PRIMARY KEY ,
AnswerTypeID INT NOT NULL REFERENCES Answers_Types(AnswerTypeID),
PropertyID INT NOT NULL REFERENCES Properties(PropertyID)
)
GO
CREATE TABLE Answers_Questions
(
AnswerID INT NOT NULL REFERENCES Answers(AnswerID) ,
QuestionId INT NOT NULL REFERENCES Questions(QuestionId),
PRIMARY KEY (QuestionId,AnswerID)
)
GO

 

[e-mid]

thnkz Uri, you are so helpful..

ok,here is goes
CREATE TABLE Questions
(
QuestionId INT NOT NULL PRIMARY KEY,
QuestionnName VARCHAR(100) NOT NULL
)
GO
CREATE TABLE Answers_Types
(
AnswerTypeID INT NOT NULL PRIMARY KEY,
AnswerType VARCHAR(100) NOT NULL ,
)
GO
CREATE TABLE Properties
(
PropertyID INT NOT NULL PRIMARY KEY,
PropertyName VARCHAR(100) NOT NULL ,
)
GO
CREATE TABLE Answers
(
AnswerID INT NOT NULL PRIMARY KEY ,
AnswerTypeID INT NOT NULL REFERENCES Answers_Types(AnswerTypeID),
PropertyID INT NOT NULL REFERENCES Properties(PropertyID)
)
GO
CREATE TABLE Answers_Questions
(
AnswerID INT NOT NULL REFERENCES Answers(AnswerID) ,
QuestionId INT NOT NULL REFERENCES Questions(QuestionId),
PRIMARY KEY (QuestionId,AnswerID)
)
GO





answer

 

[e-mid]

thnkz duane , good example!

Check out the At Your Survey demo application at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.

 

[e-mid]

CREATE TABLE Properties
(
PropertyID INT NOT NULL PRIMARY KEY,
PropertyName VARCHAR(100) NOT NULL ,
)

Uri, my real problem is about this table.Questions has completely different
properties. i dont want to keep all of the properties in this table.

One solution i think is as follows:

*For Example, for numeric answer: there is upper and lower limit. lets say
5 and 10 for this situation. i think to keep these values as 5#10 in the
propertyname column.
*For a multiple choice answer: choices would be kept, lets say there are 3
choices , simply they could be kept as : orange#apple#banana in the
propertyname column.
*For a yes/no question ,it could be yes#no to denote that it is yes/no
question.

then i will parse according to '#' to get properties.

now i ask for:
if there is better solution? or a better design?