database design question

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.

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" wrote in message
...
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" someone@somewhere wrote in message
...
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.

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" someone@somewhere wrote in message
...
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.

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?


"Uri Dimant" wrote in message
...
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

"e-mid" someone@somewhere wrote in message
...
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" wrote in message
...
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" someone@somewhere wrote in message
...
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.

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

--
Duane Hookom
MS Access MVP


"e-mid" someone@somewhere wrote in message
...
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.

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

"e-mid" someone@somewhere wrote in message
...
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" wrote in message
...
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" someone@somewhere wrote in message
...
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.

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" someone@somewhere wrote in message
...
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?


"Uri Dimant" wrote in message
...
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

"e-mid" someone@somewhere wrote in message
...
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" wrote in message
...
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" someone@somewhere wrote in message
...
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.

thnkz Uri, you are so helpful..


"Uri Dimant" wrote in message
...
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" someone@somewhere wrote in message
...
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?


"Uri Dimant" wrote in message
...
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

"e-mid" someone@somewhere wrote in message
...
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" wrote in message
...
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" someone@somewhere wrote in message
...
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.

thnkz duane , good example!

"Duane Hookom" wrote in message
...
Check out the At Your Survey demo application at
http://www.rogersaccesslibrary.com/O...p#Hookom,Duane.

--
Duane Hookom
MS Access MVP


"e-mid" someone@somewhere wrote in message
...
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.

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?




"Uri Dimant" wrote in message
...
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" someone@somewhere wrote in message
...
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?


"Uri Dimant" wrote in message
...
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

"e-mid" someone@somewhere wrote in message
...
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" wrote in message
...
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" someone@somewhere wrote in message
...
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.