Storing unit in database

Dec 13, 2013 at 7:01 AM
Hi, first of all thank you for developing this tool.

I am curious about what is your suggestion to store units in database. Let say i have a unit "kg∙m/s2" and value 5.
  1. do i have to create one column for value and one column for unit? If yes for unit column what i have to save? directly PhysicalUnit or its string value (kg∙m∙s−2)?
    2.Do i have to create tables and store all your objects with giving them Ids and in a seperate column next to value column storing these ids?
I hope i explained my problem clearly,

Thanks
Coordinator
Dec 14, 2013 at 9:38 AM
Edited Dec 14, 2013 at 9:48 AM
Hi Cemsacma,

Yes, I understand you questions. which are very good questions, for which I also would like to have a answer too.
I have only made very little use of PhysicalMeasure together with a database.
The answers may also depend on which database you use, and in what way you use the units. The perfect would be, to be able to define a new datatype, which then can be used as the datatype for a field/column. In PhysicalMeasure the base type for units are an array of SBytes: SByte[7], which simply counts the exponents of each physical baseunit. For example kg∙m/s2 becomes [1, 1, -2, 0,0,0,0]. In this way you could store the unit for a measure in a separate column. and you would be able to save different units for each row.
If a column contains the same unit for all the rows , it would be good, if the definition of the column could constrain that and still say which unit it is. If you use a small number of specific units, and all rows has only one unit for each column, maybe the best way is to define a datatype for each physical unit and use it in the definition of the column.

The small project I have made my self, was used to log measurements with very different units. So the units was stored once in a separate table and referenced with database IDs in a separate column for each measurement.

Are you using measurements with different units for same column for different rows, or just one unit for a column for all rows?

I am glad that you like this project, and hope that you find a little help in my reply.
Dec 16, 2013 at 8:37 AM
Hi KiloBravo,

Thanks for your quick reply, and it helped me to have a better view.

My datababase will work on MS SQL Server. The project will have chemical components and calculations. Current formulas will calculate only with predifined speciphic units, but i want the user be able to input his own units( for example instead of Celcius Degree, he will enter Kelvin) by changing from the user interface. By implementing your PhysicalMasure library to formulas the user will be able to use whichever unit he wants. All the input values and units and result will be saved to the database. Next time the user runs the project the user has to see previously selected units on the window.

Units may be simple as (kg,m as base units ) or combined units. In the database table same column would have different kind of measures for different rows. (temperatur, mass, time, or combined).

Also the table size will increase as new values saved, so i expected it to go to huge amounts. So i have to consider performance issues.

In the light of your explanation what do you say if I create a table as shown below on database and cast the rows in the project to IPhysicalUnit. And in other tables using those Ids in a seperate column?
ID UnitType MeasureType Length Mass Time EC TT AOS LI Unit UnitLong Symbol Prefix SVC
1 Base Length 1 0 0 0 0 0 0 m meter m 1
2 Convertible Length 1 0 0 0 0 0 0 cm centimeter cm centi 100
3 Convertible Length 1 0 0 0 0 0 0 mm millimeter mm milli 1000
4 Base Mass 0 1 0 0 0 0 0 kg kilogram kg 1
5 Convertible Mass 0 1 0 0 0 0 0 g gram g 1000
6 NamedDerived Mass 0 1 0 0 0 0 0 t tonne t 1/1000
7 NamedDerived Pressure -1 1 -2 0 0 0 0 Pa pascal Pa 1
8 NamedDerived Weight 1 1 -2 0 0 0 0 N newton N 1
EC:ElectricCurrent
TT:ThermodynamicTemperature
AOS:AmountOfSubstance
LI:LuminousIntensity
SVC:ScaledValueConversion

Thanks again.
Coordinator
Dec 16, 2013 at 10:38 PM
Hi Cemsacem,

I would say that your design should work all right.

You are welcome.