CODEWITHSUNDEEP
database-designschemadata-warehouse
Anuvrat Parashar
Anuvrat Parashar

Reputation: 3110

How to store data like length, memory, weight without losing the significance of the unit and be able to compare two values?

Say I have to populate database fields of the likes of Memory size where in a numerical value with a unit is supposed to be saved. It would have been possible to hardcode the logic needed for validating the data entered by the user, had this been the only field that I needed to take care of at one particular place alone in the project. But that not being the case it sounds quite impractical and foolish even think about embedding information of the likes of 1 MB = 1000 KB in the source code. I need to keep such info in the product in order to be able to compare the memory size field of two different entities.

Question: What is the ideal way to go about storing scalar data with a unit in a system such that the unit without degrading the unit to a meaning less string? And can be used in comparison operations?

EDIT: Data is coming in from sources I do not control, eg spreadsheets exported by third-parties or data scraped off web pages etc. The units there may be any thing. Hope it makes the question clear.

Upvotes: 0

Views: 226

Answers (2)

Ronnis
Ronnis

Reputation: 12843

I wrote an answer for something similar here Custom unit conversion

Basically, you want to store a "Unit of Measure" along with your measure. For example, you can have the value "100" in the value column, and "KB" in the unit_of_measure column. If you need to compare this with another row that has "100" and "MB" you need to convert them to a common measure first. This can be done with the conversion table I showed in the other answer.

If at all possible, you should strive to normalize the measures before inserting the data in table. This is better from most perspectives. Failing that, I think using a conversion table is the way to go.

Upvotes: 0

WW.
WW.

Reputation: 24311

In this situation I would normalise the input to a consistent unit. So in your example, you would normalise to bytes, or perhaps megabytes.

If you had two columns and stored both the numeric value and the units the database will be much harder to query. For example, a query for "who has more than 10 gigs of storage" will involve inline calculations to handle all the possible units. This will prove difficult to index compared to a column expressed in standard units.

If a user enters a figure in gigabytes you probably would not want to display them back as a large number bytes. This is a user-interface concern - you would write something to display the number of bytes in the most useful unit.

If you strictly need to display values using the same units as entered, then add a "displayUnits" column alongside the number of bytes. Use this in the user interface.

Upvotes: 3

Related Questions