CODEWITHSUNDEEP
databasedatabase-design
ino22
ino22

Reputation: 1

database design for many questions with different answer datatypes

I have a problem with my database design. I have two options in mind but none of them really solves my problem:

Option 1: 

FB (ID, Year, Question, Value)  
    1 | 2004| Q1      | hello  
    1 | 2004| Q2500   | 15.2.12  
    1 | 2004| Q2      | 56€  
    1 | 2003| Q1      | bye  
    2 | 2003| Q2      | 55€

The problem with Option 1 is that the data type of field “Value” can be really everything! To solve that problem I thought of

  1. creating a table for each datatype or
  2. changing the table to FB (ID, Year, Question, Valueint, Valuestring,….etc.)

Neither 1. nor 2. seems right to me.

Option 2: 

   FB (ID, Year,   Q1,    Q2,   ….,   Q2500)   
    1| 2004 | hello| 56€ |,....,| 15.2.12  
    1| 2003 | bye  | …...|,….., |…..  
    2| 2003 | salut| 55€ |, …..,|…..

The number of Questions (Q1-QX ) may vary a lot.

Any suggestions are appreciated! Thanks...

Upvotes: 0

Views: 834

Answers (2)

Bret Abdullah
Bret Abdullah

Reputation: 1

I would create two tables:

Create Table tblQuestions (
     ID int IDENTITY(1,1) NOT NULL,
     Year varchar(4) Default '',
     QuestionText varchar(4000) Default '',
     AnswerDataType varchar(50) Default '' )

Create Table tblAnswers (
     ID int IDENTITY(1,1) NOT NULL,
     tblQuestions_ID int Default 0,
     Answer varchar(255) Default '' )

Then I would create a function or sproc to validate that an answer was given in the correct data type and return TRUE or FALSE. Unfortunately I don't have time to write that code.

Upvotes: 0

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239646

I'd go for

CREATE TABLE Questions (
   QuestionID varchar(5) not null primary key,
   AnswerType varchar(10) not null,
   constraint CK_Question_Types CHECK (AnswerType in ('INT','CHAR','BOOL')), --Add more appropriate type names
   constraint UQ_Questions_TypeCheck UNIQUE (QuestionID,AnswerType)
)

and:

CREATE TABLE Answers (
    ID int not null,
    Year int not null,
    QuestionID varchar(5) not null,
    AnswerType varchar(10) not null,
    IntAnswer int null,
    CharAnswer varchar(max) null,
    BoolAnswer bit null,
    constraint FK_Answers_Questions FOREIGN KEY (QuestionID) references Questions,
    constraint FK_Answers_Question_TypeCheck FOREIGN KEY (QuestionID,AnswerType) references Questions (QuestionID,AnswerType),
    constraint CK_Answer_Types CHECK (
       (IntAnswer is null or AnswerType='INT') and
       (CharAnswer is null or AnswerType='CHAR') and
       (BoolAnswer is null or AnswerType='BOOL') and
       (IntAnswer is not null or CharAnswer is not null or BoolAnswer is not null)
    )
 )

This lets you ensure that each answer is of the correct type, and not null, whilst ensuring no extraneous data ends up in the table.

The use of two foreign keys isn't really required (you could remove FK_Answers_Questions), but I prefer to document that the real FK reference is on QuestionID, whilst we want to enforce a constraint across the tables, using the second one and the new CHECK constraint.

Upvotes: 2

Related Questions