Using a NULL value to signify that the value does not mattter. Not that it isn't known Say I have a table with computers where one column is a boolean &quot;fan&quot;. If true the computer has a fan, if false it does not. It can never be null.
Now I have another table with a list of recommendations for improvements to computers.
I want some recommendations to apply to all computers which have a fan. For this I have a column &quot;fan&quot; which when true will only apply to computers with a fan. When false will only apply to computers without a fan.
Is it good database design to have the column set to null when the recommendation should not care if the computer has a fan or not? As in the recommendation should apply whether the computer has a fan or not.
The purpose of null values is that the column is not known. Which is not the case here. It's not that I don't know if the recommendation is for a computer with a fan or without a fan, it's that I specifically don't care either way. This makes me think that it's not a good idea and is just changing the meaning of null.
On the other hand I think it makes sense that if the &quot;fan&quot; filter on the recommendation isn't defined it means that it doesn't need to filter based on that.
The other way to define the recommendation is have a applies_to_fanned_computers and applies_to_not_fanned_computers columns. If both are true then it applies to either, if only one is true then it applies to one of them.
Which option is best? Am I right that the first option is a bastardization of the purpose of null values in a database?
My approach for this is to think of what the fields are, which is storing information on computers. There is no case ever where having a fan would result in NULL, just a yes or no. In your case of using it as a major category for computers, I would avoid that, and instead use your queries and stored procedures to consider the filters. In the stored procedure you can have the WHERE clause look for the different combinations;
Only Computers with fans -- SELECT [recommendations] FROM table WHERE [has_fan]=1
Only computers without fans --SELECT [recommendations] FROM table WHERE [has_fan]=0
All computers\don't care -- don't use a WHERE clause;SELECT [recommendations] FROM table