Kool-Aid that I make along with their ingredients.. Each Jug is made differently with various combinations of ingredients
It would be beneficial if you'd properly introduce the entities you want to manage – so, is “Kool-Aid” identical to a “jug”?
I separated the database into different layouts and tables.
While you're working on the structure, simply ignore layouts (it would be even better if you didn't use the database at all during the planning phase).
A layout is the context for a table occurrence, and is more involved when implementing the actual workflow. What's essential at the moment are tables and fields, because they (should) represent your entities and their attributes.
One table/layout represents each jug of kool-aid. And I have 4 more tables/layouts sorting my jugs by type( they all have different formulas for calculating their ingredients because big jug, little jug, short jug, tall jug).
Don't do that. A jug is a jug, as far as your solution is concerned – they just have different attributes. You wouldn't create separate staff tables for male and female staff …?!
What you can do is differentiate the jugs by a type attribute (or size, if that's the main attribute).
As to your original issue: instead of using any number of fields (and tables, as explained above), for the start you just need three tables, in the constellation
Jugs --< JugIngredients >-- Ingredients
where --< and >-- mean one-to-many and many-to-one, respectively; you can read this as a jug has (potentiually) many ingredients, and each ingredient can (potentially) be used for many jugs (or jug types).
JugIngredients is a “join table”, in which each record is a combination of a jug, and an ingredient in a given quantity. Thus, each Jug is characterized by the sum of the related records = ingredients, together with the ingredient's individual quantities.
This also means that you only assign those ingredients to a jug that needs them (i.e. create the necesssary number of related records) – and that for each ingredient you can see in which jugs it is used, and in which quantity. (You're looking into the same join table both times, just via a different relationship).
Letv us know if you need more info.
1 of 1 people found this helpful
I guess you already have an Inventory consisting of all the ingredients you need, right ? Then you need recepies or BOMs (Bill of Material in manufacturing parlance).
For that purpose, you would need two tables:
- Recipes, one record by recipe
For each recipe(record) in Recipes, you have a certain number of records in RecipeIngredients. Each record in the second table has these fields:
- a foreign key in Recipes table, to know which recipe the line is linked to, quantity, item_number and description, the last two being related to the inventory
As far as layout are concerned, the one for a recipe would be showing fields from Recipes record, and a portal would display the related RecipeIngredients records.
Does that make sense ? I understand it could be hard to figure out with only words .
Looks like erolst was faster than me .
Thanks for all of the tips, guys. Guess it's back to the drawing board!
These answers were extremely helpful!