On 1 Jun 2006 09:41:52 -0700, HandersonVA wrote:
should I set to "0" as a default value on a quantity and a price field
or set to "null"?
if a user enter nothing on the quantity or price field on a web
browser, should I treat it as null or "0"? I am so confused about this
concept. please advise me. thank you.
Hi HandersonVA,
There's a very big difference between 0 and NULL.
Suppose you're in a restaurant and the menu lists the price for some
dishes as $0.00. You'd probably order it right away, and tell all your
friend that yoou've foound a place with free food. But I'm pretty sure
that you wouldn't do the same is the price for some (or all) dishes was
simply omitted from the list.
In a database, NULL represents the price on a menu without price list.
NULL is specifically designed to represent the fact that no data is
available for a specific column in a specific row in the table.
Alowing data to be missing in a database introduces some interesting
problems. I won't describe them here (but I skimmed the article Francois
linked to, and on first glance it appears to be a good start on the
subject), but they are the reason that the common advise is to avoid
missing data whenever possible (or, to state it very shortly: avoid
NULLS). Of course, the real world sometimes confronts us with situations
where part of the data IS missing, so we can't always avoid it.
Unfortunately, some people have taken the "avoid NULLS" advise way too
litteraly - I have seen people avoiding NULL, but using some other
"magic value" to represent missing data. And believe me - that only
results in more problems, not in less!!
(Quick example - the average of {8.5, 9.5, NULL} would be calculated as
9.0 - not exactly right [since one of the input values is missing, the
only correct answer would be that it's impossible to calculate the
average], but it is at least the real average of the values that are
present in the database. But use 0 as "magic value" to represent missing
data, and you're calculating the average of {8.5, 9.5, 0}, which yields
6.0 - and that''s just plain silly!)
After this explanation about NULLs, let's get back to your question. A
default should be either a commonly used value (eg countrycode USA when
doing business in the USA), or a "safe" value (eg no automatic unlimited
bidding on an auctioning site).
For quantity, the value 1 is quite common in many industries. It's also
safe. So I'd recommend setting the default quantity to 1. Unless you're
in an industry that usually orders large quantities at once - in that
case, either choose a better default that fits the business or don't use
a default at all. I would definitely NOT use 0 as default quantity, as
an order for a quantity of 0 items is pointless (and shouldd in fact be
rejected by a CHECK constraint).
For price, there is no goood default value in most industries, so I
would not create a default for the column at all. Define the column as
NOT NULL (to force the user to enter a price) or, if the business has to
deal with orders before a price is known, allow NULLs and don't set up a
default - the price will remain NULL until one is explicitly entered.
Make sure you handle the missing information adequately and set up
constraints to ensure that the price must be known once the order passes
the stage where the prices should be known according to the industry's
business rules.
--
Hugo Kornelis, SQL Server MVP
