0

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?

erictrigo
  • 989
  • 2
  • 13
  • 41
  • This has been discussed several times before, e.g. [here](http://dba.stackexchange.com/questions/5222/why-shouldnt-we-allow-nulls) and [here](http://stackoverflow.com/questions/8300953/why-null-values-are-mapped-as-0-in-fact-tables/8310891#8310891). One key point is that using zero instead of NULL will give incorrect (for most people) results when used with aggregate functions like `MIN()`, `MAX()` or `AVG()`. – Pondlife May 01 '13 at 15:48

2 Answers2

5

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.

granadaCoder
  • 26,328
  • 10
  • 113
  • 146
  • You can also add a CHECK constraint that would force a non-zero price. You could put that in "now", but remove the Constraint "later" if you find you need to put in "price-not-known" rows. I find this a better practice, because it is easier to remove a constraint (or tweak a contraint) down the road, then changing a column from "not null" to "null", IMHO. But that's me. There is a little gray area there. – granadaCoder May 01 '13 at 15:32
0

This might work from an administrative perspective, but what about those who query the database from a front-end application? For instance, if a user wants to filter a search for all items that have a cost of '0', then they are not taking NULL's into consideration (and as a user they most likely wont).

From an administrative perspective, you could set your queries to cover the entire range by querying both null and value columns:

If empty:

NULLIF(RTRIM(field), '') IS NOT NULL

If null:

NULLIF(RTRIM(field), NULL) IS NULL   --or simply 'IS NULL'

Default value:

(COALESCE(field, '0.0')
Futureproof
  • 375
  • 4
  • 21