Database Answers Catering Business

Home Ask a Question Careers Contact us Data Models First Timers Search Site Map
Catering Businesses for Schools
Back to the Data Model.
User Requirements 
These Requirements were established by Sean on 27th. June, 2007 who 
needs a Database to support his mother's Catering Business.

My mother runs a catering business right now, and she is the contracted caterer for the French School of Nairobi, Kenya in Africa. 
The school has 400 students, while there are 30 staff. 

From Monday to Thursday, she provides lunch for almost 300 people, consisting of some teachers but mostly children 
ranging from kindergarten level to high school level. At the beginning of each term, 

Parents can opt to make payments for their children's lunches for the whole term, and most do, but a few 
decide to decline school lunch and leave their children to fend for themselves when it comes to getting lunch. 

Every day at lunchtime, a supervisor carries a list with all children's names who have had school lunch paid for 
the term, and checks their name off the list if that child has come to take lunch. 

There are instances, however, when a student likes what my mom is offering but has not paid for school lunch and he 
would like to have some, 

He goes to the supervisor who notes down his name and he gets a meal voucher, good for one meal. 
That lunch will be accounted for and added to the student's fees to be paid at the end of the term.

There are various student categories in which the students fall, and each has different price brackets: 
  • $2 for the kindergarten level,
  • $4 for the primary school, and $6 for high school students and teachers. Those prices are per day per person depending on their level of education. Those prices are also flat rate for any student, even those who have not pre-paid for lunch. If a primary school student wanted lunch and he had not pre-paid, his meal would be worth $4. I think it is worth mentioning here that a meal does not consist of a single serving. A student can have more than one serving in a meal if they feel so inclined, but most find one serving satisfactory. The kitchen side of the catering business is a little more straight-forward. My mom has a list of different ingredients she requires to produce the food that she needs to everyday, and these ingredients are stocked in the store room. These ingredients come from various suppliers, but my mom has made an arrangement so that there is only one supplier for certain goods. For example, meat comes from only one supplier, chicken comes from only one supplier, the fruit and vegetables comes from only the greengrocer, the dry goods come from a wholesaler, etc... At the beginning of each term, my mom's stock starts from zero, so she buys enough stock for the first month including extra quantities of pantry goods, such as salt and spices, that are going to be used frequently. At the end of every month, or whenever necessary, she will reorder to refill stock that has reached the reorder level for that item. When the items are delivered, most of the time the deliveries contain the complete order, but sometimes the delivery will only contain half or 2/3 of the items ordered, so my mom will have to wait until the supplier completes the order. The problems with the way things are running now are on both ends: * On the actual serving side, it is possible that some students will end up having a meal without having gone to get a meal voucher, and they say that they are on the list, and since my mom's staff do not know all the students and thus cannot tell which ones have paid and which ones have not, we make losses in meals that go unpaid for. There are also issues with inconsistency between our count of how many meals were consumed and the supervisor's count, which is important because my mom wants to be paid for the meals that were had, not just the ones that were noticed to have been had. The losses, while most of the time small, are losses none the less and money that my mom could be earning. * On the stock side, due to the manual nature of the inventory keeping at present, it is easy to lose track of items that we have in stock and more often than not excess amounts of items are ordered when they're not necessary because there are already enough in stock. It is also difficult to account for inventory used when taken to produce the food, so the kitchen store is usually left in need of restocking when the store's levels are low. There is also the matter of record-keeping of all orders and invoices, as well as delivery notes. Currently, it is easy to lose track of the quantities pending of items when suppliers deliver only part of the order, so the potential to lose money reordering or to believe that there is enough in stock when there is not is quite high. As such, I plan on computerizing my mother's catering accounting through the use of an Access database. Using this computerized system should be able to tackle the problems on both sides by: * taking account of every single meal sold, and depending on level of education, determining how much each meal is worth so as to gather the figures and make a total for gross pay owed to my mom. * take account of inventory levels, take account of quantities used and keep record of orders and deliveries, and when the deliveries are made, to update the stock levels according to what has been received. These records will help figure out the total my mom owes suppliers. With the assistance of this program, my mom will be able to accurately tell, by deducting the money owed from the gross owed, the net amount that she is owed at the end of the term. I decided to do this in Access because it seems the simplest method of creating a good database, but I did not know Access well and in fact had to get a book to teach me how to use it. The book has taught me a lot, I understand how tables and relationships work, and I have been able to draw out these tables so far, but I'm having trouble linking them. Please refer to the diagram I have attached to see what the state of my database is like so far. As you can see from the diagram, I have two sections: meals and stock. On the stock side, I have a Suppliers table, a Products table, Order Details and Order tables, and a Deliveries table. I believe the way I have organized the tables and the joining relationships makes sense, but I don't know how to store the data such that I can record deliveries according to which order they belong to (since each order is unique and each order comes from a different supplier) and then update the stock levels of the inventory by the amount received. I also have a feeling that I'm missing a "Quantity Of Inventory Consumed" table somewhere, but I don't know where and how. On the meal side, it is a lot of the same thing. I have a Students table, which will keep all students in the school in record assigned by their unique ID numbers, as well as whether they have paid for lunch beforehand or not. Then I have them linked to the Meal Plan table, which describes what each plan costs ($2, $4 and $6 respectively), therefore being able to identify which plan goes with which student. My problem on this side is knowing how to create another table, or re-organizing the database and its elements such that I can make a record every time that a student (pre-paid or not) will be having lunch. ================================================ The Things of Interest include :-
  • Ingredients
  • Meals
  • Recipes
  • Staff
  • Students
  • Home Ask a Question Careers Contact us Data Models First Timers Search Site Map