Hello,
part of one of the projects i'm working on involves SI Units, and i am to incoporate them in a database structure.
I'm not yet very comfortable with data modelling and database building, so i come here to get some advices.
Basically, as defined by the SI system, there are 7 base units (m, kg, s, A, K, mol, cd).
Each of these has a name (metre), symbol (m), and quantity (length).
Then, comes a set of derived units. Each have a name (newton), symbol (N), quantity (force). Then each can be described as a combination of the 7 base units.
eg:
1 N = 1 kg.m/s^2 (or 1.kg.m.s^(-2) )
1 Hz = 1 s^(-1)
Each derived unit is a combination of some base units power (-3,-2,-1,1,2 or 3).
Sometimes, it is preferred to describe derived units as combinations of other derived units instead of basic ones.
For instance, you'de rather say :
1 V = 1 W/A
than
1 V = 1 kg.m^2.s^(-3).A^(-1)
Finally, there is a set of "additional units", which have a name, symbol, quantity, and are obtained by multiplying one of the existing units by a fixed number.
This fixed number can be either a prefix:
10^3 = kilo, 1 km = 10^3 m
Or an "arbitrary" constant:
1 min = 60 s
1 litre = 0.001 m^3
etc.
Now i'm facing this model and trying to put it into a database [img]images/smilies/tongue.gif[/img].
Do you guys have any advice on how i should handle it ?
Do you think i should separate each type of unit (base, derived, additional), or keep only one table for all units (with only the basic information).
What do you think is the better way to handle the composition of units from base units / other derived units ?
Should i use arrays, or a table with 7 column (one for each base unit) containing the power for the corresponding base unit (something like:
unit m kg s A K cd
V 2 1 -3 -1 0 0
).
Suggestions on how to handle the constant multiplication factors ?
This may seem a pretty simple model but as i said i'm pretty new to database modelling so any help is very very welcome.
Well i finally came up with the following structure :
Units UNIT_ID int
UNIT_NAME varchar(32)
UNIT_SYMBOL varchar(5)
UNIT_TYPE enum ( ‘base’ , ’derived’ , ’additional’ )
Quantities QUANTITY_ID int
QUANTITY_NAME varchar(32)
UnitsSets
SET_ID int
UNIT_ID int
QUANTITY_ID int
Derivations DERIVATION_ID int
DERIVATION_GROUP_ID int
DERIVED_UNIT_ID int
BASE_UNIT_ID int
POWER enum ( -3 , -2 , -1 , 1 , 2 , 3 )
Multipliers MULTIPLIER_ID int
ADDITIONAL_UNIT_ID int
BASE_UNIT_ID int
POWER float
Basically i grouped all units in the same table, and created 2 seperate tables for multipliers (ie 1km = 1000 m) and derivations.
About derivations, i created 2 different ids, one for the row and one for the group in order to achieve multiple derivations for the same unit :
For the example of the joule
–First way:
as 1 J = 1 kg.m2.s-2
using 3 derivation ids, sharing one derivation group id and pointing to m, kg and s in the units table.
–Second way:
As 1J = 1 N.m
using 2 derivation ids, one different derivation group id and pointing to N and m in the units table.
Any comments if i did something bad are very welcome
Bookmarks