Modelisation problem?

[inarobis]

Hello Guys,

I have a question:

I would like to build a flexible question tool. These are the main tables for the moment.

/*This table is question table; Question ID, The kind of answer (Text, integer or list), QuestionType (this is for script), Question Text)

CREATE TABLE [dbo].[QUESTION] (
[QUESTION_ID] [int] NOT NULL ,
[ANSWERTYPE_ID] [int] NULL ,
[QUESTIONYPE] [int] NULL ,
[QUESTION] [varchar] (500) NULL
) ON [PRIMARY]
GO

/* List of possibility of answer */

CREATE TABLE [dbo].[ANSWERTYPE] (
[ANSWERTYPE_ID] [int] IDENTITY (1, 1) NOT NULL ,
[ANSWERCODE] [varchar] (50) NULL ,
[NAME] [varchar] (50) NULL
) ON [PRIMARY]
GO
/* Answer_ID, ANSWERTYPE_ID, QUESTION_ID */
CREATE TABLE [dbo].[ANSWER] (
[ANSWER_ID] [int] IDENTITY (1, 1) NOT NULL ,
[ANSWERTYPE_ID] [int] NULL ,
[QUESTION_ID] [int] NULL
) ON [PRIMARY]
GO


/* an answer can be an integer, a text or list */
CREATE TABLE [dbo].[INTEGER] (
[INT_ID] [int] IDENTITY (1, 1) NOT NULL ,
[ANSWER_ID] [int] NULL ,
[ANSWER] [int] NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].

• (
  [LIST_ID] [int] IDENTITY (1, 1) NOT NULL ,
  [ANSWER_ID] [varchar] (100) NULL ,
  [ANSWER] [int] NULL
  ) ON [PRIMARY]
  GO
  
  CREATE TABLE [dbo].[TEXT] (
  [TEXT_ID] [int] IDENTITY (1, 1) NOT NULL ,
  [ANSWER_ID] [varchar] (100) NULL ,
  [ANSWER] [int] NULL
  ) ON [PRIMARY]
  GO
  
  My question: How can I set up correctly for type of question. if it is a question type text, fill up the table TEXT?
  
  Thanks on advance
  
  INA
  
  

 

[SQLSister]

Well first let me say do not use interger or text or anyother reser ed word as your table names. Using reserved words for objects in a database is not a good practice.

personally instead of your structure I would use one table for all the answers and three differnt fields to hold the differnt types of answers in if you need to. Then you can query using the case to pull the correct answer and without having to figure out whchi tables to join to. I think in the long run it would be easier to program queries to that structure than yours.

As far as putting data into a text field you might read about writetext and updatetext in BOL

Questions about posting. See faq183-874

 

[inarobis]

Hello SQLSister,

Thank you very much for your time and advice. I will do as you advice me much more easy for queries. I was totally fixed on this problem of different type of answer and did not go forward to another solution.

I put reserved word for table only for this first modelisation (I know that it is not good practice to do that ).

I will have look at text field in BOL.

thanks again very much

Ina