Just to let you know up front, this is also posted at SQLTeam.com.

I have a material category, material, material cost table structure as follows :

Code:
MATERIAL_CATAGORY
CategoryID | Descr
--------------------
1              | Rebar

MATERIAL
fkCategoryID | MaterialID | Descr
----------------------------------
1                 | 1            | 10M

MATERIAL_PRICE
fkManufacturerID | fkMaterialID | UnitPrice | BaseUnit
---------------------------------------------------
1                      | 1                | 0.29      | ft
Relationship between MATERIAL (1) -> MATERIAL_PRICE (Many)

Above the cost of a 10M rebar is $0.29 / ft.

What I need to do is set it so that other unit costs can be calculated from this BaseUnit. In other words, lets set the unit cost is need per pound. We know that 1 ft of 10M rebar weighs 0.527 lbs/ft. Therefore, I can figure out that 5 lbs of 10 rebar would = (5lbs * 0.527 lbs/ft) * $0.29 = $0.76

This means that a table can be used in the following manner :

Code:
MATERIAL_COST_CONVERSION
fkMaterialID | BaseUnits | NewUnits | Conversion
-------------------------------------------------
1               | lbs          | ft           | 0.527

I already have a unit conversion table, but this table is used to provide conversion for compatible standard units. Eg. 1 Inch = 25.4 mm.

The previous table is used to material specific conversions.


Code:
UNIT
Unit | Descr | Category
-------------------------
ft   | Feet  | 1
in.  | Inch  | 1

UNIT_CATEGORY 
Category | Desc
----------------
1        | Length

UNIT_CONVERSION 
fkFromUnit | fkToUnit | Conversion
----------------------------------
ft         | ft       | 1
ft         | in.      | 12
....

I thought, to provide a "Material Unit Conversion" table I would cross join the MATERIAL table with the UNIT Conversion table and then UNIT the MATERIAL - MATERIAL_COST_CONVERSION in a view then when I need to get a conversion factor I use the MaterialID, FromUnit, and ToUnit to get the conversion.


Code:
CREATE VIEW vMaterialUnitCostConversions
AS
SELECT M.MaterialID, UC.FromUnit, UC.ToUnit, Conversion
FROM MATERIAL M CROSS JOIN UNIT_CONVERSION UC
UNION
SELECT M.MaterialID, MCC.fkBaseUnits AS FromUnit, MCC.fkToUnit AS ToUnit. MCC.Conversion
FROM MATERIAL M INNER JOIN MATERIAL_COST_CONVERSION MCC
ON M.MaterialID = MCC.fkMaterialID

The above would then render the following list:


Code:
MaterialID | FromUnit | ToUnit | Conversion
-------------------------------------------
1          | ft       | ft     | 1
1          | ft       | in.    | 12
1          | ft.      | lbs.   | 0.527
This would result in thousands and thousands of records in this view.

Is there a better way then what I have described above?

Mike B