C
Chester
I'm working on an app that records data collected by service
technicians (VB.Net front-end, SQL Server 2000 back end). The
technicians need to record numbers with varying scale and precision.
For example, they may record one reading as 63.45 and the next as 123.1
and a third as 1.32456.
That's fine - those can be saved as floating point numbers (very
little math is done with these numbers so I'm not too worried about
strange floating point results). The problem is that I can't come up
with a good way to save trailing zeros. If the technician records a
reading of 12.00 or 15.560 for example, it needs to be preserved with
the trailing zeros. I can make the numbers display correctly in the
front end when they are typed in, but when they're saved, SQL Server
drops the trailing zero(s). That means that when they're retrieved
from the DB the next time they're needed, there is no way to know how
many zeros to display (e.g. the tech may have typed in 12.00, but after
it's been saved and retrieved it comes back as 12 and there's no
way to know if the tech originally type it in as 12 or 12.0 or 12.000).
The only ideas I've come up with are:
1) Save the numbers as text (varchar) and have stringent validation to
make sure that no text actually sneaks in.
2) Save the numbers as floating point AND save the precision with which
they were entered (e.g. 12.00 would get saved as 12 and the precision
would be saved as 2).
Putting numbers in text fields (option 1) makes me nervous and option 2
seems like a ton of extra work. Does anyone have a better idea or a
recommendation for option 1 or option 2?
technicians (VB.Net front-end, SQL Server 2000 back end). The
technicians need to record numbers with varying scale and precision.
For example, they may record one reading as 63.45 and the next as 123.1
and a third as 1.32456.
That's fine - those can be saved as floating point numbers (very
little math is done with these numbers so I'm not too worried about
strange floating point results). The problem is that I can't come up
with a good way to save trailing zeros. If the technician records a
reading of 12.00 or 15.560 for example, it needs to be preserved with
the trailing zeros. I can make the numbers display correctly in the
front end when they are typed in, but when they're saved, SQL Server
drops the trailing zero(s). That means that when they're retrieved
from the DB the next time they're needed, there is no way to know how
many zeros to display (e.g. the tech may have typed in 12.00, but after
it's been saved and retrieved it comes back as 12 and there's no
way to know if the tech originally type it in as 12 or 12.0 or 12.000).
The only ideas I've come up with are:
1) Save the numbers as text (varchar) and have stringent validation to
make sure that no text actually sneaks in.
2) Save the numbers as floating point AND save the precision with which
they were entered (e.g. 12.00 would get saved as 12 and the precision
would be saved as 2).
Putting numbers in text fields (option 1) makes me nervous and option 2
seems like a ton of extra work. Does anyone have a better idea or a
recommendation for option 1 or option 2?