If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
database design question
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. |
#3
|
|||
|
|||
database design question
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. |
#4
|
|||
|
|||
database design question
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. |
#5
|
|||
|
|||
database design question
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. |
#6
|
|||
|
|||
database design question
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. |
#7
|
|||
|
|||
database design question
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. |
#8
|
|||
|
|||
database design question
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. |
#9
|
|||
|
|||
database design question
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. |
#10
|
|||
|
|||
database design question
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. |
Thread Tools | |
Display Modes | |
|
|