Handling multiple units..!!

[Narender Sagar]

Hi All,
I am making a database in which I have 3 tables, one is BOM Table (Bill of materials), one Inventory Table and one Item Master table. There are various kind of items in each table and their respective Units in these tables. Now, the Units can be different in each table for a given item. e.g. The Units in Item Master Table for an Item "X" can be KG, while for same Item the units in BOM Table can be GM and in Inventory Table the Units can be Ltr.
I've created a 'Conversion' table in which I maintained the all possible conversions (from KG to GM or GM to Ltr etc.).
My objective is to have common Units for a given Item (for this I think the Item Master Unit will rule).
I'm planning to make two separate queries for BOM Table and Inventory Table and bringing the items to common unit i.e. Item Master's Units.
I'm just wondering is their any better way to accomplish this job..
Thanks

[zmbd]

Sometimes I need to do conversions in the lab
What I have is a table with the SI units and the conversion factors. You can find a simple one on Wiki
https://en.wikipedia.org/wiki/Conversion_of_units

I set it up this way:
T_Unit_Class (Length, Mass, Time, Volume, Pressure...)
T_Units (Unit_Name, Unit_Symbol, FK_Unit_Class, Unit_Formula, Unit_FactorToSIBase)

Example
(Meter, M, Length, M, 1)
(KiloGram, Kg, Mass, Kg, 1)
(Gram, g, Mass, Kg/1000, 0.001)

Now one could set up a table that self joins back to the Base SI Units; however I've never bothered.

Usage, a bit contrived;
1 Unit in and unit convert to, find in table, check for same classification.
2 Unit in, find in table, find conversion factor to SI-Base, Convert to SI Base,
3 Find new unit, find conversion factor, inverse factor, convert.

:)

You could get really fancy and have a table with the Metric scalars (n,u,m,d,D,K,M etc) and then attempt to find if the unit conversions are simple scalars... :)

[Oralloy]

Narender,

What you suggest makes sense. After all, medicines are usually measured in grams, where fruit is measured in kilograms.

One project, which I worked on, had a reference table of units-of-measure in the schema. One record of which was "count", used for items like trucks. This was very handy, as we could use it for unit display values, like "kg" or "count", and their (possible) language dependencies.

As you know, some items have the problem of how they are managed - in bulk, small items are often sold by the "kilo", and smaller quantities are done by "count". If I want ten 6mm*20mm SHCS (socket-head cap-screws), I buy them by count. If I want 200,000 screws, I'll have to buy by weight.

This also means that each small part has a unique conversion from count to weight. For example a 6mm*20mm SHCS weighs roughly 75% that of an 8mm*20mm SHCS - meaning that there are 33% more 6mm screws per kilogram than there are 8mm screws.

The conversion may also be unique to each manufacturer in some cases. Consider for a moment aspirin tablets.

You sound like you've considered several solution ideas:

• Use of UNIT and CONVERSION tables
• Fundamental UNITs have exact conversions that apply to all products (e.g. gram to kilograms).
  • These are included in theSystème international units, Imperial, or Amerikan unit systems.
  • Weight and Mass are often confused in common use.
• Each product has a unique set of conversions that apply to dissimilar units:
  • Screw count to weight is unique to each screw type.
  • Screw count to weight may be unique to manufacturer, depending on manufacturing details.
  • Liquid densities differ, depending on material - water is nominally 1.00g/cm^3, oil is roughly 0.75g/cm^3.
  • Nitrogen can deliver as liquid or compressed gas.
• At times a unit conversion cannot be performed, or is ambiguous, perhaps pointless.
  • Ordering a litre of 6mmx20mm SHCS makes little sense.
  • Converting a litre of 6mmx20mm SHCS into kilograms is highly inaccurate.
  • Converting a litre of 6mmx20mm SHCS into a count is likely worse.
  • It is probably meaningless to say "one" oxygen (although one atom oxygen is actually intelligible).
  • Converting "one" to "kilograms" oxygen actually makes physical sense, but is likely meaningless in your context.
• For some products, it may be worth maintaining separate "products" which represent form:
  • Nitrogen, gaz.
  • Nitrogen, liquid.
• And some products include sizing, or packaging:
  • Resistor, 10 ohm, bulk.
  • Resistor, 10 ohm, tape.
  • Resistor, 10 ohm, individually wrapped.
  • Nitrogen, gas, 450 bar.
  • Nitrogen, liquid, 10 kl (10,000 litres).
  • Lead Styphnate, 1 kg bulk.
  • Lead Styphnate, 80 mg, primer.
  • &c.

So we need to understand the requirements and what the implications are. If you want help mapping requirements into solutions, ask away, and be prepared for questions in reply.

In other words, build the "right" solution, as there is no value in unnecessary complexity designed to solve non-problems. Let's deal with building a good solution to your immediate problem. We can worry about building a fully-comprehensive solution when you really need to do so.

This looks like it might be a class assignment, based on how you have written it up (and the lack of accompanying detail). (If I'm wrong, you have my apologies). So ask direct questions and you'll get direct answers here. We have a bunch of very bright people helping us here, and they appreciate interesting and difficult questions.

Cheers!
Oralloy

[Narender Sagar]

Thank you ZMBD & Oralloy..
What I understand from your response is that I'm on right direction as I'm trying to build something similar as you advised.
Although your suggestion sounds much more professional and internationally applicable solution.
best regards,

[jforbes]

For what you are doing, I wouldn't implement a units conversion unless it is absolutely needed as it can get pretty hairy as zmbd and Oralloy have done a good job of pointing out.

One other important note: I've worked on a few systems with an Item Master and typically they create multiple entries (and Part Numbers) for the same item in its different Units of Measure. Usually in this case, when a pallet or carton needs to be opened and the resultant material needs to be inventoried there is usually either a Manufacturing process or Outside process where the Initial Item is shipped to a Process which then returns the Item in the other U/M. This way any cost accounting can be applied/adjusted as usually the cost associated with a single quantity and bulk quantity of the same item are different.

[Narender Sagar]

Hi jforbes,
I'm fully understanding the point you are emphasizing.
Thanks for same.