As is my wont, I like to explain things with analogies and this is no different. Consider a table holding job application details for a job board website.
CREATE TABLE dbo.tblJobApplications(
CandidateID INT,
JobID INT,
DateApplied DATE,
Successful BIT)
-- Application in progress
INSERT INTO dbo.tblJobApplicationsSELECT 1, 1, GETDATE(), NULL
-- Application unsuccessful
INSERT INTO dbo.tblJobApplicationsSELECT 2, 1, GETDATE(), 0
-- Application successful
INSERT INTO dbo.tblJobApplicationsSELECT 3, 1, GETDATE(), 1
The key field to note here is of course the BIT field which indicates the success or failure of the applicaiton. Obviously, when a candidate applies to a job, the success of the application isn't known - the candidate has been accepted, nor rejected. Its only at the end of the lifecycle of the application that this field can take on a meaningful value.
Hopefully, this contrived example helps explain just when you might require a NULL bit field.
Nulls are evil. Didn't your mother teach to stay away from them? :)
ReplyDeleteIn your example you are describing a value that has 3 possible states - "In progress", "Accepted" or "Rejected". There is nothing unknown.
The field isn't Status though, its Successful which is either true or false or (as yet) undecided.
ReplyDeleteThat is my point - we have missed a domain concept, namely that a job application progresses through various states. Once we have the correct domain concepts then the need for null disappears.
ReplyDeleteAs i mentioned in my post, this is a contrived example and this isn't a discussion about good database design nor whether NULLs are good (or evil). It was merely illustrating a scenario when a NULL bit field could be relevant.
ReplyDelete