I have a recipe a shopping list database that is working okay.

There is:

Parent Table: Recipes

Child Table: Ingredients with fields:

ID

Quantity

unit (fk)

ingredient name (fk)

Converted Quantity

Converted Unit (fk)

the units are stored in a separate table with fields:

ID

Unit name

type (mass,volume,count)

ratio of unit to mL or g

So when you enter an ingredient a base Quantity/unit and then when you enter a unit to convert to the ingredient record calculates the conversion Quantity to go with the new unit.

I use this Quantity to sum up on the Shopping list.

That works okay. The problem is with the types of ingredients obviously it becomes difficult to convert from mL to g as the conversion ratio is not the same for different ingredients. Sometimes recipes refer to the same ingredient with volume and mass units. So I'm trying to think up a way to have have special conversion table for conversion between types.

I'll work on a sample database and post it. I have this idea that you could put the base unit in the ingredient table with the type then have a many to many join table to the ingredient specific ratio table.

The ingred specific table would carry a fk of the regular unit that you are converting to.

So a conversion from mass to volume would have 2 ratios you would multiply together, one for the g to mL and, one for the base unit to g.

I am having trouble implementing this idea.

That sounded as clear as mud. Any input would be appreciated.

Siri

You need a field in your ingredients table to store the ingredient's density (I.E., grams per liter). Then, you can easily convert between mass and volume for any ingredient.

Regardless of the preferred unit for an ingredient, I would store all quantities in the same units and then have a separate data entry/display field to enter/display the quantity in the preferred units. That way, your units table need only contain a single record for each unit type rather than a record for every possible unit to unit conversion combination.

I've worked out a solution but its more geared towards cooking than using density. Usually each unit belongs to a type like mass or volume. I've created more units like clove, Case, or head that belongs to its own type of the same name (Case unit, of the group Case type)

This way you can connect each ingredient and their base unit to any other unit/type combination.

It requires regular relationship to the conversion to g/mL and a relationship which is a double relationship of ingredientID and type Id with a many to many join table. You do a conversion by multiplying the two ratios together basically.

I'll attach the file if anyone has any comments.

unittype.zip