3 Replies Latest reply on Oct 21, 2014 2:44 PM by tdennis

    (Help) Relating tables and layouts.

    tdennis

      Hello! I'm a new user, please try to be as simple as you can. Please check out my example below and see if you can help me.

       

       

      I'm designing a database that tracks all the different kinds of Kool-Aid that I make along with their ingredients.. Each Jug is made differently with various combinations of ingredients. i.e. sugar, lemons, kool-aid mix, salt, ice, etc. Not all jugs are created equal, but I want to reflect the ingredients on the Kool-Aid Jug's record and I want to count how many of each ingredient is in and add them up.

       

      I separated the database into different layouts and tables. 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). I have about 30 different jugs and they all have different properties.

       

      My problem, lies in getting all of the different tyes of ingredients to be reflected on the Kool-Aid Jug's layout in a way that I can tally how many let's say lemons I used, and how many bags of sugar were used. This is hard because not every jug has sugar, and not every jug with sugar has lemons. I can't combine them together, because of the distinct formulas each ingredient has.

       

      I'd also like to do this without having to create 20 visble and empty fields on the Jug's layout because only 5 fields were used.

       

       

       

       

      Any suggestions?

        • 1. Re: (Help) Relating tables and layouts.
          erolst

          tdennis wrote:

          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”?

           

          tdennis wrote:

          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.

           

          tdennis wrote:

          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.

          • 2. Re: (Help) Relating tables and layouts.
            planteg

            Hi tdennis,

             

            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
            • RecipeIngredients

             

            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 .

             

            EDIT

             

            Looks like erolst was faster than me .

            1 of 1 people found this helpful
            • 3. Re: (Help) Relating tables and layouts.
              tdennis

              Thanks for all of the tips, guys. Guess it's back to the drawing board!

              These answers were extremely helpful!