non-numeric value in a numeric field

  • Thread starter Thread starter aa
  • Start date Start date
A

aa

I have a table of chemicals' properties. The fields contain numeric data. But there are some substances for which some property has a non-numeric value. Like, say, Flash Point some liquids have a numeric values for it, whereas others have it equal to "None".

How do people get round this?

One of the solution> I thought, would be entering a certain number, which I know for sure, would never be equila to Flash Point of any substance, and then add some logic when making queries. But this looks very awkward. Any other approaches?
 
The general rule is to define fields as text unless you want to use the
numerics for direct calculations. Since it would be most unusual to want to
do calcs on things like boiling point, you may as well store them as text.
Then you get the obvious flexibility of putting things like "unknown" in the
field.

Even with text fields, you can still do calculations if you ever need to,
just use the CDbl conversion function to convert the text to numerics during
the calculation.

--
Regards,

Adrian Jansen
J & K MicroSystems
Microcomputer solutions for industrial control
I have a table of chemicals' properties. The fields contain numeric data.
But there are some substances for which some property has a non-numeric
value. Like, say, Flash Point some liquids have a numeric values for it,
whereas others have it equal to "None".

How do people get round this?

One of the solution> I thought, would be entering a certain number, which I
know for sure, would never be equila to Flash Point of any substance, and
then add some logic when making queries. But this looks very awkward. Any
other approaches?
 
Thanks.
I do not need to do calculations indeed, but I need to perfopom some
comparisons to run queries like
WHERE FlashPoint is between A and B.

Of course I can do it as you suggested, but converting text to numbers, but
this does not look like an elegant solution.

I hoped there is a sort of surrogate numeric value which could be accepted
by a numeric field.
BTW, is it possible to hold the "infinity" in a numeric field?
 
I hoped there is a sort of surrogate numeric value which could be accepted
by a numeric field.
BTW, is it possible to hold the "infinity" in a numeric field?
Click to expand...

I would recommend leaving the field NULL, and perhaps adding a second
text field to explain NULLs.
 
John Vinsons suggestion is better, else you will get into trouble with even
simple comparisons

WHERE Cdbl(FlashPoint) is between A and B

will fall over when FlashPoint is not numeric. Even of it sets the result
to zero, that may not be what you want.

And no, there is way of entering 'infinity' in a numeric field, even though
there are representations of several different sorts of non numbers in the
IEEE Double Float definitions.
--
Regards,

Adrian Jansen
J & K MicroSystems
Microcomputer solutions for industrial control
 
Thanks, yet NULL is not ideal as for some material certain property is not
applicable and I am already using NULL for that purpose. So I have two types
of non-numeric values to ve stored in a numeric field: None and Not
applicable and they have different meaning.
So if there is no way to have a non-numetic or infinite value, then
replacing None or Not Applicable with a number long enough and big enough to
secure that it is not confused with a real vlaue, and then applying simple
logic, seems to me a more efficient solution.
 
While you *can* do that, in effect you are coding data into the record which
is not applicable to that record type. And you then have to decode the
special values into meaningful display to a user, which has to be hard-coded
into some code in the running application. So if you want to change or add
extra codings to this, you have to add code to adjust each time. The whole
point of database design is that the data should be independent of the
design. I would take John Vinson's suggestion of storing comments/special
cases in a separate field very seriously.

--
Regards,

Adrian Jansen
J & K MicroSystems
Microcomputer solutions for industrial control
 
Yet, I took John's idea onboard, and this seems to be the radical and
scaleable solution.
My concern is that adding another field which in my case is going to be
mainly empty, will denormalise the database - what is your opinion on that?
 
My concern is that adding another field which in my case is going to be
mainly empty, will denormalise the database - what is your opinion on that?
Click to expand...

My consulting resume contains the line:

"Judicious denormalization, only when necessary".

Yes, it's evil, but it's the least evil of the alternatives in my
opinion!
 
My concern is that adding another field which in my case is going to
be mainly empty, will denormalise the database - what is your opinion
on that?
Click to expand...

But the sparseness or otherwise of a field has nothing to do with
normalisation: the definition of 3NF is (approximately) "functional
dependence on the key, the whole key, and nothing but the key". As far as I
can see, it's fully compliant.

HTH


Tim F
 
But the sparseness or otherwise of a field has nothing to do with
normalisation: the definition of 3NF is (approximately) "functional
dependence on the key, the whole key, and nothing but the key". As far as I
can see, it's fully compliant.
Click to expand...

Well, from a purist point of view, the text field depends on the
numeric field; it should be NULL if the number field is non-NULL and
vice versa. OTOH one could always use it as an optional comment
concerning the amount, even if the amount were numeric.
 
Well, from a purist point of view, the text field depends on the
numeric field; it should be NULL if the number field is non-NULL and
vice versa. OTOH one could always use it as an optional comment
concerning the amount, even if the amount were numeric.
Click to expand...

From a purist point of view we have one piece of data - the flash point
- whose possible values are Unknown, None or a positive real number
(assuming we're thinking in kelvin). Since Jet and (AFAIK) SQL Server
don't offer a data type that matches this domain, any implementation is
going to have its awkward points and it's a matter of what's simplest in
practice.

I feel that the OP's idea of using a magic number is probably the least
bad. If the rule is that any value < 0 means "no flash point", then
queries such as
WHERE FlashPoint BETWEEN x AND y
will work just fine; the trap would be sprung if one used
WHERE FlashPoint < x
rather than
WHERE FlashPoint BETWEEN 0 AND x
 
Thanks, John.
In case of Flash Point(FP) and other temperature-related properties, the
Magic number -274 would obviously do the trick for every existing and
non-existing compound. However logically, FP="None" is equivalent to
FP=very big number. So it should be a reasonably big positive, say, 9999. In
this case a query WHERE FP>some_value will return the correct result. But
otherwise you've said exactly what I meant.

The trouble is that the table contains other properties and I would like to
apply a standard approach to all of them, therefore the number needs to be
"safe" for other parameters as well. For example, molecular weight could be,
say, 300000 and Volume Resistivity could be as big as 10 in 15th. However
this sort of numbers it is sufficient to store just 15.
 
Thank you very much, gentlemen, for very useful opinions
I have a table of chemicals' properties. The fields contain numeric data. But there are some substances for which some property has a non-numeric value. Like, say, Flash Point some liquids have a numeric values for it, whereas others have it equal to "None".

How do people get round this?

One of the solution> I thought, would be entering a certain number, which I know for sure, would never be equila to Flash Point of any substance, and then add some logic when making queries. But this looks very awkward. Any other approaches?
 
The Double datatype can store values larger than 10^308. Is that big
enough to be magical?
 
Back
Top