5 Replies Latest reply on Jun 23, 2014 8:37 AM by philmodjunk

    Trouble with basic DB design



      Trouble with basic DB design


           Hi folks:

           I am a self-taught FileMaker Pro user, and I'm having trouble designing a new DB to capture a component of my organization's work. I was hoping someone here could help me with the basic design.

           I run a collegiate outdoor program (we send students out into the woods on backpacking trips, canoe trips, and so on).  I am trying to design a database to handle our food planning and purchasing operations.  

           RIght now, I have four main tables, laid out as below:

           1. Food Items. Information related to a given food item (i.e., vendor, price, pack size, amount per person per meal, etc).

           2. Menu. I want to be able to create a menu that describes the food for a given trip.  The point here is to have a menu type (i.e., standard menu, nut-free, gluten-free, etc). [You can ignore the "Menu Day" table - that is just to specify the various meals for the given days of a trip. I won't be using the info in "Menu Day" in any calculations, as it just exists for planning purposes.]

           3. Ingredients list. This is the join table for Menus and Food Items.  Basically, there is only one additional field - Number of Times Eaten (i.e., if a trip will be having two burrito meals, the item "tortillas" needs to packed twice).

           4. Trips. Each trip could have a different number of people. I want to be able to assign a menu to each trip, so it could automatically inherit all of the food items related to that menu.

           The problem comes when I try to create reports for food packing and order.  Ideally, I want to have two "packing list" reports and one order report:

           1. Packing list per trip. So that each trip has a record of the food items and amounts they should have before they head into the field. In other words, I am looking for a place to put in a calculation field that would multiply "Amount per person per trip" * "Number of times eaten" * "Number of people per trip".  I can't figure out how to do this - I want each menu to be independent of the trips to which it is assigned (and therefore the number of people).  

           2. Total packing list per group of trips.  Our biggest push is when we send out ~40 orientation trips in August, and it is easier for us just to pack all the food at once.  So I would like a report that could show all of the trip packing lists together.

           3. Order amounts. We do a bulk order through our university food services, and I would like to be able to push out a report that just gives a total of how much of each item we need to buy.  

           Given that I am new at this, I think my implementation of this design must be flawed in some way, but I can't figure out what it is.  Can anyone provide any guidance?


        • 1. Re: Trouble with basic DB design

               1) That's definitely a "can't get there from here" situation. You want your menus to be independent of each trip and yet the quantities you want for your report ARE dependent on the trip.

               2) I see what you want and after looking at both 1 and 2 here, I suggest setting up a system where to get the report for 1), you specify a "group of trips" of just that 1 trip. Then one basic set up can be used for your reports for both 1 and 2.

               I think I see a way to set this up for you, but I just thought of a question that might change everything:


                     The point here is to have a menu type (i.e., standard menu, nut-free, gluten-free, etc).

               What you have set up assumes that you will have exactly one menu for any given trip. All participants in that trip will all have the same menu (all will get the nut free menu or all get the gluten free menu, etc.)

               Won't you have some people eating from the standard menu while others eat gluten free and so forth?

               3) You'll need at least one more table to do this as you'll need a table that lists all food items that you currently have on hand and how much of each you have. Your order report will need to deduct what you have on hand from what you plan to pack, the difference will be the minimum amount that you need to order.



          • 2. Re: Trouble with basic DB design

                 Thanks for the response.  Some further thoughts:

            •           I was able to get the "packing list per trip" to work as I wanted it to by defining a calculation field in the "Ingredients" table that multiplied "Food Items::Amount per Person Per Meal" * "Number of Times Eaten" * "Trips::Number of People".  Then I defined a portal in "Trips" that looked at the related records in "Ingredients Lists" and voila! The amounts would automatically change if I changed the menu assigned a trip or if I changed the make-up of any menu (i.e., changing the number of times eaten).  Seems like basic stuff when I look at it now, but I'm impressed Filemaker can constantly run those queries.
            •           In answer to your question about whether everyone will be eating the same menu, the answer is (almost always) yes, they will be eating the same thing.  Usually, this is because of an allergen like peanuts that could kill one person on the trip - we just have the whole trip eat peanut-free to make life easier.  
                        There are, of course, instances where not everyone on a trip will eat the same food. A classic example is a vegetarian who will not partake of the summer sausage at lunch.  In general, though, we speak about the trip's menu (instead of a person's menu) because of how we do the packing.  That is, because we pack all the food for a trip at once (often as we are packing the food for a dozen other trips), it is much easier just to talk about the food at the trip level instead of the person level.  Our food planners design a menu to meet the needs of the members of a given trip, and then those planners just say something like: "Trip 27 will be gluten- and dairy-free" or "Trip 44 is the religiously observant trip; it will be eating the kosher menu".  At this point, I'm comfortable with things as they are set up now, i.e., we assign an entire trip to the same menu.
            •           You mentioned you thought you saw a way to set up a "group of trips" in order to report out both the per-trip packing list and the overall packing list.  This makes sense to me because we tend to send out trips in bundles (i.e., our next trips are pre-orientation trips in August).   I think it would make sense to create a table called "Semesters" or "Months" to this effect.  Good idea.
            •           I see your point about needing to compare inventory to need before placing an order.  Trouble is, I can't see a good reason to do the inventory in a separate table.  Can you point out why I just shouldn't make a "Current Inventory" field in the "Food Items" tab, and create a layout that would show me only that field?  What benefit would a separate table provide? Wouldn't it essentially just be a one-to-one relationship (which I have heard is to be avoided)?

                 Thanks so much for your help with this,

            • 3. Re: Trouble with basic DB design

                        I was able to get the "packing list per trip" to work as I wanted it to by defining a calculation field

                   I don't see how that will work from your current relationships. the other values that you need to multiply to calculate this value are from fields in a related table and your current relationships do not connect to specific trips as would be needed in order to connect to data from a specified trip instead of data from the first related trip in your table.

                   The method for computing totals for a group of trips (or a "group" of just 1 trip) involves setting up records with TripIDs in an additional table, which in turn produces a list of values for linking to a different occurrence of Trips for use in calculations that now only access the values you need from the trip records you have selected.

                   With regards to inventory management, you make a good point. You could use a number field in the Food Items table. That may in fact, be the best option for getting this up and running quickly, but such a method can be a bit problematic. The problem you have with this approach is when you need to update those "on hand" fields to show depletion due to packing for your trips. If you should run a script that loops through the Ingredients List table to update these values and then find that you need to change the data in those records (an error was made, a last minute change in the number of trekkers is made....), you have to very carefully put the amounts back into inventory and then use the corrected data to re-update your inventory levels.

                   A better approach, though it will take quite a bit more time to set up, is to set up a "ledger" based inventory system where you add a new record to a "transaction" table each time you acquire more Food Items and each time that you pack food items or otherwise remove them from inventory. The balance of total removed from inventory and total added to inventory tells you your amount on hand. The type of corrections mentioned earlier then becomes a case of updating the transaction records and you no longer have to "undo" inventory changes before updating with the changed data. In addition, such a method allows you to review past consumption and inventory levels--which can sometimes be a useful way to better set re-order levels. (Though whether this is useful in your particular situation or not is something that you will need to survive.)

                   If interested in that method, see this thread: Managing Inventory using a Transactions Ledger

              • 4. Re: Trouble with basic DB design

                     Oh, you're right - the per-trip calculation was just using the "Number of People" in the first related record in the Trips table.  Good catch - it would have taken me a while to figure this out.  Thanks.

                     And I've been working on the creations of a "group of trips" table, but I think I am not correctly understanding the relationships you are suggesting I create.   I tried creating the set-up below, but this doesn't use a separate occurrence of the Trips table.  Can you explain this any further?

                     And I see the benefits of the ledger system.  That is quite a bit more more work, but I think it will be useful for us.  First, though, I need to get the reporting stuff above taken care of. Once that's in place, I'll work on the order/inventory system.


                • 5. Re: Trouble with basic DB design

                       The TripList table would be linked directly to Menus:

                       Menus::__MenuIDpk = TripList::_fkMenuIDfk

                       it serves solely to allow you to set up a portal to TripList on your Menus layout where you can use a drop down list or pop up menu in the portal row (set up on _fkTripIDfk) to select trips. This can be a conditional value list that limits the list to only trips linked to the current menu to keep the list fairly short. There are also ways to limit this list to trips with a date located in the future.

                       This then allows you define this calculation field in Menus with a text result type:

                       List ( TripList::_fkTripIDfk )

                       This, in turn, serves as a match field to a new occurrence of Trips, that I'll call Trips|Selected

                       With these relationships in place, a calculation field can use Sum ( Trips|Selected::Number of People ) to get a total number of people over all the selected trips.

                       Note: there are several scripted short cuts that can make this easier to work with. A "clear" button can pull up and delete the set of portal records in TripList. And a "select all" button could put in the ID's for all upcoming trips that specify the current menu to save the trouble of selecting each trip individually. (And a delete portal row button can then be used to drop out any that you want to exclude from your current calculations.)