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.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.
I would do the following :
1) Set quantity and price to "not null"
2) Put 0 when there is no value, instead of NULL.
This way,
a) if you use aggrate function like AVG, thoses functions will use
everyrow
b) non unique index on thoses fields will contained every rows
c) if you query thoses field (where quantit=0) rows will come out. IF
quantity is null and you query thoses rows, youll need (where
quantity=0 or quantity is null)...
etc.|||francois.bourdages@.harfan.com wrote:
> 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.
Here is a link to an oracle document (outch), that explain some
problems with null...
http://www.oracle.com/technology/or...jul/o45sql.html|||HandersonVA (handersonva@.hotmail.com) writes:
> 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.
That depends on the business rules. If not entering any price, means
"this item is for free", yes the 0 is the right thing. If not entering
any price means "I don't know", you should use NULL.
The same goes for Quantity. If this is an inventory 0 probably means "out
of stock". Which could be a good default. Then again, it's probably better
to have the user to state that explicitly.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||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|||Hugo Kornelis (hugo@.perFact.REMOVETHIS.info.INVALID) writes:
> 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.
That usually means that it's very expensive!
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
No comments:
Post a Comment