In one of my forms it will be necessary to collect the height of the user in meters. However, this form may be in English or Portuguese. If it is in English this height will be in feet. If it is in Portuguese, the height will be in meters.
The best practice would be to have one column for feet and one for meters on my table? Or only in meters? What would be the best data type for my column to store this data?
I always say that information that is only descriptive must be of character type ( VARCHAR , for example). Only information that involves accounts should use numeric types ( DECIMAL , FLOAT , for example).
This is a curious case. If you use a string ( VARCHAR ), ideally you have two columns, one for each unit, after all you have two descriptions.
It's not going to happen that the database should not have two columns with the same data, because in this case the data is different.
Of course the ideal there would also be that the input of the two data are independent. If you calculate one of them, then the thing changes a little.
Nothing prevents you from doing this even if one of the measures is calculated. But if it's calculated, it's starting to be better to use a numeric 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 do not know yours.
If you prefer to have only one column, okay, but then you have to calculate the other measure every time and the character type is not ideal. You have to think about it. If you have two columns and will only display, there you will not have this calculation.
The character type is useful for presenting information in a "more human" format as demonstrated by Motta's comment above.
If you adopt the numeric field, then you need to decide how much accuracy you need. Type FLOAT or DOUBLE has rounding problems . And if you can not have this type of problem, I strongly suggest not only using the DECIMAL or INTEGER type, but also having two columns and storing the two measures independently. So you have control over how to do the rounding.
If you do not do this, if you need to add the measurements, for example, you will have different results depending on the unit used. There is no other way to resolve this.
If accuracy is not important, if information does not need to be confronted or manipulated together later, 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 high, engrave the number of centimeters or even millimeters, if this is necessary, do not use meters. And use a type that only works with integers. The same goes for inches, do not use feet. What's even hard to figure out.
Even if you use an integer part, the conversion may cause inaccuracies, so if you can not live with it, adopt a rounding strategy and save both.
If you save just one die, you may wonder which one should be saved. I would go in the most accurate, that is, in centimeters. It's not that much.
But remember that it's okay to keep two columns if they are useful. Beware of such good practices, people use them blindly. And often people only know half of them.
The best practice would be to have one column for feet and another for meters in my table?
No, remember that in a database you should not have different columns with the same data, because it generates some problems, for example when you need to update you will have to update two different columns. If you need this value as a foreign key which column would you use?
Or only in meters? What would be the best Data Type for my column store this data?
I would choose a standard unit, meters or feet either, and would create a single column in the bank to record that value. For precise values the ideal is to use decimal and specify the number of decimal places, not float, depending on the account the result can vary. Using string causes queries involving accounts to slow down.
To solve the problem of how the data will be read from the database later, you have several ways to implement the solution.
You can create a stored procedure that has as one of the parameters the unit that wants the height value and convert directly using sql.
You can create an intermediary service layer that has a function with the same functionality described above.
Or, convert the value directly to the client interface depending on which unit of measure is selected.
More important than how to register the data is to know how it will be used to choose the best way to persist and read it.
