Basic Rules
You’d expect when you spend hundreds of thousands or even millions including consulting costs for a piece of software that not only would it work, but it would be well written. Our previous experience at work proved quite otherwise. Our current, on the other hand, has panned out much better. Unfortunately I ran in to something that really ticks me off today.
In your typical database you have three logical states: true, false, and null. Null represents the unknown and undefined. Null is used for any data field that has no data in it. In other words a value that you do not know. Database logic is built around the idea that a null isn’t simply not a value, but an unknown. For instance if I ask for all rows where a certain field does not equal ‘X’, null columns aren’t supposed to be counted. Likewise in numerical values selecting less than a number won’t count nulls, they aren’t zeros after all.
Thus when you mean to use True/False (T/F) or Yes/No (Y/N) values you shouldn’t substitute F with null, nor should you substitute N with null because it ceases to work properly if you are using negative logic. Granted, negative logic isn’t processor friendly in the least, but none the less this does not account for allowing an obvious flaw in your data.
Now then, back to the topic. Today I was working in our the impressively expensive software suite we have purchased only to find a table with an indicator column that switched on Y/null instead of Y/N. My SQL and the logic of my programs that make reference to the table won’t be effected by it, but none the less such a glaring flaw in the data modeling makes me ill at ease. For the money you’d hope they would have corrected for such issues to make sure the entire structure of the database is rock solid and beyond any such little issues.
Of course anyone who enjoys video games knows that quality is far from being job #1 for much of anyone these days.