Replicating a long form entry table design considerations

Question

I am currently converting a paper based system over to a new web portal. When the company (a medical practice) enrolls a new patient, there is a particular form that the patient currently fills out. This form contains 28 questions with as many as 20 different yes/no options per question.

My question is, should I create one table with many columns (possibly as many as 300, mostly containing BIT fields) or should I normalize it in some way?

All of the fields are associated with a single entity; the reason a patient is here.

In my experience of database design, huge tables with 100+ columns are generally frowned upon however I'd like to see what the community thinks of this example.

Answer 1

When using many boolean (BIT) fields, sometimes it's better to use a single INT field which is a bitwise sum. This simplifies the storage and table definition, but makes the code more complicated. Say you have a table Patients and some numeric values with different meanings: is on medication, is allergic on paracetamol, is diabetic etc. When you insert the ticked fields, you simply sum the corresponding numeric values. When performing select statements, you will do bit a bitwise-and comparison: TotalValue & Value = Value. Here is a bit of code:

CREATE TABLE #Patients(Id INT, PatientName VARCHAR(50), MiscIssues INT)

DECLARE @IsOnMedication TINYINT = 2
DECLARE @IsParacetamolAllergic TINYINT = 4
DECLARE @IsDiabetic TINYINT = 8

INSERT INTO #Patients
VALUES
    (1, 'A', @IsDiabetic + @IsOnMedication)
    , (2, 'B', @IsDiabetic + @IsOnMedication + @IsParacetamolAllergic)

SELECT * FROM #Patients WHERE MiscIssues & @IsDiabetic = @IsDiabetic

DROP TABLE #Patients

Another option is to use custom fields, but in my experience this approach performs poorly when you have a large amount of records.