5 Replies Latest reply on Dec 21, 2013 9:57 PM by philmodjunk

    ERD

    Mark_2

      Title

      ERD

      Post

           Would someone please take look at this ERD and let me know if I am on the right track? Brand new user of Filemaker Pro 13 and trying to get started on the right track. Not sure what other information I need to provide to get feedback on this ERD but if you let me know, I will gladly post it.

      screen.jpeg

        • 1. Re: ERD
          Mark_2
          /files/949caddc63/screen.jpeg 1280x802
          • 2. Re: ERD
            philmodjunk

                 An ERD by itself can be misleading. It may make perfect sense yet be quite wrong for the real life processes that it is supposed to support.

                 What I see is a blending of a typical set of relationships for invoicing, purchasing and recipes. Those parts show typical primary to foreign key relationships setting up one to many and many to many relationships. In many ways, it just like a manufacturing and sales system where the "recipe" section has the the same role as a BOM (bill of materials) section for a manufacturing system.

                 What is not clear is whether you need a separate Recipes table or could use Inventory in place of Recipes. Either option might work and either might be the better approach depending on specifics of your data and work flow.

                 You may need additional tables to log and manage the production process--the number of items produced from your recipes--both what is planned and then what is actually produced with resulting changes in inventory as ingredients are consumed and product is produced.

                 The only thing that looks odd are three separate "Menu" tables. There isn't any clear reason to have 3 separate tables named Menu 1, Menu 2 and Menu 3.

            • 3. Re: ERD
              Mark_2

                   The recipes table would consist of a combination of ingredients, Many recipes could have many ingredients. The function of a recipe would be to get a cost breakdown by serving. So 1 pound of flour plus 1 pound of sugar plus whatever, would yield 3 pounds of dough which would yield 36 cookies at a cost of $x per cookie. Could I not include the number of portions per recipe into the recipe table?

                   This was actually my biggest question. The products that will be in menus have vastly different qualities. One product may only need 3 fields to describe it, say name, price, description. Other menu items may require 10 or more fields to describe it, say color, size, weight, etc. I thought it would be best to separate the products into similar categories. Hence the 3 menus. If I combine all the items into 1 menu, then that table may have 20 fields, with any one item using a only portion of those fields and the rest would be empty.

                   So would it matter much if the data in a record has mostly empty fields? I also wasn't sure it it would be easier or more effective to populate a portal for each menu from a separate table or just using a key identifier from a combined menus table.

                   And lastly, assuming my ERD structure is correct, can you please tell me if the foreign and primary keys have been setup correctly?

                   Thanks much

              • 4. Re: ERD
                philmodjunk
                     

                          Could I not include the number of portions per recipe into the recipe table?

                     You could. You might also do that via the inventory table. I'm not suggesting that you do that, just noting the possibility.

                     

                          This was actually my biggest question.

                     That's a classic data modeling issue with no one clear answer. Much depends on what you need to do with that data. If you need to set up reports that combine data from two or more of these three tables, it will be much more difficult to do using separate tables. Using a single table with many unused fields is one option. Setting up a structure where the additional fields are in one or more related tables is also used.

                • 5. Re: ERD
                  philmodjunk

                       With regards to fk and pk fields, you've listed a number of fk fields on the "one" side of relationships that make no sense. As an Example, the fk fields in Inventory don't make sense and what is missing are the fk fields from Menu1, Menu2 and Menu3--if you kept them as separate tables.