9 Replies Latest reply on Dec 17, 2015 3:17 AM by Zerojojo

    How to build a nutritional database

    Zerojojo

      You good people,

       

      I am working for a small chocolate manufacturer and we would like to build a database that helps us create nutritional tables for the label of our products. The idea is to catalogue all our ingredients according to their composition (i.e. protein, fat, carbs, etc.) and then enter the recipes we use into the database in such a way that the database takes into account the composition and the relative amount of the ingredients to calculate the nutritional table of the final product.

       

      I have some experience with Excel, but I am completely new to FileMaker. To me it seems like FileMaker offers such a wide variety of solutions that I have trouble finding the right place to start reading for my particular problem. I guess it's too much to ask for instructions for how to build such a database and set up the calculations, but could anybody point me to some valuable tutorials or discussions on the topic?

       

       

      Any help is much appreciated.

       

       

      Best regards,

      Johannes

        • 1. Re: How to build a nutritional database
          macwombat

          Hi Johannes

           

          Welcome to the world of FileMaker.  A good place to start would be the FileMaker Training Series:

          Database Skills, FileMaker Pro Training | FileMaker

           

          The basic version is free and then later if you want to go to more advanced functionality you can always buy the advanced version of the training.  You will be given examples to work through that will assist you to learn how to use FileMaker and at the same time you will be able to think through your own database requirements.

           

          Kind regards

           

          Chris

          • 2. Re: How to build a nutritional database
            Extensitech

            This seems to me like one of those things that's just slightly too complex to fit in a simple post. It sounds like you're wanting to do this yourself (which is great! Welcome to FM!) but if you want some outside help, we (and many others on this site) would be happy to do it for you as a project.

             

            The primary requirement here is pretty easy. You create a table of ingredients, with fields for amounts of protein, carbs, fat, sugar, etc. On the recipe you sum the various ingredients' values.

             

            The first "catch" is whether the various factors (protein, carbs, etc.) is a limited, fixed list, or something that'd change over time, perhaps even frequently. If there's a potential to need to add more, especially if it's frequent, it might make sense to have a table of "nutritional elements" and have a join table between ingredients and nutritional elements where you store the value. That way, if you end up tracking "gluten" in the near future, you could just create a new nutritional element rather than having to add fields every time a new element comes up.

             

            The second "catch" is units of measure. For example, you can say how much protein beef has, but only if you know how much beef you're talking about. To further complicate this, there are a number of equally valid units of measure, so your system needs to "understand" the difference between a pound of beef, an ounce of beef, a kilo of beef, etc. Other ingredients might be measured in milliliters, grams, teaspoons, cups, fluid ounces, and so on.

             

            (Hey, does anyone else remember when the best reason anyone could come up with for having a home computer was that you could keep recipes on it? )

             

            Anyhow, we've done some solutions similar to this, though not exactly the same. Like I said, it's a bit much to get into a forum post, but perhaps this will give you some "food" for thought? (See what I did, there?)

             

            And, of course, if you need to hire a consultant either to build this for you or act as a mentor, we (or others here) are available for more in-depth help.

             

            In fact, we offer a free one-hour consultation for potential clients. I wouldn't be at all offended if you got one of those, and then didn't decide to have us do any paid work. We do it for free to demonstrate our value and helpfulness, so that even if you don't need paid help now, you'll think of us if and when you do. (Besides, this actually sounds like it could be fun to work through... well, "fun" in an "I'm a total geek" way ) www.extensitech.com

             

            Chris Cain

            Extensitech

            • 3. Re: How to build a nutritional database
              bigtom

              You will be happy with FM I am sure.

               

              I would do this with two tables. One for recipes and one for ingredients. Most commercial food can be managed in grams and mL. Recipes layout can simply have a portal with the ingredients added and summed.

               

              Does the cooking/manufacturing process ever break down fats or combine sugars? I have no idea, maybe not.

               

              I will work for chocolate. If you have an Excel file with some sample data on nutrition and some sample recipes I could do a simple example for you that you could build on.

              • 4. Re: How to build a nutritional database
                TorstenBernhard

                Considering that the results your database generates must comply with legal requirements ( food ingredients list), thorough testing is highly recommended before going live.

                • 5. Re: How to build a nutritional database
                  Zerojojo

                  Thanks. I'm a food chemist by training, so that will be the easy part for me.

                  • 7. Re: How to build a nutritional database
                    Magnus Fransson

                    Hi Johannes,

                     

                    Chris Cain Extensitech has some very good considerations to think about. What I suggest here is a total database normalisation assuming maximum granularity and flexibility.

                     

                    Then you need a minimum of five tables to store all information.

                     

                    Three main tables:

                    * Products.

                    * Ingredients.

                    * Nutrients.

                     

                    And two join tables:

                    * Between Products and Ingredients you store the amounts in the Recipe.

                    * Between Ingredients and Nutrients you store the relative amounts of nutrient as "parts per".

                     

                    Every table should have ther own "primary key". (I suggest using "auto enter serial".) The join tables should even have "foreign keys" joining them to their "parent tables".

                    Once you got that much working (with layouts and portals) so that you can easily insert and store the data in a logical and reliable way you got a good start.

                     

                    Best regards Magnus Fransson.

                    • 8. Re: How to build a nutritional database
                      Magnus Fransson

                      Hi Johannes,

                      Zerojojo

                      I'm just being curious. How's it going?

                       

                      Best regards Magnus Fransson.

                      • 9. Re: How to build a nutritional database
                        Zerojojo

                        I went through many tutorials and I think I figured out how to get it to work. Since this is step one in my new job which will only start in January, it will be a little while before I will put it into practice and see how it really goes.

                         

                        Your answers were extremely helpful! Thanks a lot. I'm starting to love FileMaker. I started out seeing it as a more complicated version of Excel, but now I get the power behind it.

                         

                         

                        Regards,

                        Johannes