CODEWITHSUNDEEP
sql-server-2008database-design
Nighil
Nighil

Reputation: 4127

Database design for Unitconvertion Table?

i am trying to design a table for storing unit this table is used for unit convertion also the structure of my table is like in the image

table Structure

is this table structure enough for convertion of units from one type to another

Upvotes: 0

Views: 534

Answers (2)

Damir Sudarevic
Damir Sudarevic

Reputation: 22187

alt text

This assumes that the UnitSymbol is unique, which is not the case if you mix metric, imperial etc, for example tblsp = table-spoon may be imperial, metric, Canadian, US etc. In that case use UnitID (integer) as a primary key.

Upvotes: 1

veljkoz
veljkoz

Reputation: 8512

It is enough, but, it would prove unnecessarily difficult to query. For example, imagine if someone queries "how many kilograms are in a ton? Or in a milligram?". You'd have to have many rows with "Kilograms" that reference each other, or alternatively run a query a couple of times until you get to the level you need.

Usually, you do that in a connecting table. So, using your naming of columns, it would be something like:

Units (unit_ID, Name, unit_Type, unit_Group, IsDeleted)

IsFactorOf(unit_ID, parent_ID, factor)

where in "IsFactorOf" the unit_ID is FK to child unit, parent_ID is FK to parent unit (child-parent are with regards to factor element - 'child is factor times of parent'), and both keys make a PK.

Now you can define how much kilo's are in whatever unit available of the same type by entering the IsFactorOf for each conversion. Obviously, you'll get a two way conversion...

Upvotes: 0

Related Questions