Best practice: should I store prices and costs as NULL or NOT NULL?
I'm working with a DB that's been developed over time by a number of people. There are a number of inconsistencies regarding fields related to Cost or Price; in some tables these columns are NULL, while in other tables they're NOT NULL. I'm thinking of updating the tables and code to a certain standard.
The only advantage I see to allow NULL is that you difference between value not set (NULL), or value set to blank (0.00). If the column was NOT NULL, all records would have 0.00 as default and you would not be able to tell which ones were set to 0.00 by the user. In the other hand, it does not make any sense whatsoever for the user to set a cost or price to 0.00 in the system.
In your opinion, what's the best practice regarding this?
NULL means "not known" IMHO. If "0.00" is not the price/cost, then it should be NULL. Some things are 0.00 (like if you get a free t-shirt with a CD purchase). Please do NOT set at "ZeroMeansNotKnown" "flag" in your table. That's squirrelly code.