Storing measure drive in Mysql table

Question

In one of my forms it will be necessary to collect the height of the user in meters. However, this form can be in English or in Portuguese. If you are in English this height will be in feet (Feet). If you are in Portuguese the height will be in meters.

The best practice would be to have a column for feet and another for meters in my table? Or only in meters? Which would be the best data type for my column to store this data?

Answer 1

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.

Answer 2

The best practice would be to have a 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 Type date for my column store that data?

I would choose a standard unit, feet or feet whatever, and create a single column in the bank to record that amount. For precise values the ideal is to use decimal and specify the number of decimals, and not float because depending on the account the result may vary. Using string slows down queries involving accounts.

To solve the problem of how the data will later be read from the bank, you have several ways to implement the solution.

You can create a stored Procedure that has as one of the parameters the unit you want 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 into the client interface depending on which unit of measure is selected.

More important than how to record the data is to know how it will be used to choose the best form of persistence and reading of it.