Trouble with basic DB design
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?