November 13, 2015 at 10:29 am
I have come across a problem regarding choosing the proper data types and designing schema for storing information about a piece of specification on an engineering drawing. If you have never seen an engineering drawing, just google it and take a look at some of the images that com across.
They vary in specification, but one of the most common ones is defined as the following: 1.00" +/- 0.05". Nominal being 1.00" and upper and lower tolerances being 0.05". The trailing zeros in the nominal are very important, and based on the standard used, the nominal can have a different level of precision than the tolerances. For example, 1.0 mm +/- 0.05 mm. Keep in mind that this is one of the simplest forms of defining a specification. Sometimes 3, 4, or more numbers are involved in doing so.
The challenge is that if a user inputs data with a trailing zero, the application (a Web API and MVC web application written in .Net) must retain that information and keep track of the number of trailing zeros used for every number in the specification. The only data type that has this type of flexibility in SQL is the float, but I very much would like to avoid using floats, because of the nature of the information being stored. These are discrete numbers that happen to have varying scale. I attempted to use decimal and then store the number of trailing zeros in a single column in form of a smallint, but that was before realizing that every part of the specification could have varying number of trailing zeros. The other obvious solution is to store the count of trailing zeros for every part of the specification separately, but how would one achieve that cleanly?
November 13, 2015 at 1:04 pm
This is certainly an interesting situation. In every drawing I've seen, 3 decimal places are the norm, but I've seen some go out to 4 places. With this in mind, I think I'd go with a Numeric(8, 4). That'll go up to 9999.9999, which is probably enough inches (~833 ft) unless you're designing buildings or a whole manufacturing line. If you're going to programming a CNC machine from your data, you might need 6 decimal places. You can adjust as necessary, but given the limited knowledge I have of what you're designing, I think I'd start out with 4 decimal places.
I think I'd also keep my tolerances in a separate column. If your tolerances are always (and I do mean always) in both directions, you don't need a column for it. If they ever go in separate directions (+, -, +/-) then I'd create a separate columns. Using the base measurement, direction(s) and tolerance, you'll have the base measurement and be able to calculate the min and max measurements as well.
I hope this helps.
November 13, 2015 at 1:19 pm
Hi Ed, Thanks for the response.
As I mentioned, the specification can be defined using 3, 4, or more values. In the example I provided, they can go in separate directions sometimes, but that's besides the point too.
My problem is entirely around the number of trailing zeros for each one of the numbers involved in the specification. I have to be able to reconstruct the specification as the user inputted the data. Basically, if they added 3 trailing zeros to the upper tolerance and only 1 to the nominal, I need to be able to store that information somehow so the UI can use it to display it back to the user.
I have used decimal(19,10) for nominal, low, and high, and everything else. However, there is no way of knowing how many trailing zeros were used to construct the decimal just from the value stored in the database. So somehow, I need to be able to store the scale associated with each decimal value of the specification as well as the actual value. So far, there are 15 columns that would need to get the same treatment.
November 14, 2015 at 1:15 am
set nocount on
create table MyTable
(
StrSource varchar(19),
SQLNo decimal(19,10),
EngFormat varchar(11)
)
go
create trigger StrFormat
on MyTable
INSTEAD OF insert, update
as
insert MyTable
(StrSource, SQLNo, EngFormat)
select StrSource, StrSource,
CASE WHEN charindex('.',StrSource)>0
THEN '.' + replicate('0',len(StrSource)-charindex('.',StrSource))
ELSE '0'
END
from inserted
where isnumeric(StrSource)=1
insert MyTable
(StrSource)
select StrSource
from inserted
where isnumeric(StrSource)=0
go
insert MyTable
(StrSource)
values('1.00'),('1.000'),('oops'),('42')
select EngFormat, SQLNo--, StrSource
from MyTable
select FORMAT(SQLNo,EngFormat) 'SigDig', SQLNo--, StrSource
from MyTable
go
drop table MyTable
November 16, 2015 at 6:08 am
boosts (11/13/2015)
Hi Ed, Thanks for the response.As I mentioned, the specification can be defined using 3, 4, or more values. In the example I provided, they can go in separate directions sometimes, but that's besides the point too.
My problem is entirely around the number of trailing zeros for each one of the numbers involved in the specification. I have to be able to reconstruct the specification as the user inputted the data. Basically, if they added 3 trailing zeros to the upper tolerance and only 1 to the nominal, I need to be able to store that information somehow so the UI can use it to display it back to the user.
I have used decimal(19,10) for nominal, low, and high, and everything else. However, there is no way of knowing how many trailing zeros were used to construct the decimal just from the value stored in the database. So somehow, I need to be able to store the scale associated with each decimal value of the specification as well as the actual value. So far, there are 15 columns that would need to get the same treatment.
When you think about it, do you really have to be able to reproduce the print exactly as it was input by the user? If, for example, you have a measurement of 1 3/8, you would store that as 1.375. Displaying 1.3750 is the same value, but it specifies the 4th significant digit.
Unless I'm missing something, I would store dimensions and tolerances in numeric columns with a fixed number of decimals. You can have the front end round or truncate if you want, but I think (at least several years ago) having a print with 4 digits when showing eighths of an inch was acceptable. As long as you show enough digits to denote the proper accuracy, you should be good.
The alternative is to also store an integer column denoting the number of decimal places for the measurement on that row. If you keep all your measurements in one column in a table, you would need another column to store significant digits and the front end would have to use it to format your output.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply