Relational databases don't have Boolean data types for good reason: the inherent nature of tri-value logic (i.e., true, false, and unknown/null) makes it a logical impossibility. To compensate for this, developers will use a code of some sort to represent their data: 1/0, 'T'/'F', 'Y'/'N', or, in D.P.'s case, "green"/"red".

There's really no standard when it comes to encoding "Boolean"-like data in a database, leaving the choice up to the developer. Some prefer the ease of conversion from integers to Booleans, and stick with 1's and 0's. Others prefer a more natural representation and use a 'Y' or a 'N'. In D.P.'s case, I guess the developer just liked colors or traffic lights or something.

Alas, this bizarre choice is just the tip of the iceberg in the system that D.P. has come to maintain. Among many other things, the original developers didn't care so much for normalization either ... 

CREATE TABLE [AppForms]
(
 [AppForms] INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
 
 /* ... Snip few dozen columns ... */

 [Content_PAGE1] TEXT NULL,
 [Title_PAGE1] VARCHAR(50) NULL,
 [IsComplete_PAGE1] VARCHAR(15) NOT NULL,

 [Content_PAGE2] TEXT NULL,
 [Title_PAGE2] VARCHAR(50) NULL,
 [IsComplete_PAGE2] VARCHAR(10) NOT NULL,

 /* ... Snip few dozen columns ... */

 [Content_PAGE25] TEXT NULL,
 [Title_PAGE25] VARCHAR(50) NULL,
 [IsComplete_PAGE25] VARCHAR(15) NOT NULL,
)

... fortunately, things got a bit clearer once D.P. entered the middle-tier .NET code. The developers decided to drop the "green"/"red" confusion and represent Booleans as "Y"/"N" strings ... 

While appsReader.Read()

  If appsReader("IsComplete_PAGE1") = "green" Then
    showPage1="Y"
    displayPage1.ImageUrl = "../../img/pages/checked.gif"
  Else
    showPage1="N"
    displayPage1.ImageUrl = "../../img/pages/unchecked.gif"
  End If

  If appsReader("IsComplete_PAGE2") = "green" Then
    showPage2="Y"
    displayPage2.ImageUrl = "../../img/pages/checked.gif"
  Else
    showPage2="N"
    displayPage2.ImageUrl = "../../img/pages/unchecked.gif"
  End If

  ' ... Snip ...

  If appsReader("IsComplete_PAGE25") = "green" Then
    showPage25="Y"
    displayPage25.ImageUrl = "../../img/pages/checked.gif"
  Else
    showPage25="N"
    displayPage25.ImageUrl = "../../img/pages/unchecked.gif"
  End If

End While

' ... Snip ...

If showPage1="Y" And showPage2="Y" And ... And showPage25="Y" Then
  formCompleteButton.Enabled = True
  formCompleteButton.Text = "Submit Application Form"
Else
  formCompleteButton.Enabled = False
    formCompleteButton.Text = "Please Complete Application Form"
End If

... and as you probably surmise from the brief snippets of code here, things just pretty much went downhill from here.

[Advertisement] BuildMaster allows you to create a self-service release management platform that allows different teams to manage their applications. Explore how!