I always say that information that is only descriptive should be of the character type (VARCHAR
, for example). Only information involving accounts should use numeric types (DECIMAL
, FLOAT
, for example).
This is a curious case. If you use a string (VARCHAR
), the ideal is to have two columns, one for each unit, after all you have two descriptions.
It does not go on the wave that the database should not have two columns with the same data because in this case the data is different.
Of course, the ideal would also be for the input of the two data to be independent. If you are going to calculate one of them, then things change a little.
Nothing prevents you from doing so even if one of the measures is calculated. But if it is calculated, it is already beginning to be better to use a numerical type, right?
Not so much, it will depend on the specific context. Eventually a basic calculation just to insert the data may not be considered a calculation. In general I would consider in most situations, but I don’t know your.
If you prefer to have only one column, ok, but then you have to calculate the other measure every time and the character type is not ideal. You have to think about what is best for your case. If you have two columns and will only display, then you will not have this calculation.
The character type is useful to present the information in "more human" format as demonstrated by the above Motta comment.
If you adopt the numeric field, then you need to decide how much you need precision and accuracy. The types FLOAT
or DOUBLE
has rounding problems. And if you can’t have this kind of problem, I strongly suggest not only use the type DECIMAL
or INTEGER
, but also have two columns and store the two measures independently. So you have control on how to make the rounding.
If you don’t do it this way, if you need to add the measurements together, for example, you will get different results depending on the unit used. There’s no other way to fix this.
If accuracy is not important, if the information does not need to be confronted or manipulated together later, the inaccurate types can be used. But honestly I would avoid them anyway.
In these cases I prefer not to have decimal places, I prefer to use the basic measure that avoids this. If it is height, record the number of centimeters or even millimeters, if necessary, do not use meters. And use a guy who only works with integers. The same goes for inches, don’t use feet. Which makes it hard to calculate.
Important: Even using the whole part, the conversion can cause inaccuracies, so if you can’t live with it, adopt a rounding strategy and save both data.
If you only keep one piece of data, you may wonder which one should be stored. I would go in the most exact, that is, in centimeters. It’s not "whatever".
But remember that it is okay to keep two columns if they are useful. Beware of such good practices, people use them blindly. And often people only know about them in half.
I would say it depends on your business rule, but you can put in meters and when the drive comes in feet, just convert using Trigger or before even calling the bank.
– ptkato
And what would be the best type of data to store meters?
– sThiago
If you are not going to count it, char, if it is float or decimal, if you need a precise number.
– ptkato
In case I want it to be possible value in meters of type : 1.5 meters, 10.4 meters, 3 meters. Decimal (10.1) would be the best choice to store?
– sThiago
Decimal(10,1)
is the same as9999999999,9
. Could putDecimal(2,2)
.– ptkato
Blz brother. problem solved =)
– sThiago
Height Observation in the US is generally reported in feet and inches type 6'6'' , 5-10 etc the average would have to be calculated by turning everything into inches (1 foot = 12 plows) and then reverting to ft/in
– Motta