Siri Posted September 12, 2006 Share Posted September 12, 2006 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 Link to comment Share on other sites More sharing options...
BobWeaver Posted September 19, 2006 Share Posted September 19, 2006 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. Link to comment Share on other sites More sharing options...
Siri Posted September 19, 2006 Author Share Posted September 19, 2006 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 Link to comment Share on other sites More sharing options...
Recommended Posts
This topic is 6564 days old. Please don't post here. Open a new topic instead.
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now