CODEWITHSUNDEEP
sqldatabase-design
rpf3
rpf3

Reputation: 691

Database Design - Optional Bit Fields

I'm building a database that will be used to store Questions and Answers. There are varying Question types that deal so far with a DateTime answer, Free Text, DropDownList, and some that link to other tables in the database. My design question is this: Some Question types have other boolean attributes that are unique to that type. Is it better to have the boolean column in the generic Questions table or create some sort of Flag table for each question type?

As an example, a DropDownList Question might have a boolean attribute to tell whether or not to display a TextBox when a value "Other" is selected, but a Free Text Question would have no use for this.

Thanks heaps

EDIT:

I guess it seems to be boiling down to is it better to store unused columns in a generic Questions table to extend out for each Question type and have lots of keys back to the Question table using Views to access the data for various Question types.

Upvotes: 2

Views: 974

Answers (4)

rpf3
rpf3

Reputation: 691

I am mulling this over as a possible solution, seems more abstracted to me and allows for the most future extension.

CREATE TABLE dbo.QuestionTypes
(
    Id      INT             IDENTITY(1, 1) PRIMARY KEY,
    Type    VARCHAR(256)    NOT NULL
);


CREATE TABLE dbo.TypeSpecificFlags
(
    Id        INT             IDENTITY(1, 1) PRIMARY KEY,
    TypeId    INT             REFERENCES dbo.QuestionTypes(Id) NOT NULL,
    Flag      VARCHAR(256)    NOT NULL
)


CREATE TABLE dbo.Questions
(
    Id            INT             IDENTITY(1, 1) PRIMARY KEY,
    Name          VARCHAR(256)    NOT NULL,
    ShortName     VARCHAR(32),
    TypeId        INT             REFERENCES QuestionTypes(Id) NOT NULL,
    AllowNulls    BIT             NOT NULL DEFAULT 1,
    Sort          INT
);


CREATE TABLE dbo.QuestionsFlags
(
    Id            INT    IDENTITY(1, 1) PRIMARY KEY,
    QuestionId    INT    REFERENCES dbo.Questions(Id) NOT NULL,
    FlagId        INT    REFERENCES dbo.TypeSpecificFlags(Id) NOT NULL,
    Answer        BIT    NOT NULL
);

Upvotes: 0

rkg
rkg

Reputation: 5719

Strip out all the extra attributes from the base question table and have a field for the 'Question Type' and a set of tables for each question type. In your application code, based on the questions type retrieve the row from the particular question type table and use them.

An example:

Base Question Table: t_question <QuestionID, Question, QuestionType, QuestionTypeLink>

Let's say you have two question types: Comprehensive or Simple. Create two tables for each of them with schema: t_compflags <linkID, field1, field2...> and t_simpleflags <linkID, field1, field2...>.

Now in the base question table, QuestionType would take two values: Comprehensive or Simple. Based on that field it uses the QuestionTypeLink to refer the row in either of the tables.

EDIT:

You can't directly enforce PK-FK constraint on these tabes, you have to do that in application code. But if you would like to enforce that constraint, there is a dirty way of doing it. Instead of QuestionTypeLink, have two columns CompQuestionTypeLink and SimpQuestionTypeLink which allow nulls and references the other two tables. But I personally think this is a bad design.

Upvotes: 2

John
John

Reputation: 16007

Depending on how many combinations you have you could just express each combination as its own type:

DateTime
DropDownList
DropDownListWithOptionalOther
FreeText
FreeTextNumbersOnly
...

This flattens your design a little at the expense of a potential combinatorial explosion. But I don't know how many combinations you have, or will have.

Could you include the text box automatically if you have a DropDownList choice of "Other?" Or would there be a case when the user wouldn't have to specify what "other" is?

If you have too many combinations to consider, then it still sounds like you'll need to specify the flags at a per-question basis, so it makes sense to include another field in the Questions table to specify these flags. Maybe have them as plain text so you can extend later if you need to? Like a comma-separated list of flags in that field?

Upvotes: 0

Mike Mengell
Mike Mengell

Reputation: 2398

This depends entirely on how much normalisation you want to do and how many columns you're talking about.

If you are expecting quite a number then you should have a 1:1 table relationship simply to extend that question type. Something like

Create Table QuestionType_DropDownList

(OtherDisplay bit,

SomethingElse bit)

This is easier to read and easier to query. But it's not easily maintainable. It is unfortunately very much a pros/cons thing.

In my experience I would pick this solution as you never know what the future may hold.

Upvotes: 0

Related Questions