Re: Storing units in the database

Mikito Harakiri wrote:
> This discussion is triggerd by Jon Heggland's message about dimensional
> units supported by user-defined type.
>
> Mikito Harakiri wrote:
>

>> vc wrote:
>>
>>>My impression is that you've got no clue as to what you are talking
>>>about The impression,  of course,  can be reversed by trying to present
>>>your thoughts in a coherent manner.

>>The
>>best I can suggest is googling "units group:sci.math". First few hits
>>are pretty good ones. Perhaps, in this context that writing would have
>>more sence to you.

You have to do that search at http://groups.google.com/; doing it at http://www.google.com/ is not so helpful (it drops the term 'group:sci.math' from the search and comes up with a number of books).

> The key idea is that you treat the expression with unit symbols, for
> example
>
> 20 feet
>
> formally. That is "Dimensionless number 20 multiplied by variable
> 'feet', whatever it might be". Now the conversion
>
> feet = 0.3 meters
>
> is an equation that can also be considered formally. We can substitute
> variables and get
>
> 20 feet = 20*0.3 meters

0.3048, but who's counting.

> Now, when applied to the database, how should we store 20 feet? User
> defined types provide us some (quite unsophisticated) solution. But do
> we really have to program a class every time we want to do trivial
> conversions?

C J Date does discuss this. No; but (in his terminology) you do define a different POSSREP for each set of units within a type. Thus, you can define a type for speeds (velocities have direction as well as magnitude), and you define a different possrep for each set of units you wish to work with - kilometres per hour, miles per hour, metres per second and furlongs per fortnight. The data stored in the DBMS in a separately defined way - hidden from the user. When you want a value in furlongs per fortnight, you simply choose the appropriate possrep when specifying the value. Further, if the type designer has done their stuff properly, you can multiply your value which you think is measured in furlongs per fortnight by a duration in microseconds and end up with the distance travelled in metres. Yes, someone has to think about which possreps are meaningful and provide some basic functionality, but the rest pretty much falls into place.

About the only issue that I've not seen dealt with is the ability or requirement to retrieve the original value as specified by the user, rather than obtaining the value in some specific set of units. That is, I might have (typically legal) reasons for needing to know that the value was originally specified as 23.45 furlongs per fortnight rather than, say, 0.01280 feet per second (23.45 furlongs per fortnight * 220 yards per furlong * 3 feet per yard / (14 days per fortnight * 24 * 60 * 60 seconds per day)). There's an easy way to get any the output in any particular supported possrep - but not to determine the original possrep that was used.

-- 
Jonathan Leffler                   #include <disclaimer.h>
Email: jleffler_at_earthlink.net, jleffler_at_us.ibm.com
Guardian of DBD::Informix v2005.01 -- http://dbi.perl.org/